Thread: Slony DDL/DML Change and "attempted to lock invisible tuple" PG Error
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 (addinga column and updating some rows) worked on the master but failed on the slave, with the PG database on the slave throwingan "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 updatingthe sl_event entry on the master node, changing it to a SYNC record, dropping the table in question from the replicationset, applying the DDL/DML manually on the slave, then re-adding the table to the replication set. (All of whichworked 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, Slony2.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_dbhost=10.192.2.1 port=41547 Thanks in advance for your help! Regards, Bob Lunney
Re: Slony DDL/DML Change and "attempted to lock invisible tuple" PG Error
From
"Joshua D. Drake"
Date:
On Fri, 2010-06-25 at 06:31 -0700, Bob Lunney wrote: > I'm not sure if this is really a bug, so I'm posting here instead of pgsql-bugs. I am thinking this needs to be posted to: http://lists.slony.info/mailman/listinfo/slony1-general JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
Re: Slony DDL/DML Change and "attempted to lock invisibletuple" PG Error
From
"Joshua D. Drake"
Date:
On Fri, 2010-06-25 at 06:31 -0700, Bob Lunney wrote: > I'm not sure if this is really a bug, so I'm posting here instead of pgsql-bugs. I am thinking this needs to be posted to: http://lists.slony.info/mailman/listinfo/slony1-general JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering