Обсуждение: "tuple concurrently updated" during index deletion
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 ont(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). Anyfailure 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
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-hackers/2007-01/msg00937.php http://archives.postgresql.org/pgsql-bugs/2007-03/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
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/
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
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