SEO

On cvs head, I can get "tuple concurrently updated" if two separate
transactions are both trying to drop the same index:

8<----------------------------
<psql session #1>
contrib_regression=# create table t(f1 int);
CREATE TABLE
contrib_regression=# create index idx1 on t(f1);
CREATE INDEX
contrib_regression=# begin;
BEGIN
contrib_regression=# drop index idx1;
DROP INDEX

<psql session #2>
contrib_regression=# drop index idx1;

<psql session #1>
contrib_regression=# commit;
COMMIT

<psql session #2>
ERROR: tuple concurrently updated
8<----------------------------


The backtrace for session #2 looks like:
8<----------------------------
#0 errfinish (dummy=0) at elog.c:293
#1 0x00000000006fb15d in elog_finish (elevel=20, fmt=0x73bda1 "tuple
concurrently updated") at elog.c:937
#2 0x000000000046c25f in simple_heap_delete (relation=0x2aaaaaac4990,
tid=0xba8e44) at heapam.c:2006
#3 0x00000000004c15cb in recursiveDeletion (object=0x7fffb9f7e5e0,
behavior=DROP_RESTRICT, msglevel=18,
callingObject=0x0, oktodelete=0xba90a0, depRel=0x2aaaaaac4990,
alreadyDeleted=0x0) at dependency.c:657
#4 0x00000000004c0be5 in performDeletion (object=0x7fffb9f7e5e0,
behavior=DROP_RESTRICT) at dependency.c:177
#5 0x0000000000533863 in RemoveIndex (relation=0xba8d78,
behavior=DROP_RESTRICT) at indexcmds.c:1133
#6 0x00000000006474d5 in ProcessUtility (parsetree=0xb7dbd0,
queryString=0xba8ce8 "drop index idx1;", params=0x0,
isTopLevel=1 '\001', dest=0xb7dc68, completionTag=0x7fffb9f7eb40
"") at utility.c:608
#7 0x0000000000645bf7 in PortalRunUtility (portal=0xbb0d08,
[...]
8<----------------------------

The comments for simple_heap_delete say:

/*
* simple_heap_delete - delete a tuple
*
* This routine may be used to delete a tuple when concurrent updates of
* the target tuple are not expected (for example, because we have a
* lock on the relation associated with the tuple). Any failure is
* reported via ereport().
*/

So the question is -- is the comment wrong, or is this a bug, or am I
not just plain misunderstanding something ;-)

The reason I ask is that someone contacted me who is seeing this on a
production system, and the abort on session #2 is rolling back a large
bulkload.

Thanks,

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Joe Conway <mail@joeconway.com> writes:
> On cvs head, I can get "tuple concurrently updated" if two separate
> transactions are both trying to drop the same index:

This seems related to the discussions we had awhile back about how
deletion needs to take locks *before* it starts doing anything.
http://archives.postgresql.org/pgsql...1/msg00937.php
http://archives.postgresql.org/pgsql...3/msg00143.php

Notice that recursiveDeletion() tries to clean out pg_depend before
it actually deletes the target object, and in the current code that
object-specific subroutine is the only thing that takes any sort of lock.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 7/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Notice that recursiveDeletion() tries to clean out pg_depend before
> it actually deletes the target object, and in the current code that
> object-specific subroutine is the only thing that takes any sort of lock.

In the past 4-6 months, we've seen 4 cases that look like logical
catalog corruption in the area of dependencies and, likely, concurrent
activity. I don't recall the exact releases off-hand, but 1 case was
on 8.1 and the others were on 8.2. In two of the cases, there were
DDL changes while running Slony (which we originally attributed to
Slony). The other two cases were normal DDL including ALTER TABLE ADD
COLUMN, CREATE OR REPLACE VIEW, and REINDEX.

Unfortunately, we were not able to acquire a forensic copy of the
databases for further analysis. In all cases, the hardware behaved
and tested fine. But, it looks dependency-related as in all cases,
dependency records existed for views and indexes which no longer
existed in pg_class (the files and records were gone)

Sorry I don't have more information available right now, but this is
an area that requires further investigation.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, 2007-07-11 at 16:49 -0700, Joe Conway wrote:
> On cvs head, I can get "tuple concurrently updated" if two separate
> transactions are both trying to drop the same index:

> ERROR: tuple concurrently updated

> The reason I ask is that someone contacted me who is seeing this on a
> production system, and the abort on session #2 is rolling back a large
> bulkload.

This situation will always cause an error on session #2, since if you
lock this more closely you'll get an ERROR because the object does not
exist.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Simon Riggs wrote:
> On Wed, 2007-07-11 at 16:49 -0700, Joe Conway wrote:
>> On cvs head, I can get "tuple concurrently updated" if two separate
>> transactions are both trying to drop the same index:
>
>> ERROR: tuple concurrently updated
>
>> The reason I ask is that someone contacted me who is seeing this on a
>> production system, and the abort on session #2 is rolling back a large
>> bulkload.
>
> This situation will always cause an error on session #2, since if you
> lock this more closely you'll get an ERROR because the object does not
> exist.

That's a really good point. In any case I had already asked them to look
into moving the index related DDL outside the data loading transaction.

Thanks,

Joe


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate