Thread: simple_heap_update: tuple concurrently updated -- during INSERT
Dear Gurus, I tried to shuffle through the archives but was lost in the technical details. Please feel free to tell me a search keyword suitable for my case if there's any. QUESTION1: is this error _always_ harmless (other than transaction rollback)? QUESTION2: is this reported exactly like other errors? i.e. in psql, through psqlodbc, etc. As you can see below, this came during an INSERT. I don't really understand the warning. QUESTION3: May I assume that the transaction aborted even before it began? QUESTION4: If that's so, does it rule out triggers and such? QUESTION5: May it be a problem in 7.3.x that's fixed in 7.4.y? System: PostgreSQL 7.3.4, Dual Xeon HT Log: ERROR: simple_heap_update: tuple concurrently updatedog. LOG: statement: INSERT INTO pakolas_cikktetel (cikk, minoseg,helyrol, mennyi, pakolas, sorszam, helyre) VALUES (102165, 1, 1488, '25', 68615, 1, 1338) DEBUG: AbortCurrentTransactionWARNING: AbortTransaction and not in in-progress state I read in an earlier post that turning on statement logging and duration may help. If we can reproduce this error, I will. So far this is the only occurence. G. %----------------------- cut here -----------------------% \end
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> ERROR: simple_heap_update: tuple concurrently updatedog.
> LOG: statement: INSERT INTO pakolas_cikktetel
> (cikk, minoseg, helyrol, mennyi, pakolas, sorszam, helyre) VALUES
> (102165, 1, 1488, '25', 68615, 1, 1338)
Hmm. AFAIK, an INSERT statement per se will never call
simple_heap_update at all --- that routine is only used for system
catalog updates. I'm wondering what is going on behind the scenes
here. Have you got any triggers or rules that this INSERT would
fire, and if so what do they do? Maybe you are using NOTIFY?
regards, tom lane
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> Q1. So is this everything that can be said -- NOTIFY calls
> simple_heap_update that is concurrently updated by a different transaction?
If that's what it is, then there's still a question: why? The notify
code has enough locking that this failure shouldn't happen. If you can
reproduce this I'd like to look into it.
regards, tom lane
Dear Tom,
Thanks for your reply.
Thinking about it, yes; there are triggers that (may) do updates on this
table, and there is a master table "pakolas" ("pakolas_cikktetel" is a
detail of it) that I touch, and yes, it has a NOTIFY in AFTER trigger. (that
is one of the causes I touch that table ;) )
Q1. So is this everything that can be said -- NOTIFY calls
simple_heap_update that is concurrently updated by a different transaction?
Q2. I'm not sure if it's a good idea, but if the transaction simply fails
due to a NOTIFY, can I hack around it so that it won't ruin the entire
transaction, only NOTIFY?
Q3. Is there a way to degrade this error to warning or maybe notice (without
recompiling the server source ;) )?
Thanks again,
G.
%----------------------- cut here -----------------------%
\end
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "SZŰCS Gábor" <surrano@mailbox.hu>
Cc: "PGH" <pgsql-hackers@postgresql.org>
Sent: Wednesday, June 09, 2004 5:23 PM
Subject: Re: [HACKERS] simple_heap_update: tuple concurrently updated --
during INSERT
> "SZŰCS Gábor" <surrano@mailbox.hu> writes:
> > ERROR: simple_heap_update: tuple concurrently updated.
> > LOG: statement: INSERT INTO pakolas_cikktetel
> > (cikk, minoseg, helyrol, mennyi, pakolas, sorszam, helyre) VALUES
> > (102165, 1, 1488, '25', 68615, 1, 1338)
>
> Hmm. AFAIK, an INSERT statement per se will never call
> simple_heap_update at all --- that routine is only used for system
> catalog updates. I'm wondering what is going on behind the scenes
> here. Have you got any triggers or rules that this INSERT would
> fire, and if so what do they do? Maybe you are using NOTIFY?
>
> regards, tom lane
>
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> A final question: as far as you can remember, may this be an issue already
> fixed in later versions?
I should have looked in the code before, because indeed we have a recent
bug fix addressing exactly this issue. Here's the commit message:
2003-09-15 19:33 tgl
* src/: backend/access/heap/heapam.c, backend/commands/async.c,backend/executor/execMain.c, include/access/heapam.h:
FixLISTEN/NOTIFYrace condition reported by Gavin Sherry. While areally general fix might be difficult, I believe the
onlycasewhere AtCommit_Notify could see an uncommitted tuple is where theother guy has just unlistened and not yet
committed. The bestsolution seems to be to just skip updating that tuple, on theassumption that the other guy does not
wantto hear about thenotification anyway. This is not perfect --- if the other guyrolls back his unlisten instead of
committing,then he reallyshould have gotten this notify. But to do that, we'd have to waitto see if he commits or not,
ormake UNLISTEN hold exclusive lockon pg_listener until commit. Either of these answers isdeadlock-prone, not to
mentionhorrible for interactiveperformance. Do it this way for now. (What happened to thatproject to do LISTEN/NOTIFY
inmemory with no table, anyway?)
This is in 7.4, but not 7.3.*.
You can duplicate the failure like so (in 7.3):
session one:listen foo;begin;unlisten foo;
session two:notify foo;-- hangs
session one:end;
-- session two now says
WARNING: AbortTransaction and not in in-progress state
ERROR: simple_heap_update: tuple concurrently updated
regards, tom lane
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> The only thing I still don't understand is the "not in in-progress
> state" thing.
At the point where it's trying to send a NOTIFY, it's partially out
of its transaction --- the state has become TRANS_COMMIT instead of
TRANS_INPROGRESS. Thus the warning. It's no big deal.
regards, tom lane
Dear Tom, I'll try my best. Also, I'll try to describe the situation more precisely, in case it may give you another idea. INSERT INTO p_items; -> p_items before: INSERT, UPDATE and/or DELETE other tuples in p_items -> p_items after: UPDATE p SET touch_time, toucher; ----> p after: INSERT INTO p_ny -------> p_ny after: NOTIFY p May it be that more "NOTIFY p"'s come from the same transaction (since I change more than one tuples in p_items)? Based on the error text, I assume this error comes only when two different transactions clash. I think it's very unlikely anyway since it happens ~100-1000 times a day and so far (~2 years of 7.3.3) this is the only occurence of this error. A final question: as far as you can remember, may this be an issue already fixed in later versions? Thanks again, HTH, and I'll report back if I encounter the error again. G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> Subject: Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT > "SZŰCS Gábor" <surrano@mailbox.hu> writes: > > Q1. So is this everything that can be said -- NOTIFY calls > > simple_heap_update that is concurrently updated by a different transaction? > > If that's what it is, then there's still a question: why? The notify > code has enough locking that this failure shouldn't happen. If you can > reproduce this I'd like to look into it. > > regards, tom lane >
Dear Tom, You did it again! The all-amazing-Tom-Lane-clearsight ;) I could reproduce this. I can imagine this practically as: (session 1) someone shutting down one of our Windows clients, while (session 2) another one did the INSERT at the very same moment. This thing caused session 2 to abort. The only thing I still don't understand is the "not in in-progress state" thing. After all, it's the very end of quite a long transaction. Thanks again. I'll lobby to upgrade our production server to 7.4 :) Yours, G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> Sent: Thursday, June 10, 2004 3:57 PM > session one: > listen foo; > begin; > unlisten foo; > session two: > notify foo; > -- hangs > session one: > end; > -- session two now says > WARNING: AbortTransaction and not in in-progress state > ERROR: simple_heap_update: tuple concurrently updated > > regards, tom lane >