Re: Synchronize two similar tables: recursive triggers

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
>