Pivotal Greenplum Specific Information

This will allow the repeatable read transaction isolation level (not supported by GP) to be translated to serializeable, which will enable the Postgres FDW to operate properly

PG Notify support

This is not currently supported with Greenplum vs. current Postgres sofware versions.

Trigger Invalidation with Greenplum

While Greenplum supports triggers in a limited sense, the triggers do not work when writing to a table from the data segments. As such, trigger invalidation is not yet working with Greenplum (see below for an alternative).

Stored Procedure based Invalidation with Greenplum

As a replacement for trigger based invalidation, a stored procedure can be used that leverages the "Trigger invalidation" mechanism to provide invalidation for Greenplum. When used, the stored procedure can be called after each data load to notifiy Heimdall of new data.

Connection properties to use:

Greenplum configuration:

CREATE SCHEMA heimdall;

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

CREATE OR REPLACE FUNCTION heimdall.changed(text) returns void AS $$
BEGIN
  LOOP
    UPDATE heimdall.tableupdates SET ts=(date_part('epoch', now())*1000)::bigint where name=$1;
    IF found THEN
      RETURN;
    END IF;
    BEGIN
       INSERT INTO heimdall.tableupdates VALUES ($1, (date_part('epoch', now())*1000)::bigint);
       RETURN;
    EXCEPTION WHEN unique_violation THEN
    END;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

Source Configuration

Below is a screenshot of the default recommended data source configuration for a baseline Greenplum cluster. Details are explained below: