Re: [HACKERS] 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 [EMAIL PROTECTED] Subject: Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT =?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 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: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] To: SZCS Gbor [EMAIL PROTECTED] Cc: PGH [EMAIL PROTECTED] 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=?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
