simple_heap_update: tuple concurrently updated -- during INSERT

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <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

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

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <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,

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

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <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?

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <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: Fix
LISTEN/NOTIFY race condition reported by Gavin Sherry. While a
really general fix might be difficult, I believe the only case
where AtCommit_Notify could see an uncommitted tuple is where the
other guy has just unlistened and not yet committed. The best
solution seems to be to just skip updating that tuple, on the
assumption that the other guy does not want to hear about the
notification anyway. This is not perfect --- if the other guy
rolls back his unlisten instead of committing, then he really
should have gotten this notify. But to do that, we'd have to wait
to see if he commits or not, or make UNLISTEN hold exclusive lock
on pg_listener until commit. Either of these answers is
deadlock-prone, not to mention horrible for interactive
performance. Do it this way for now. (What happened to that
project to do LISTEN/NOTIFY in memory 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

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

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <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