I'm not sure if this is really a bug, so I'm posting here instead of pgsql-bugs.
Last night I attempted to make a DDL and DML change through Slony, using the
execute script command. The changes (adding a column and updating some rows)
worked on the master but failed on the slave, with the PG database on the slave
throwing an "attempted to lock invisible tuple" error. Neither DDL or DML
change stuck on the slave, but did on the master.
Slony, of course, restarted its worker thread and tried again (and again and
again...). I stopped the retries by updating the sl_event entry on the master
node, changing it to a SYNC record, dropping the table in question from the
replication set, applying the DDL/DML manually on the slave, then re-adding the
table to the replication set. (All of which worked fine, BTW.)
So, the question is: Is this a Slony or a PostgreSQL problem, and what
should/can I do about it?
I'm running SLES 10 (Linux slave1 2.6.16.60-0.33-smp #1 SMP Fri Oct 31 14:24:07
UTC 2008 x86_64 x86_64 x86_64 GNU/Linux), PostgreSQL 8.4.1 (PostgreSQL 8.4.1 on
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux),
64-bit), and Slony 2.0.3-rc2. This setup has been running fine for 5 months
under very heavy daily load. (BTW, Slony 2.0.3-rc2 has been working great
replicating data and servicing DDL requests just fine, with no problems up to
now, but I'm still going to test 2.0.4 and upgrade if the test results pan out).
The Slony log contains:
2010-06-24 18:06:09 EDT CONFIG remoteWorkerThread_1: DDL request with 7
statements
2010-06-24 18:06:09 EDT CONFIG remoteWorkerThread_1: DDL Statement 0: [
-- -*- SQL -*-
set session authorization main_usr;]
2010-06-24 18:06:09 EDT CONFIG DDL success - PGRES_COMMAND_OK
2010-06-24 18:06:09 EDT CONFIG remoteWorkerThread_1: DDL Statement 1: [
set search_path to public;]
2010-06-24 18:06:09 EDT CONFIG DDL success - PGRES_COMMAND_OK
2010-06-24 18:06:09 EDT CONFIG remoteWorkerThread_1: DDL Statement 2: [
alter table public.rte add column dols boolean default false not null;]
2010-06-24 18:06:09 EDT CONFIG DDL success - PGRES_COMMAND_OK
2010-06-24 18:06:09 EDT CONFIG remoteWorkerThread_1: DDL Statement 3: [
update public.rte set dols = true where mctr in ('AA', 'YY');]
2010-06-24 18:06:09 EDT ERROR DDL Statement failed - PGRES_FATAL_ERROR
2010-06-24 18:06:09 EDT INFO slon: retry requested
2010-06-24 18:06:09 EDT INFO slon: notify worker process to shutdown
The relevant PG log entries are:
10.192.2.1(41547):2010-06-24 18:06:09.913 EDT:LOG: statement:
alter table public.rte add column dols boolean default false not null;
10.192.2.1(41547):2010-06-24 18:06:09.935 EDT:ERROR: attempted to lock
invisible tuple
10.192.2.1(41547):2010-06-24 18:06:09.935 EDT:STATEMENT:
update public.rte set dols = true where mctr in ('AA', 'YY');
10.192.2.1(41547):2010-06-24 18:06:09.958 EDT:LOG: unexpected EOF on client
connection
10.192.2.1(41547):2010-06-24 18:06:09.958 EDT:LOG: disconnection: session
time: 0:00:00.071 user=rep_usr database=main_db host=10.192.2.1 port=41547
Thanks in advance for your help!
Regards,
Bob Lunney
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
