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 <[EMAIL PROTECTED]>:
>>
>>
>>> 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
>>>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
