Обсуждение: "attempted to lock invisible tuple" error while update
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 ??
Please help.
Thanks..
Tamanna
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 cameacross 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 circumventthis 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 cameacross > 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 circumventthis 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.