Hi all,
I need to have two tables that are mostly synchronized in my database,
such that an edit to a row in one is made to the other, and vice versa.
Normally, this is done using views with rules, however my situation does
not allow editable views (http://trac.osgeo.org/fdo/ticket/346). So, I
need to have two database tables.
The other thing is that the two tables are not identical, as I need to
omit columns with "advanced" data types in one of the tables (another
bug: http://trac.osgeo.org/fdo/ticket/394). The two tables also need to
be isolated in different schemata.
Here are some example tables:
CREATE SCHEMA prim;
CREATE SCHEMA second;
CREATE TABLE prim.mytable
(
id integer,
fname character varying,
num real,
timestmp timestamp with time zone, -- not in second.mytable
CONSTRAINT mytable_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);
CREATE TABLE second.mytable
(
id integer,
fname character varying,
num real,
CONSTRAINT mytable_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);
To synchronized the two tables, I plan to use a trigger function to
handle INSERT, UPDATE and DELETE events, using TG_OP and
TG_TABLE_SCHEMA. (If there are better solutions that don't use triggers,
stop me here and fill me in).
What I'm having difficulty designing is how to deal with recursive
triggers, since I require two-way communication. For example:
1. change on prim.mytable fires trigger to sync change on second.mytable
2. change from (1) on second.mytable fires trigger to sync change on
prim.mytable
3. change from (2) on prim.mytable fires trigger ... etc.
This behaviour is mentioned in the documentation:
http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html
(search for "recurs") however, it doesn't offer an example nor solution.
Some possible solutions may involve using trigger functions with
parameters (I'm yet to see an example of this), or disable the second
trigger from the first trigger while updating the other table, etc.
Perhaps there is a global variable somewhere that could indicate the
level of recursion. Or, possibly, a "version" column could be kept in
each column, which is incremented on the first trigger fire, and returns
NULL if OLD.version=NEW.version.
Any suggestions or references to other examples would be much
appreciated. Thanks in advance.
-Mike
