Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Fri, Mar 11, 2011 at 9:31 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. ?I can probably fix it, if nobody else wants to do it. Well, it has languished for five months, so the nobody else wants part is probably accurate. ?;-) OK. Do we want to back-patch this, and if so how far? On the one hand, the symptom that OP is experiencing clearly sucks for him, but on the other hand upgrading the strength of a lock in releases that have been out in the field for a long time seems like an open invitation to have the villagers show up with pitchforks. Then again, ShareUpdateExclusiveLock doesn't interfere with routine queries, so maybe it's no big deal. Given that we have only one report, I'm inclined to just fix it in the master branch, but I could easily be talked into the other approach if someone wants to make an argument for it. Agree on master-only. Done. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. I can probably fix it, if nobody else wants to do it. Well, it has languished for five months, so the nobody else wants part is probably accurate. ;-) OK. Do we want to back-patch this, and if so how far? On the one hand, the symptom that OP is experiencing clearly sucks for him, but on the other hand upgrading the strength of a lock in releases that have been out in the field for a long time seems like an open invitation to have the villagers show up with pitchforks. Then again, ShareUpdateExclusiveLock doesn't interfere with routine queries, so maybe it's no big deal. Given that we have only one report, I'm inclined to just fix it in the master branch, but I could easily be talked into the other approach if someone wants to make an argument for it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Robert Haas wrote: On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. ?I can probably fix it, if nobody else wants to do it. Well, it has languished for five months, so the nobody else wants part is probably accurate. ?;-) OK. Do we want to back-patch this, and if so how far? On the one hand, the symptom that OP is experiencing clearly sucks for him, but on the other hand upgrading the strength of a lock in releases that have been out in the field for a long time seems like an open invitation to have the villagers show up with pitchforks. Then again, ShareUpdateExclusiveLock doesn't interfere with routine queries, so maybe it's no big deal. Given that we have only one report, I'm inclined to just fix it in the master branch, but I could easily be talked into the other approach if someone wants to make an argument for it. Agree on master-only. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Was this fixed? --- Alvaro Herrera wrote: Excerpts from Alvaro Herrera's message of jue nov 18 15:31:16 -0300 2010: Excerpts from Robert Haas's message of jue nov 18 15:11:37 -0300 2010: In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. What if two of these run at the same time, and the parent doesn't have children when they start? They would both try to set relhassubclass, no? Yep, duplicated the issue that way. -- ??lvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. I can probably fix it, if nobody else wants to do it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Robert Haas wrote: On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. I can probably fix it, if nobody else wants to do it. Well, it has languished for five months, so the nobody else wants part is probably accurate. ;-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Thu, Nov 18, 2010 at 1:35 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alvaro Herrera's message of jue nov 18 15:31:16 -0300 2010: Excerpts from Robert Haas's message of jue nov 18 15:11:37 -0300 2010: In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. What if two of these run at the same time, and the parent doesn't have children when they start? They would both try to set relhassubclass, no? Yep, duplicated the issue that way. I think ATExecAddInherit() and MergeAttributes() need to take ShareUpdateExclusiveLock instead of AccessShareLock to prevent this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Wed, Nov 17, 2010 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson jnelson+pg...@jamponi.net wrote: I have a process which runs in parallel creating tables which, as the /final/ step in the import, gets SQL much like the following applied: ALTER TABLE foo INHERIT bar; Periodically, I get this error: tuple concurrently updated Of course, I googled for the error message and see a bunch of issues involving ANALYZE and even DROP function. Is this the same root cause? Is there a fix? Is there a lock I could take or some other approach that would prevent the error? I thought all ALTER TABLE statements took a big fat lock to prevent such an issue. The ALTER TABLE generates that error? Is it running concurrently with any other DML? What version of PostgreSQL is this? Yes, sometimes yes, and 8.4.5. That does sound like a bug. That's what I thought! -- Jon -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Thu, Nov 18, 2010 at 10:28 AM, Jon Nelson jnelson+pg...@jamponi.net wrote: On Wed, Nov 17, 2010 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson jnelson+pg...@jamponi.net wrote: I have a process which runs in parallel creating tables which, as the /final/ step in the import, gets SQL much like the following applied: ALTER TABLE foo INHERIT bar; Periodically, I get this error: tuple concurrently updated The ALTER TABLE generates that error? Is it running concurrently with any other DML? What version of PostgreSQL is this? Yes, sometimes yes, and 8.4.5. Any chance you can isolate a reproducible test case? Maybe a series of steps to be run in two psql sessions? Or any idea what DDL might be running against the parent at the same time? In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Excerpts from Robert Haas's message of jue nov 18 15:11:37 -0300 2010: In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. What if two of these run at the same time, and the parent doesn't have children when they start? They would both try to set relhassubclass, no? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Excerpts from Alvaro Herrera's message of jue nov 18 15:31:16 -0300 2010: Excerpts from Robert Haas's message of jue nov 18 15:11:37 -0300 2010: In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. What if two of these run at the same time, and the parent doesn't have children when they start? They would both try to set relhassubclass, no? Yep, duplicated the issue that way. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson jnelson+pg...@jamponi.net wrote: I have a process which runs in parallel creating tables which, as the /final/ step in the import, gets SQL much like the following applied: ALTER TABLE foo INHERIT bar; Periodically, I get this error: tuple concurrently updated Of course, I googled for the error message and see a bunch of issues involving ANALYZE and even DROP function. Is this the same root cause? Is there a fix? Is there a lock I could take or some other approach that would prevent the error? I thought all ALTER TABLE statements took a big fat lock to prevent such an issue. The ALTER TABLE generates that error? Is it running concurrently with any other DML? What version of PostgreSQL is this? That does sound like a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs