Slony1 PostgreSQL Replication - slony replication problem

Author

slony replication problem

Robert Ngo

2005-09-16, 11:24 am

Hi,

I have installed postgresql 7.4.8 from source and then install slony on
both master and slave node. i follow the example given in
http://gborg.postgresql.org/project...php?howto_basic

i run pgbench and start slon on both side to start the replication,
there is error at the slavenode

ERROR remoteWorkerThread_1
: "delete from only public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005001';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005002';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005003';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005004';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005005';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005006';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005007';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005008';
delete from only
public.history where
"_Slony- I_slony_example_rowI
D" ='1000000000005009';
delete from only
public.history where "_Slony- I_slony_example_rowI
D" ='1000000000005010';
"
ERROR: Slony-I: logTrigger() called in non-client session
- qualification was: where log_origin = 1 and ( (
log_tableid in (4,3,2,1)
and (log_xid < '22068')
and (log_xid >= '10910')
) )
ERROR remoteWorkerThread_1
: helper 1 finished with error
ERROR remoteWorkerThread_1
: SYNC aborted

why slony have problem with removing the old record?

Christopher Browne

2005-09-16, 8:24 pm

Robert Ngo wrote:

> Hi,
>
> I have installed postgresql 7.4.8 from source and then install slony
> on both master and slave node. i follow the example given in
> http://gborg.postgresql.org/project...php?howto_basic
>
> i run pgbench and start slon on both side to start the replication,
> there is error at the slavenode
>
> ERROR remoteWorkerThread_1
: "delete from only public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005001';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005002';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005003';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005004';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005005';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005006';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005007';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005008';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005009';
delete from only
> public.history where
> "_Slony- I_slony_example_rowI
D" ='1000000000005010';
" ERROR: Slony-I:
> logTrigger() called in non-client session
> - qualification was: where log_origin = 1 and ( (
> log_tableid in (4,3,2,1)
> and (log_xid < '22068')
> and (log_xid >= '10910')
> ) )
> ERROR remoteWorkerThread_1
: helper 1 finished with error
> ERROR remoteWorkerThread_1
: SYNC aborted
>
> why slony have problem with removing the old record?

This is a case that shouldn't happen.

Take a look at the schema for table public.history on both nodes.

What you *should* see looks like the following:

On the origin:

/* cbbrowne@[local]/dba2 slony_test1=*/ \d history
Table "public.history"
Column | Type |
Modifiers
-------------------+-----------------------------+------------------------------------------------------
tid | integer |
bid | integer |
aid | integer |
delta | integer |
mtime | timestamp without time zone |
filler | character(22) |
_Slony-I_T1_rowID | bigint | not null default
nextval('"_T1". sl_rowid_seq'::text)

Indexes:
"history__Slony-I_T1_rowID_key" UNIQUE, btree ("_Slony-I_T1_rowID")
Triggers:
"_T1_logtrigger_4" AFTER INSERT OR DELETE OR UPDATE ON history FOR
EACH ROW EXECUTE PROCEDURE "_T1".logtrigger('_T1', '4', 'vvvvvvk')

Then, switching to a subscriber...
/* cbbrowne@[local]/dba2 slony_test1=*/ \c slony_test2
You are now connected to database "slony_test2".
/* cbbrowne@[local]/dba2 slony_test2=*/ \d history
Table "public.history"
Column | Type |
Modifiers
-------------------+-----------------------------+------------------------------------------------------
tid | integer |
bid | integer |
aid | integer |
delta | integer |
mtime | timestamp without time zone |
filler | character(22) |
_Slony-I_T1_rowID | bigint | not null default
nextval('"_T1". sl_rowid_seq'::text)

Indexes:
"history__Slony-I_T1_rowID_key" UNIQUE, btree ("_Slony-I_T1_rowID")
Triggers:
"_T1_denyaccess_4" BEFORE INSERT OR DELETE OR UPDATE ON history FOR
EACH ROW EXECUTE PROCEDURE "_T1".denyaccess('_T1')

What is evidently happening is that there is a logtrigger() on a
subscriber, which oughtn't be the case.

The only way I can think of for this to happen would be if you had a
table where you tried to share it between two clusters, and so the table
had both sorts of triggers attached to it...

Sponsored Links