PostgreSQL Specific Information

PostgreSQL Proxy Restrictions

Schema Assist for Dependencies

To be documented (supported for views only at this time via the information schema)

PG Notifify Support

Postgres supports notification support, i.e. it provides a built-in pub/sub interface. When Postgres is used, this option can be explicitly controlled with a connection property of "hdUsePGNotify" and a value of true or false

When used, this will override the need to have a grid cache for invalidations, although it does not automatically enable multi-primary Heimdall management nodes as a grid cache does. This also enables the below trigger invalidation that leverages the pg_notify interface.

Trigger Invalidation with Postgres

Postgres provides a listen/notify interface that can be used to push invalidation messages directly from the database to the proxy. In order to configure this, use the following:

Connection property of hdUsePGNotify, value of true, then configure the following trigger function and trigger on each table:

CREATE OR REPLACE FUNCTION heimdall.changed()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('heimdall', 'invalidate-trigger,'||current_database()||'.'||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||','||((date_part('epoch', now())*1000)::bigint)::varchar);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- for each table on the system, perform the following:
CREATE TRIGGER trigger_table_changed
  BEFORE INSERT OR UPDATE OR DELETE ON test
  EXECUTE PROCEDURE heimdall.changed();

The result of this is near instant invalidation of tables, even if a write is done to PostgreSQL without going through Heimdall. Log messages with debug mode will indicate when this is functional.

When this is in place, this adjusts a few other behaviors. When active, invalidations will be issued through the DB notify interface vs. the grid cache as well. This enables local only cache configurations to effectively work without a grid cache. Additionally, if a table is invalidated via the grid cache with the above configuration, then additional notification messages will not be generated for that table by Heimdall, but other tables that have not been invalidated by the trigger will continue to have invalidations from Heimdall. This allows a mixed setup, so that frequently updated tables may have the trigger, while infrequently updated tables (or new tables) are invalidated directly. This will reduce the overhead of maintaining the triggers as the DB schema changes, while optimizing the number of invalidation messages needed.

This also supports invalidation of tables that are changed via stored procedures, other triggers, or are modified in other ways. It is still a requirement that stored procedures be tagged with the tables they are reading, but writes will be automatically accounted for.

Alternate Trigger Invalidation with Postgres (via polling)

This technique requires polling against the database, which adds load and latency to the invalidation process, but is documented for completion.

Connection properties:

Note: The following example is drafted for use with older Postgres, so doesn't use the ON CONFLICT syntax available in PG 9.5+

CREATE SCHEMA heimdall;

CREATE TABLE heimdall.tableupdates (
name text PRIMARY KEY,
ts bigint not null );

CREATE OR REPLACE FUNCTION heimdall.changed()
RETURNS TRIGGER AS $$
BEGIN
  LOOP
    UPDATE heimdall.tableupdates SET ts=(date_part('epoch', now())*1000)::bigint, name=current_database()||'.'||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;
    IF found THEN
      RETURN NEW;
    END IF;
    BEGIN
       INSERT INTO heimdall.tableupdates VALUES ((date_part('epoch', now())*1000)::bigint, current_database()||'.'||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME);
       RETURN NEW;
    EXCEPTION WHEN unique_violation THEN
    END;
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trigger_table_changed
  BEFORE INSERT OR UPDATE OR DELETE ON test
  FOR EACH ROW
  EXECUTE PROCEDURE heimdall.changed();