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:  AbortCurrentTransaction
  WARNING:  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


Wed, 9 Jun 2004 10:31:20 +0200
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
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

Wed, 09 Jun 2004 11:23:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: simple_heap_update: tuple concurrently updated -- during INSERT 
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:
> >   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
>


Wed, 9 Jun 2004 18:12:05 +0200
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
Re: 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

Wed, 09 Jun 2004 12:38:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: simple_heap_update: tuple concurrently updated -- during INSERT 
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?
>
> 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
>


Thu, 10 Jun 2004 11:40:59 +0200
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
Re: simple_heap_update: tuple concurrently updated -- during INSERT
"=?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

Thu, 10 Jun 2004 09:57:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: simple_heap_update: tuple concurrently updated -- during INSERT 
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
>


Thu, 10 Jun 2004 16:58:09 +0200
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu>
Re: simple_heap_update: tuple concurrently updated -- during INSERT
"=?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

Thu, 10 Jun 2004 11:03:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: simple_heap_update: tuple concurrently updated -- during INSERT 