Delayed Invalidation Trigger

cdb_delayed_invalidate.sql

CREATE OR REPLACE FUNCTION public.CDB_Delayed_TableMetadata_Trigger() 
RETURNS trigger AS $$ 
  DECLARE upd timestamp;
BEGIN 
  IF TG_RELID = 'cartodb.CDB_TableMetadata'::regclass::oid THEN 
    RETURN NULL;
  END IF;
  SELECT updated_at INTO upd 
    FROM cartodb.CDB_TableMetadata WHERE tabname = TG_RELID::regclass;
  IF upd IS NOT NULL AND (now() - upd) > '20m'::interval THEN 
    DELETE FROM cartodb.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 cartodb.CDB_TableMetadata x SET updated_at = nv.t FROM nv 
        WHERE x.tabname = nv.tabname RETURNING x.tabname ) 
      INSERT INTO cartodb.CDB_TableMetadata SELECT nv.* 
      FROM nv LEFT JOIN updated USING(tabname) 
      WHERE updated.tabname IS NULL;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

DROP TRIGGER track_updates ON british_columbia_voting_areas_2013;

CREATE trigger track_updates AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON british_columbia_voting_areas_2013 FOR EACH STATEMENT EXECUTE PROCEDURE public.cdb_delayed_tablemetadata_trigger()