<commit_before>
CREATE TABLE IF NOT EXISTS
  public.CDB_TableMetadata (
    tabname regclass not null primary key,
    updated_at timestamp with time zone not null default now()
  );

-- No one can see this
-- Updates are only possible trough the security definer trigger
-- GRANT SELECT ON public.CDB_TableMetadata TO public;

--
-- Trigger logging updated_at in the CDB_TableMetadata
-- and notifying cdb_tabledata_update with table name as payload.
--
-- Attach to tables like this:
--
--   CREATE trigger track_updates
--    AFTER INSERT OR UPDATE OR TRUNCATE OR DELETE ON <tablename>
--    FOR EACH STATEMENT
--    EXECUTE PROCEDURE cdb_tablemetadata_trigger(); 
--
-- NOTE: _never_ attach to CDB_TableMetadata ...
--
CREATE OR REPLACE FUNCTION CDB_TableMetadata_Trigger()
RETURNS trigger AS
$$
BEGIN
  -- Guard against infinite loop
  IF TG_RELID = 'public.CDB_TableMetadata'::regclass::oid THEN
    RETURN NULL;
  END IF;

  -- Cleanup stale entries
  DELETE FROM public.CDB_TableMetadata
   WHERE NOT EXISTS (
    SELECT oid FROM pg_class WHERE oid = tabname
  );

  WITH nv as (
    SELECT TG_RELID as tabname, NOW() as t
  ), updated as (
    UPDATE public.CDB_TableMetadata x SET updated_at = nv.t
    FROM nv WHERE x.tabname = nv.tabname
    RETURNING x.tabname
  )
  INSERT INTO public.CDB_TableMetadata SELECT nv.*
  FROM nv LEFT JOIN updated USING(tabname)
  WHERE updated.tabname IS NULL;

  RETURN NULL;
END;
$$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

--
-- Trigger invalidating varnish whenever CDB_TableMetadata
-- record change.
--
CREATE OR REPLACE FUNCTION _CDB_TableMetadata_Updated()
RETURNS trigger AS
$$
DECLARE
  tabname TEXT;
  rec RECORD;
  found BOOL;
  function_start timestamptz;
  function_end timestamptz;
  function_duration float;
  log_error_verbosity text;
BEGIN

  IF TG_OP = 'UPDATE' or TG_OP = 'INSERT' THEN
    tabname = NEW.tabname;
  ELSE
    tabname = OLD.tabname;
  END IF;

  EXECUTE 'SELECT clock_timestamp()' INTO function_start;
 
  -- Notify table data update
  -- This needs a little bit more of research regarding security issues
  -- see https://github.com/CartoDB/cartodb/pull/241
  -- PERFORM pg_notify('cdb_tabledata_update', tabname);

  --RAISE NOTICE 'Table % was updated', tabname;

  -- This will be needed until we'll have someone listening
  -- on the event we just broadcasted:
  --
  --  LISTEN cdb_tabledata_update;
  --

  -- Call the first varnish invalidation function owned
  -- by a superuser found in cartodb or public schema
  -- (in that order)
  found := false;
  FOR rec IN SELECT u.usesuper, u.usename, n.nspname, p.proname
             FROM pg_proc p, pg_namespace n, pg_user u
             WHERE p.proname = 'cdb_invalidate_varnish'
               AND p.pronamespace = n.oid
               AND n.nspname IN ('public', 'cartodb')
               AND u.usesysid = p.proowner
               AND u.usesuper
             ORDER BY n.nspname
  LOOP
    EXECUTE 'SELECT ' || quote_ident(rec.nspname) || '.'
            || quote_ident(rec.proname)
            || '(' || quote_literal(tabname) || ')';
    found := true;
    EXIT;
  END LOOP;
  IF NOT found THEN RAISE WARNING 'Missing cdb_invalidate_varnish()'; END IF;
  
  EXECUTE 'SELECT clock_timestamp()' INTO function_end;
  SELECT extract(epoch from (function_end - function_start)) INTO function_duration;
  
  EXECUTE 'SELECT setting FROM pg_settings where name=''log_error_verbosity''' INTO log_error_verbosity;
  SET log_error_verbosity=TERSE;
  RAISE LOG 'invalidation_duration: %', function_duration::text;
  EXECUTE 'SET log_error_verbosity= ' || log_error_verbosity;
  
  RETURN NULL;
END;
$$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

DROP TRIGGER IF EXISTS table_modified ON CDB_TableMetadata;
-- NOTE: on DELETE we would be unable to convert the table
--       oid (regclass) to its name
CREATE TRIGGER table_modified AFTER INSERT OR UPDATE
ON CDB_TableMetadata FOR EACH ROW EXECUTE PROCEDURE
 _CDB_TableMetadata_Updated();


-- similar to TOUCH(1) in unix filesystems but for table in cdb_tablemetadata
CREATE OR REPLACE FUNCTION public.CDB_TableMetadataTouch(tablename regclass)
    RETURNS void AS
    $$
    BEGIN
        WITH upsert AS (
            UPDATE public.cdb_tablemetadata
            SET updated_at = NOW()
            WHERE tabname = tablename
            RETURNING *
        )
        INSERT INTO public.cdb_tablemetadata (tabname, updated_at)
            SELECT tablename, NOW()
            WHERE NOT EXISTS (SELECT * FROM upsert);
    END;
    $$
LANGUAGE 'plpgsql' VOLATILE STRICT;
<commit_msg>Optimize invalidation time logging<commit_after>
CREATE TABLE IF NOT EXISTS
  public.CDB_TableMetadata (
    tabname regclass not null primary key,
    updated_at timestamp with time zone not null default now()
  );

