Slony DDL/DML Change and "attempted to lock invisible tuple" PG Error
am 25.06.2010 15:31:37 von Bob LunneyI'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 (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin