Hey,

I'm having a real problem getting this to work. What I'm trying to do is to use a trigger on the replicated table to reformat the information and add this to a dable in the same database, but a separate schema. The trigger is firing, and the data is formatted into an INSERT statement that is EXECUTEd. This fails with a duplicate key ( on the remote table ) error.

If I cut/paste the statement in error, and login to the replicated database I can add the row to the non-replicated table with no problem at all, so it must be an environmental thing that's wrong, not the data???

If I drop the trigger, the replication works great.

This is driving me crazy! Can anyone suggest where I'm going wrong: It's postgres 8.3.6 and slony 2.0.0.

Many thanks IA!

Steve

Here's the slony log (anonymised, so smelling misfakes will be mine)...

2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=1.2.3.4 user=postgres password=password sslmode=require" is 80306
2009-02-24 10:22:34 NZDT CONFIG enableNode: no_id=1
2009-02-24 10:22:34 NZDT INFO   remoteWorkerThread_1: thread starts
2009-02-24 10:22:34 NZDT INFO   remoteListenThread_1: thread starts
2009-02-24 10:22:34 NZDT CONFIG cleanupThread: thread starts
2009-02-24 10:22:34 NZDT CONFIG cleanupThread: bias = 35383
2009-02-24 10:22:34 NZDT INFO   syncThread: thread starts
2009-02-24 10:22:34 NZDT INFO   main: running scheduler mainloop
2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=localhost  user=postgres password=password sslmode=require" is 80306
2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=1.2.3.4 user=postgres password=password sslmode=require" is 80306
2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=1.2.3.4 user=postgres password=password sslmode=require" is 80306
2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=1.2.3.4 user=postgres password=password sslmode=require" is 80306
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: update provider configuration
TODO: ********** remoteWorkerThread: node 1 - EVENT 1,6 STORE_NODE - unknown event type
2009-02-24 10:22:34 NZDT CONFIG storeListen: li_origin=1 li_receiver=10 li_provider=1
TODO: ********** remoteWorkerThread: node 1 - EVENT 1,7 ENABLE_NODE - unknown event type
2009-02-24 10:22:34 NZDT CONFIG storeListen: li_origin=1 li_receiver=10 li_provider=1
2009-02-24 10:22:34 NZDT CONFIG storeListen: li_origin=1 li_receiver=10 li_provider=1
2009-02-24 10:22:34 NZDT CONFIG storeSubscribe: sub_set=1 sub_provider=1 sub_forward='t'
2009-02-24 10:22:34 NZDT CONFIG storeListen: li_origin=1 li_receiver=10 li_provider=1
2009-02-24 10:22:34 NZDT INFO   copy_set 1
2009-02-24 10:22:34 NZDT CONFIG version for "dbname=dbase host=localhost  user=postgres password=password sslmode=require" is 80306 
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: connected to provider DB
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: prepare to copy table "dbase"."table1"
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: prepare to copy table "dbase"."table2"
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: prepare to copy table "dbase"."table3"
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: all tables for set 1 found on subscriber
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: copy table "dbase"."table1"
2009-02-24 10:22:34 NZDT CONFIG remoteWorkerThread_1: Begin COPY of table "dbase"."table1"
NOTICE:  truncate of "dbase"."table1" succeeded
2009-02-24 10:22:41 NZDT ERROR  remoteWorkerThread_1: copy from stdin on local node - PGRES_FATAL_ERROR ERROR:  duplicate key value violates unique constraint "remote_data1_key"
CONTEXT:  SQL statement "insert into remote.remtab(col1,col2,col3) values ('data1', 'data2', 'data3')"
PL/pgSQL function "addtoremtab" line 35 at EXECUTE statement

Line 35 of the trigger is ( after the parts are catenated  )

EXECUTE 'insert into remote.remtab(col1,col2,col3) values (''data1'', ''data2'', ''data3'')';


remtab is ( created in a remote schema which is in the search path for postgres )

CREATE TABLE remtab (
    id integer NOT NULL,
    data1 character varying(255) NOT NULL,
    data2 character varying(255) NOT NULL,
    data3 character varying(255) NOT NULL
);

CREATE SEQUENCE remtab_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER SEQUENCE remtab_id_seq OWNED BY remtab.id;

ALTER TABLE remtab ALTER COLUMN id SET DEFAULT nextval('remtab.remtab_id_seq'::regclass);

ALTER TABLE ONLY remtab
    ADD CONSTRAINT remote_data1_key UNIQUE (data1);


-- 
Steve Holdoway <steve at greengecko.co.nz>