-- No one can see this
-- Updates are only possible trough the security definer trigger
-- GRANT SELECT ON public.CDB_TableMetadata TO public;

--
-- Trigger logging updated_at in the CDB_TableMetadata
-- and notifying cdb_tabledata_update with table name as payload.
--
-- Attach to tables like this:
--
--   CREATE trigger track_updates
--    AFTER INSERT OR UPDATE OR TRUNCATE OR DELETE ON <tablename>
--    FOR EACH STATEMENT
--    EXECUTE PROCEDURE cdb_tablemetadata_trigger(); 
--
-- NOTE: _never_ attach to CDB_TableMetadata ...
--
CREATE OR REPLACE FUNCTION CDB_TableMetadata_Trigger()
RETURNS trigger AS
$$
BEGIN
  -- Guard against infinite loop
  IF TG_RELID = 'public.CDB_TableMetadata'::regclass::oid THEN
    RETURN NULL;
  END IF;

  -- Cleanup stale entries
  DELETE FROM public.CDB_TableMetadata
   WHERE NOT EXISTS (
    SELECT oid FROM pg_class WHERE oid = tabname
  );

  WITH nv as (
    SELECT TG_RELID as tabname, NOW() as t
  ), updated as (
    UPDATE public.CDB_TableMetadata x SET updated_at = nv.t
    FROM nv WHERE x.tabname = nv.tabname
    RETURNING x.tabname
  )
  INSERT INTO public.CDB_TableMetadata SELECT nv.*
  FROM nv LEFT JOIN updated USING(tabname)
  WHERE updated.tabname IS NULL;

  RETURN NULL;
END;
$$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

--
-- Trigger invalidating varnish whenever CDB_TableMetadata
-- record change.
--
CREATE OR REPLACE FUNCTION _CDB_TableMetadata_Updated()
RETURNS trigger AS
$$
DECLARE
  tabname TEXT;
  rec RECORD;
  found BOOL;
  function_start timestamptz;
  function_end timestamptz;
  function_duration float;
  log_error_verbosity_value text;
BEGIN

  IF TG_OP = 'UPDATE' or TG_OP = 'INSERT' THEN
    tabname = NEW.tabname;
  ELSE
    tabname = OLD.tabname;
  END IF;

  function_start := clock_timestamp();
 
  -- Notify table data update
  -- This needs a little bit more of research regarding security issues
  -- see https://github.com/CartoDB/cartodb/pull/241
  -- PERFORM pg_notify('cdb_tabledata_update', tabname);

  --RAISE NOTICE 'Table % was updated', tabname;

  -- This will be needed until we'll have someone listening
  -- on the event we just broadcasted:
  --
  --  LISTEN cdb_tabledata_update;
  --

  -- Call the first varnish invalidation function owned
  -- by a superuser found in cartodb or public schema
  -- (in that order)
  found := false;
  FOR rec IN SELECT u.usesuper, u.usename, n.nspname, p.proname
             FROM pg_proc p, pg_namespace n, pg_user u
             WHERE p.proname = 'cdb_invalidate_varnish'
               AND p.pronamespace = n.oid
               AND n.nspname IN ('public', 'cartodb')
               AND u.usesysid = p.proowner
               AND u.usesuper
             ORDER BY n.nspname
  LOOP
    EXECUTE 'SELECT ' || quote_ident(rec.nspname) || '.'
            || quote_ident(rec.proname)
            || '(' || quote_literal(tabname) || ')';
    found := true;
    EXIT;
  END LOOP;
  IF NOT found THEN RAISE WARNING 'Missing cdb_invalidate_varnish()'; END IF;
  
  function_end := clock_timestamp();
  SELECT extract(epoch from (function_end - function_start)) INTO function_duration;
  SELECT setting INTO log_error_verbosity_value FROM pg_settings WHERE name='log_error_verbosity';
  SET log_error_verbosity=TERSE;
  RAISE LOG 'invalidation_duration: %', function_duration::text;
  PERFORM 'SET log_error_verbosity= ' || log_error_verbosity_value;
  
  RETURN NULL;
END;
$$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

DROP TRIGGER IF EXISTS table_modified ON CDB_TableMetadata;
-- NOTE: on DELETE we would be unable to convert the table
--       oid (regclass) to its name
CREATE TRIGGER table_modified AFTER INSERT OR UPDATE
ON CDB_TableMetadata FOR EACH ROW EXECUTE PROCEDURE
 _CDB_TableMetadata_Updated();


-- similar to TOUCH(1) in unix filesystems but for table in cdb_tablemetadata
CREATE OR REPLACE FUNCTION public.CDB_TableMetadataTouch(tablename regclass)
    RETURNS void AS
    $$
    BEGIN
        WITH upsert AS (
            UPDATE public.cdb_tablemetadata
            SET updated_at = NOW()
            WHERE tabname = tablename
            RETURNING *
        )
        INSERT INTO public.cdb_tablemetadata (tabname, updated_at)
            SELECT tablename, NOW()
            WHERE NOT EXISTS (SELECT * FROM upsert);
    END;
    $$
LANGUAGE 'plpgsql' VOLATILE STRICT;

{'fraction_non_alphanumeric': 0.07710896309314587, 'fraction_numerical': 0.0008787346221441124, 'mean_word_length': 3.086624775583483, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3419020', 'n_tokens_mistral': 3043, 'n_tokens_neox': 2846, 'n_words': 932}