Thread: BUG #2166: attempted to update invisible tuple
The following bug has been logged online: Bug reference: 2166 Logged by: Euler Taveira de Oliveira Email address: eulerto@yahoo.com.br PostgreSQL version: 8.1+ Operating system: Slackware 10.1 Description: attempted to update invisible tuple Details: I just execute the same transaction in 2 different backends and I got the message: 'attempted to update invisible tuple'. In the first transaction I execute: BEGIN - UPDATE and then open the other transaction and execute: BEGIN - UPDATE so I commit the first one. In 8.0 it executes fine. ========== first transaction ========================= tst=# begin; BEGIN tst=# update produtos set estoque = estoque - itensmov.qtde from itensmov where trim(itensmov.codigo) = 'C0001' and itensmov.fk_produto = produtos.id_produto and produtos.controlarestoque = 'S'; UPDATE 21 tst=# commit; COMMIT tst=# ========== first transaction ========================= ========== second transaction ======================== tst=# begin; BEGIN tst=# update produtos set estoque = estoque - itensmov.qtde from itensmov where trim(itensmov.codigo) = 'C0003' and itensmov.fk_produto = produtos.id_produto and produtos.controlarestoque = 'S'; ERRO: attempted to update invisible tuple tst=# ========== second transaction ========================
"Euler Taveira de Oliveira" <eulerto@yahoo.com.br> writes:
> I just execute the same transaction in 2 different backends and I got the
> message: 'attempted to update invisible tuple'.
If you want this investigated then you'll need to provide a
self-contained example. We have other things to do than guess about
the contents of your database...
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> escreveu: > "Euler Taveira de Oliveira" <eulerto@yahoo.com.br> writes: > > I just execute the same transaction in 2 different backends and I > got the > > message: 'attempted to update invisible tuple'. > > If you want this investigated then you'll need to provide a > self-contained example. We have other things to do than guess about > the contents of your database... > Sorry for that. I didn't have the data yet. Some data to test it is in: http://www.timbira.com/tst2.sql.gz In 8.0 and previous I didn't reproduce it. Is it something related to UPDATE ... FROM patch that I submitted? I just execute the same transaction in 2 different backends and I got the message: 'attempted to update invisible tuple'. In the first transaction I execute: BEGIN - UPDATE and then open the other transaction and execute: BEGIN - UPDATE so I commit the first one and got that message. ========== first transaction ========================= tst=# begin; BEGIN tst=# update produtos set estoque = estoque - itensmov.qtde from itensmov where trim(itensmov.codigo) = 'C0001' and itensmov.fk_produto = produtos.id_produto and produtos.controlarestoque = 'S'; UPDATE 21 tst=# commit; COMMIT tst=# ========== first transaction ========================= ========== second transaction ======================== tst=# begin; BEGIN tst=# update produtos set estoque = estoque - itensmov.qtde from itensmov where trim(itensmov.codigo) = 'C0003' and itensmov.fk_produto = produtos.id_produto and produtos.controlarestoque = 'S'; ERRO: attempted to update invisible tuple tst=# ========== second transaction ======================== Euler Taveira de Oliveira euler[at]yahoo_com_br _______________________________________________________ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html
Euler Taveira de Oliveira <eulerto@yahoo.com.br> writes: > Sorry for that. I didn't have the data yet. Some data to test it is in: > http://www.timbira.com/tst2.sql.gz Thanks. > In 8.0 and previous I didn't reproduce it. Which versions did you try exactly? I've reproduced it in 8.0.6 and HEAD. > Is it something related to UPDATE ... FROM patch that I submitted? No, it looks to me like it's a problem with this patch: 2005-08-19 20:39 tgl * src/: backend/access/heap/heapam.c, backend/commands/async.c, backend/commands/trigger.c, backend/commands/vacuum.c, backend/executor/execMain.c, backend/utils/time/tqual.c, include/access/heapam.h, include/access/htup.h, include/executor/executor.h, include/utils/tqual.h: Repair problems with VACUUM destroying t_ctid chains too soon, and with insufficient paranoia in code that follows t_ctid links. I think EvalPlanQual is deciding that updated tuples are valid in some cases where it shouldn't. Unfortunately, if that's correct it means that all the branches are broken since last August :-( regards, tom lane
> > I think EvalPlanQual is deciding that updated tuples are valid in > some cases where it shouldn't. Unfortunately, if that's correct it > means that all the branches are broken since last August :-( > > regards, tom lane > i reproduced it in 8.1.1, so i guess you are right in tell that old branches are broken... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
I wrote:
> I think EvalPlanQual is deciding that updated tuples are valid in
> some cases where it shouldn't. Unfortunately, if that's correct it
> means that all the branches are broken since last August :-(
OK, here's the deal: the former coding of EvalPlanQual ignored (returned
NULL for) any tuple that was determined to have been updated by the
current transaction. This is clearly wrong --- tuples updated by
previous commands of the current transaction should be visible for
further modification. However, the new coding is also wrong --- tuples
already updated by the current command of the current transaction should
be ignored, and they aren't being. The reason Euler's query is failing
is that there are multiple "itensmov" rows joining to some rows of
"produtos", leading to multiple attempts to update the row.
(BTW, this raises the question whether his query is really correct or
not, but I think we need to restore the previous behavior where the
first update attempt succeeds and additional updates in the same command
are just dropped. This is what happens when EvalPlanQual is not entered
because there are no concurrent transactions, so we want to make the
concurrent case work the same.)
It looks to me like the correct test requires passing in the current
command ID so we can check the tuple's cmax against it.
regards, tom lane
I wrote:
> It looks to me like the correct test requires passing in the current
> command ID so we can check the tuple's cmax against it.
Er, cmin not cmax. Here's the patch against 8.1 if you need it.
regards, tom lane
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.195.2.1
diff -c -r1.195.2.1 trigger.c
*** src/backend/commands/trigger.c 22 Nov 2005 18:23:07 -0000 1.195.2.1
--- src/backend/commands/trigger.c 12 Jan 2006 21:10:09 -0000
***************
*** 1736,1742 ****
epqslot = EvalPlanQual(estate,
relinfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax);
if (!TupIsNull(epqslot))
{
*tid = update_ctid;
--- 1736,1743 ----
epqslot = EvalPlanQual(estate,
relinfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax,
! cid);
if (!TupIsNull(epqslot))
{
*tid = update_ctid;
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.256.2.4
diff -c -r1.256.2.4 execMain.c
*** src/backend/executor/execMain.c 22 Nov 2005 18:23:08 -0000 1.256.2.4
--- src/backend/executor/execMain.c 12 Jan 2006 21:10:09 -0000
***************
*** 1219,1225 ****
newSlot = EvalPlanQual(estate,
erm->rti,
&update_ctid,
! update_xmax);
if (!TupIsNull(newSlot))
{
slot = newSlot;
--- 1219,1226 ----
newSlot = EvalPlanQual(estate,
erm->rti,
&update_ctid,
! update_xmax,
! estate->es_snapshot->curcid);
if (!TupIsNull(newSlot))
{
slot = newSlot;
***************
*** 1527,1533 ****
epqslot = EvalPlanQual(estate,
resultRelInfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax);
if (!TupIsNull(epqslot))
{
*tupleid = update_ctid;
--- 1528,1535 ----
epqslot = EvalPlanQual(estate,
resultRelInfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax,
! estate->es_snapshot->curcid);
if (!TupIsNull(epqslot))
{
*tupleid = update_ctid;
***************
*** 1679,1685 ****
epqslot = EvalPlanQual(estate,
resultRelInfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax);
if (!TupIsNull(epqslot))
{
*tupleid = update_ctid;
--- 1681,1688 ----
epqslot = EvalPlanQual(estate,
resultRelInfo->ri_RangeTableIndex,
&update_ctid,
! update_xmax,
! estate->es_snapshot->curcid);
if (!TupIsNull(epqslot))
{
*tupleid = update_ctid;
***************
*** 1826,1831 ****
--- 1829,1835 ----
* rti - rangetable index of table containing tuple
* *tid - t_ctid from the outdated tuple (ie, next updated version)
* priorXmax - t_xmax from the outdated tuple
+ * curCid - command ID of current command of my transaction
*
* *tid is also an output parameter: it's modified to hold the TID of the
* latest version of the tuple (note this may be changed even on failure)
***************
*** 1835,1841 ****
*/
TupleTableSlot *
EvalPlanQual(EState *estate, Index rti,
! ItemPointer tid, TransactionId priorXmax)
{
evalPlanQual *epq;
EState *epqstate;
--- 1839,1845 ----
*/
TupleTableSlot *
EvalPlanQual(EState *estate, Index rti,
! ItemPointer tid, TransactionId priorXmax, CommandId curCid)
{
evalPlanQual *epq;
EState *epqstate;
***************
*** 1912,1917 ****
--- 1916,1939 ----
}
/*
+ * If tuple was inserted by our own transaction, we have to check
+ * cmin against curCid: cmin >= curCid means our command cannot
+ * see the tuple, so we should ignore it. Without this we are
+ * open to the "Halloween problem" of indefinitely re-updating
+ * the same tuple. (We need not check cmax because
+ * HeapTupleSatisfiesDirty will consider a tuple deleted by
+ * our transaction dead, regardless of cmax.) We just checked
+ * that priorXmax == xmin, so we can test that variable instead
+ * of doing HeapTupleHeaderGetXmin again.
+ */
+ if (TransactionIdIsCurrentTransactionId(priorXmax) &&
+ HeapTupleHeaderGetCmin(tuple.t_data) >= curCid)
+ {
+ ReleaseBuffer(buffer);
+ return NULL;
+ }
+
+ /*
* We got tuple - now copy it for use by recheck query.
*/
copyTuple = heap_copytuple(&tuple);
Index: src/include/executor/executor.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/executor/executor.h,v
retrieving revision 1.120.2.1
diff -c -r1.120.2.1 executor.h
*** src/include/executor/executor.h 23 Nov 2005 20:28:05 -0000 1.120.2.1
--- src/include/executor/executor.h 12 Jan 2006 21:10:10 -0000
***************
*** 98,104 ****
extern void ExecConstraints(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate);
extern TupleTableSlot *EvalPlanQual(EState *estate, Index rti,
! ItemPointer tid, TransactionId priorXmax);
/*
* prototypes from functions in execProcnode.c
--- 98,104 ----
extern void ExecConstraints(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate);
extern TupleTableSlot *EvalPlanQual(EState *estate, Index rti,
! ItemPointer tid, TransactionId priorXmax, CommandId curCid);
/*
* prototypes from functions in execProcnode.c