Re: Synchronize two similar tables: recursive triggers

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

Hello

ad colum that will contains info about source of value

like

create table a(a integer, from_trigger bool);
create table b(a integer, from_trigger bool);

create or replace function synchronize_handler_a()
returns trigger as $$
begin
if not new.from_trigger then
new.from trigger := true;
insert into b values(new.*);
end if;
return new;
end;
$$ language plpgsql;

this is protection under resursive triggers

regards
Pavel Stehule

2008/9/18 Michael Toews <mwtoews(at)sfu(dot)ca>:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

The INSERT and DELETE TG_OPs are straightforward (the simplest solution
for these is that the existence of the primary key can be checked in the
other table), however the UPDATE handler is really confusing.

Is it possible for a trigger function to know where an UPDATE originated
(user vs trigger)? I'm not sure how a trigger could know the first to be
fired, or how many times it has passed between. Any other ideas? Thanks
again.

-Mike

Pavel Stehule wrote:
> Hello
>
> ad colum that will contains info about source of value
>
> like
>
> create table a(a integer, from_trigger bool);
> create table b(a integer, from_trigger bool);
>
> create or replace function synchronize_handler_a()
> returns trigger as $$
> begin
> if not new.from_trigger then
> new.from trigger := true;
> insert into b values(new.*);
> end if;
> return new;
> end;
> $$ language plpgsql;
>
> this is protection under resursive triggers
>
> regards
> Pavel Stehule
>
>
>
>
> 2008/9/18 Michael Toews <mwtoews(at)sfu(dot)ca>:
>
>> 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
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>

hello

2008/9/19 Michael Toews <mwtoews(at)sfu(dot)ca>:
> The INSERT and DELETE TG_OPs are straightforward (the simplest solution
> for these is that the existence of the primary key can be checked in the
> other table), however the UPDATE handler is really confusing.
>
> Is it possible for a trigger function to know where an UPDATE originated
> (user vs trigger)? I'm not sure how a trigger could know the first to be
> fired, or how many times it has passed between. Any other ideas? Thanks
> again.
>

in 8.3 you should to analyze pg_stat_activity

Pavel Stehule

> -Mike
>
> Pavel Stehule wrote:
>> Hello
>>
>> ad colum that will contains info about source of value
>>
>> like
>>
>> create table a(a integer, from_trigger bool);
>> create table b(a integer, from_trigger bool);
>>
>> create or replace function synchronize_handler_a()
>> returns trigger as $$
>> begin
>> if not new.from_trigger then
>> new.from trigger := true;
>> insert into b values(new.*);
>> end if;
>> return new;
>> end;
>> $$ language plpgsql;
>>
>> this is protection under resursive triggers
>>
>> regards
>> Pavel Stehule
>>
>>
>>
>>
>> 2008/9/18 Michael Toews <mwtoews(at)sfu(dot)ca>:
>>
>>> 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
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>
>

If anyone is interested, here is my solution to my problem, which I hope
will be obsolete when the issues with the FDO PostGIS provider are
fixed. I have also successfully tested this setup out with foreign key
constraints in the primary table only --- the secondary tables just use
primary key constraints. I ended up using a third table to store
information for update synchronization operations (rather than query
pg_stat_activity, since I couldn't see the use in it):

CREATE TABLE prim.sync
(
source text NOT NULL,
CONSTRAINT sync_pkey PRIMARY KEY (source)
) WITH (OIDS=FALSE);

BEGIN
IF TG_OP = 'INSERT' THEN
IF (TG_TABLE_SCHEMA = 'prim') AND
(SELECT count(id) = 0 FROM second.mytable WHERE id = NEW.id) THEN
INSERT INTO second.mytable(id, fname, num) VALUES(NEW.id,
NEW.fname, NEW.num);
ELSIF (TG_TABLE_SCHEMA = 'second') AND
(SELECT count(id) = 0 FROM prim.mytable WHERE id = NEW.id) THEN
INSERT INTO prim.mytable(id, fname, num) VALUES(NEW.id, NEW.fname,
NEW.num);
END IF;
ELSIF TG_OP = 'UPDATE' THEN
IF (SELECT count(*) = 0 FROM prim.sync WHERE source=TG_TABLE_NAME) THEN
INSERT INTO prim.sync VALUES (TG_TABLE_NAME);--First trigger fire
IF TG_TABLE_SCHEMA = 'prim' THEN
UPDATE second.mytable SET fname = NEW.fname, num = NEW.num WHERE
id = OLD.id;
ELSIF TG_TABLE_SCHEMA = 'second' THEN
UPDATE prim.mytable SET fname = NEW.fname, num = NEW.num WHERE
id = OLD.id;
END IF;
ELSE--This is the second and last trigger fire
DELETE FROM prim.sync WHERE source = TG_TABLE_NAME;
END IF;
ELSIF TG_OP = 'DELETE' THEN
IF TG_TABLE_SCHEMA = 'prim' THEN
DELETE FROM second.mytable WHERE id = OLD.id;
ELSIF TG_TABLE_SCHEMA = 'second' THEN
DELETE FROM prim.mytable WHERE id = OLD.id;
END IF;
ELSE
RAISE EXCEPTION 'TG_OP %', TG_OP;
END IF;
RETURN NEW;
END;

CREATE TRIGGER prim_sync
AFTER INSERT OR UPDATE OR DELETE
ON prim.mytable
FOR EACH ROW
EXECUTE PROCEDURE prim.sync_mytable_fn();

CREATE TRIGGER second_sync
BEFORE INSERT OR UPDATE OR DELETE
ON "second".mytable
FOR EACH ROW
EXECUTE PROCEDURE prim.sync_mytable_fn();

Michael Toews wrote:
> The INSERT and DELETE TG_OPs are straightforward (the simplest solution for these is that the existence of the primary key can be checked in the other table), however the UPDATE handler is really confusing.
>
> Is it possible for a trigger function to know where an UPDATE originated (user vs trigger)? I'm not sure how a trigger could know the first to be fired, or how many times it has passed between. Any other ideas? Thanks again.
>
> -Mike
>
> Pavel Stehule wrote:
>
>> Hello
>>
>> ad colum that will contains info about source of value
>>
>> like
>>
>> create table a(a integer, from_trigger bool);
>> create table b(a integer, from_trigger bool);
>>
>> create or replace function synchronize_handler_a()
>> returns trigger as $$
>> begin
>> if not new.from_trigger then
>> new.from trigger := true;
>> insert into b values(new.*);
>> end if;
>> return new;
>> end;
>> $$ language plpgsql;
>>
>> this is protection under resursive triggers
>>
>> regards
>> Pavel Stehule
>>
>>
>>
>>
>> 2008/9/18 Michael Toews <mwtoews(at)sfu(dot)ca>:
>>
>>
>>> 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
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>