Implementation Choices for PostgreSQL Event Generation:

Using Periodic Polling to logically compare the results of SQL SELECT queries.

1. Before SQL SELECT Query
2. After SQL SELECT Query
3. Polling Interval
4. Before-After Logical Comparison Function.
5. Event Name, Description, Output Function.

   Example:
-- Table: joe_event_table

-- DROP TABLE joe_event_table;

CREATE TABLE joe_event_table
(
  originating_event character varying NOT NULL,
  event_time timestamp with time zone NOT NULL,
  event_text character varying,
  event_query character varying,
  database_user character varying NOT NULL,
  triggering_table character varying NOT NULL,
  event_id serial NOT NULL,
  CONSTRAINT joe_event_table_pkey PRIMARY KEY (event_id),
  CONSTRAINT joe_event_table_event_id_key UNIQUE (event_id)
)
WITH (OIDS=FALSE);

-- Function: poll_new_protocols()

-- DROP FUNCTION poll_new_protocols();

CREATE OR REPLACE FUNCTION poll_new_protocols()
  RETURNS void AS
$BODY$DECLARE
  inserted_row  source.protocol%ROWTYPE;
BEGIN

  IF NOT EXISTS(SELECT tablename FROM pg_catalog.pg_tables WHERE 
     tablename = 'protocol_previous' AND schemaname ='source') THEN
    CREATE TABLE source.protocol_previous AS SELECT protocol_id FROM source.protocol;
  END IF;

  FOR inserted_row IN SELECT  * FROM source.protocol WHERE protocol_id NOT IN
      (SELECT protocol_id FROM 	source.protocol_previous WHERE protocol_id IS NOT NULL) 
      LOOP
	  INSERT INTO joe_event_table
	       VALUES
	  (
		'poll_new_protocols',
		now(),
		'New Protocol',
		'New Protocol - ' || inserted_row.tla,
		CURRENT_USER,
		'source.protocol'
	   );
	   INSERT INTO source.protocol_previous VALUES ( inserted_row.protocol_id);	
       END LOOP;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Sample run:

SELECT * FROM poll_new_protocols();
DELETE FROM source.protocol_previous WHERE protocol_id=12;
DELETE FROM source.protocol_previous WHERE protocol_id=24;
SELECT * FROM poll_new_protocols();


INSERT INTO joe_event_table 
(originating_event, event_time, event_text, event_query, database_user, triggering_table, event_id)
 VALUES 
('poll_new_protocols', '2009-04-06 14:08:41.007-07', 'New Protocol', 'New Protocol - v041', 'slangley', 'source.protocol', 1);
INSERT INTO joe_event_table 
(originating_event, event_time, event_text, event_query, database_user, triggering_table, event_id)
 VALUES 
('poll_new_protocols', '2009-04-06 14:08:41.007-07', 'New Protocol', 'New Protocol - h021', 'slangley', 'source.protocol', 2);

Advantages:

• The queries are in standard SQL SELECT form and their results can be easiliy previewed.

Disadvantages:

• Notification of changes is not instantaneous.
• Can miss changes that happen between polling intervals if evidence of the change is deleted by the time of the next poll.

Using triggers on tables, coupled with OLD/NEW-state logical comparison functions.

1. Logical function taking OLD/NEW row-values from a trigger function as input.
2. Table to monitor for changes - via a trigger.
3. Which trigger events to monitor (INSERT, DELETE, and/or UPDATE)
4. Columns in the table to monitor for changes (optional).
5. Column value filters to apply for the monitored table (optional).
6. Event Name, Description, Output Function.

   Example:

CREATE OR REPLACE FUNCTION getnewprotocol()
  RETURNS trigger AS
$BODY$
    BEGIN
	INSERT INTO joe_event_table
	    VALUES
	(
		TG_NAME,
		now(),
		'New Protocol,
		'New Protocol - ' || inserted_row.tla,
		CURRENT_USER,
		TG_RELNAME
	);
	RETURN NULL;
     END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

CREATE TRIGGER new_protocol
  AFTER INSERT
  ON source.protocol
  FOR EACH ROW
  EXECUTE PROCEDURE getnewprotocol();

Advantages:

• Notification of changes is near instantaneous.
• No changes go undetected.

Disadvantages:

• Determination of table(s) on which to add triggers requires some effort for queries and functions based on views. (Very similar to the more general database topic of "how to maintain materialized views"). In particular, it would require considerable coding to automate this.
• Queries and functions are not standard SQL SELECT's - due to the use of OLD and NEW - and cannot be easily previewed.

Using an External Application Outside of PostgreSQL to Process the PostgreSQL Write-Ahead Log

• Postgresql Write-Ahead Log (WAL)
• xlog.h - header file describing WAL format.

Advantages:

• Notification of changes is near instantaneous.
• No changes go undetected.
• Freedom to innovate outside of the PostgreSQL environment

Disadvantages:

• Would involve the most work. So much so that the scope of the work is yet-to-be-determined.

-- ScottLangley - 31 Mar 2009