"attempted to lock invisible tuple" error while update

On Thu, Jul 8, 2010 at 10:56 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

I am using a  cluster setup  with two nodes in it. Replication between two
nodes is being done through slony.

Postgres version is 8.1.2 and slony version is 1.1.5 .

I am running an operation that does thousands of update/inserts/delete on
some tables. While running update query on a

particular table let say <abc> , it gives error “attempted to lock invisible
tuple” and fails. This update query is supposed to update

 hundreds of record. Sometimes it gives the error while  running this update
query and sometimes not. I have seen postgres logs of two

 instances when it fails due to “attempted to lock invisible tuple” error ,
in these two instances while it was doing an update  on <abc> table,

 another query was going on the same table which was updating a particular
record. Can this (two updates at the same time on the same table) be the
reason for

this error  in some way ( just a guess) . If not, then what can be the
reason of this error ??

Not sure what the cause is but have you tried updating to the latest
8.1.x version?

Hi Scott

Thanks for your reply . I haven't yet tried updating to latest 8.1.x version. Was juss googling about this error and came across
a link discussing the same issue :

http://groups.google.com/group/pgsql.general/browse_thread/thread/75df15648bcb502b/10232d1f183a640a?lnk=raot

In this , the problem had occurred on 8.4.1 and a patch (snapmgr-bugfix-rehash-2.patch ) was provided on 8.4.1 to circumvent this problem.

This patch worked fine . I was just wondering if this patch
is backward compatible and can it be merged with 8.1.2
to get it working. Any suggestions ??

Thanks
Tamanna

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, July 09, 2010 9:35 PM
To: tamanna madaan
Cc: pgsql-general@postgresql.org; Tapin Agarwal
Subject: Re: [GENERAL] "attempted to lock invisible tuple" error while update

On Thu, Jul 8, 2010 at 10:56 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

I am using a  cluster setup  with two nodes in it. Replication between two
nodes is being done through slony.

Postgres version is 8.1.2 and slony version is 1.1.5 .

I am running an operation that does thousands of update/inserts/delete on
some tables. While running update query on a

particular table let say <abc> , it gives error "attempted to lock invisible
tuple" and fails. This update query is supposed to update

 hundreds of record. Sometimes it gives the error while  running this update
query and sometimes not. I have seen postgres logs of two

 instances when it fails due to "attempted to lock invisible tuple" error ,
in these two instances while it was doing an update  on <abc> table,

 another query was going on the same table which was updating a particular
record. Can this (two updates at the same time on the same table) be the
reason for

this error  in some way ( just a guess) . If not, then what can be the
reason of this error ??

Not sure what the cause is but have you tried updating to the latest
8.1.x version?

On Mon, Jul 12, 2010 at 11:54 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi Scott

Thanks for your reply . I haven't yet tried updating to latest 8.1.x version. Was juss googling about this error and came across
a link discussing the same issue :

http://groups.google.com/group/pgsql.general/browse_thread/thread/75df15648bcb502b/10232d1f183a640a?lnk=raot

In this , the problem had occurred on 8.4.1 and a patch (snapmgr-bugfix-rehash-2.patch ) was provided on 8.4.1 to circumvent this problem.

This patch worked fine . I was just wondering if this patch
is backward compatible and can it be merged with 8.1.2
to get it working. Any suggestions ??

Check the release notes for the 8.1 version that came our around the
same time as 8.4.2? I'd expect if it's a known issue that affected
all versions the fix got put into the older versions as well, unless
it was considered a more dangerous thing to fix there.

Hi Scott

I looked into the release notes of 8.4.2 and found the following fix in
the fix list for 8.4.2 :

------------------------------------------------------------------------
----
Ensure that a cursor's snapshot is not modified after it is created
(Alvaro)

This could lead to a cursor delivering wrong results if later operations
in the same transaction modify the data the cursor is supposed to
return.
------------------------------------------------------------------------
----

The same fix is not included in fix list for postgres-8.1.19 which came
at the same time when postgres-8.4.2 was released i.e 14th Dec.,2009.
Its not there in any of the 8.1 releases after that i.e 8.1.20 and 21.

So , it seems that this fix is not included in 8.1. Do you have any
idea what could be the reason.

Thanks..
Tamanna

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tuesday, July 13, 2010 1:14 PM
To: tamanna madaan
Cc: pgsql-general@postgresql.org; Tapin Agarwal
Subject: Re: [GENERAL] "attempted to lock invisible tuple" error while
update

On Mon, Jul 12, 2010 at 11:54 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi Scott

Thanks for your reply . I haven't yet tried updating to latest 8.1.x

version. Was juss googling about this error and came across

a link discussing the same issue :

http://groups.google.com/group/pgsql.general/browse_thread/thread/75df15
648bcb502b/10232d1f183a640a?lnk=raot

In this , the problem had occurred on 8.4.1 and a patch

(snapmgr-bugfix-rehash-2.patch ) was provided on 8.4.1 to circumvent
this problem.

This patch worked fine . I was just wondering if this patch
is backward compatible and can it be merged with 8.1.2
to get it working. Any suggestions ??

Check the release notes for the 8.1 version that came our around the
same time as 8.4.2? I'd expect if it's a known issue that affected
all versions the fix got put into the older versions as well, unless
it was considered a more dangerous thing to fix there.

tamanna madaan wrote:

The same fix is not included in fix list for postgres-8.1.19 which came
at the same time when postgres-8.4.2 was released i.e 14th Dec.,2009.
Its not there in any of the 8.1 releases after that i.e 8.1.20 and 21.

See http://archives.postgresql.org/pgsql-committers/2009-10/msg00004.php
; that was a bug specific to 8.4 that shouldn't be present in the
earlier versions.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us

Excerpts from Greg Smith's message of mié jul 14 09:52:46 -0400 2010:

tamanna madaan wrote:

The same fix is not included in fix list for postgres-8.1.19 which came
at the same time when postgres-8.4.2 was released i.e 14th Dec.,2009.
Its not there in any of the 8.1 releases after that i.e 8.1.20 and 21.

See http://archives.postgresql.org/pgsql-committers/2009-10/msg00004.php
; that was a bug specific to 8.4 that shouldn't be present in the
earlier versions.

Specifically, the bug was on code that didn't exist back in 8.1.

On Wed, Jul 14, 2010 at 2:14 AM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi Scott

I looked into the release notes of 8.4.2 and found the following fix in
the fix list for 8.4.2 :

Your first priority should be updating to the latest 8.1 version
available. While it may or may not have had release notes made about
it, it's quite likely you're being bitten by a fixed bug. Why torture
yourself? Pgsql is the best package of any bit of kit I've ever used
about NOT introducing new and broken things in updates. Upgrade to
8.1.latest first.