2009/9/28 Airbus380 <[email protected]>
>
> Hi all,
>
> I used two databse with slony-i, they work well. db_master1 is a master
> node, and db_slave1 is a slave.
>
> In the both databse, I have a table call staff with following structure:
>
> CREATE TABLE staff
> (
> id text NOT NULL,
> "name" text,
> age integer,
> CONSTRAINT staff_pkey PRIMARY KEY (id)
> )
> WITH (OIDS=FALSE);
> ALTER TABLE staff OWNER TO postgres;
>
>
> I test on windows xp, it is ok.
>
> Then I created one more databse, its name is db_center.
>
> My idea is: when slave have an operation - insert or update or delete then
> the operated record must be updated to db_center, so i create a trigger
> function as following:
>
> CREATE OR REPLACE FUNCTION process_staff_audit() RETURNS TRIGGER AS
> $staff_audit$
> BEGIN
> IF (TG_OP = 'DELETE') THEN
> PERFORM dblink_exec('dbname=db_center port=5432
> host=192.168.1.226 user=user1 password=user1',
> 'DELETE FROM staff WHERE id = ''' || OLD.id
> || ''';',false);
> RETURN OLD;
> ELSIF (TG_OP = 'UPDATE') THEN
> PERFORM dblink_exec('dbname=db_center port=5432
> host=192.168.1.226 user=user1 password=user1',
> 'UPDATE staff SET "name" = ''' || NEW.name
> || ''', age = ''' || NEW.age
> || ''' WHERE id = ''' || NEW.id || ''';',false);
> RETURN NEW;
> ELSIF (TG_OP = 'INSERT') THEN
> PERFORM dblink_exec('dbname=db_center port=5432
> host=192.168.1.226 user=user1 password=user1',
> 'INSERT INTO staff VALUES( ''' || NEW.id
> ||''', ''' || NEW.name ||''',
> ''' || NEW.age ||''');',false);
> RETURN NEW;
> END IF;
> RETURN NULL; -- result is ignored since this is an AFTER trigger
> END;
> $staff_audit$ LANGUAGE plpgsql;
>
> CREATE TRIGGER staff_audit
> AFTER INSERT OR UPDATE OR DELETE ON staff
> FOR EACH ROW EXECUTE PROCEDURE process_staff_audit();
>
> I tested this trigger function on two database without slony-i, it worked
> well.
> But I used this trigger function on db_slave1 (using slony-i), it didn't
> work.
>
>
>
Triggers on replicated tables are disabled by Slony-I for slave nodes.
You would have to enable selected trigger explicitely, with STORE TRIGGER.
http://www.slony.info/documentation/stmtstoretrigger.html
--
Filip Rembiałkowski
JID,mailto:[email protected]
http://filip.rembialkowski.net/
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
