Re: Synchronize two similar tables: recursive triggers - Mailing list pgsql-general

hello

2008/9/19 Michael Toews <mwtoews@sfu.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@sfu.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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>
>