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