Обсуждение: Problem with ALTER TABLE - occasional "tuple concurrently updated"
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. -- Jon
On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wr= ote: > 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: =A0tuple 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, Nov 17, 2010 at 8:57 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson <jnelson+pgsql@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: =C2=A0tuple 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? =C2=A0Is it running concurrently wi= th > any other DML? =C2=A0What version of PostgreSQL is this? Yes, sometimes yes, and 8.4.5. > That does sound like a bug. That's what I thought! --=20 Jon
On Thu, Nov 18, 2010 at 10:28 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wr= ote: > On Wed, Nov 17, 2010 at 8:57 PM, Robert Haas <robertmhaas@gmail.com> wrot= e: >> On Tue, Nov 16, 2010 at 10:48 AM, Jon Nelson <jnelson+pgsql@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: =A0tuple concurrently updated >> >> The ALTER TABLE generates that error? =A0Is it running concurrently with >> any other DML? =A0What 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Nov 18, 2010 at 1:35 PM, Alvaro Herrera <alvherre@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? =A0They 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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 <alvherre@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian <bruce@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
Robert Haas wrote: > On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian <bruce@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > Robert Haas wrote: >> On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian <bruce@momjian.us> wrote: >> > Was this fixed? >> >> Not yet. =A0I 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. =A0;-) 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote: > On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > > Robert Haas wrote: > >> On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian <bruce@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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Mar 11, 2011 at 9:31 AM, Bruce Momjian <bruce@momjian.us> wrote: > Robert Haas wrote: >> On Thu, Mar 10, 2011 at 10:37 PM, Bruce Momjian <bruce@momjian.us> wrote: >> > Robert Haas wrote: >> >> On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian <bruce@momjian.us> wro= te: >> >> > 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" pa= rt >> > is probably accurate. ?;-) >> >> OK. =A0Do we want to back-patch this, and if so how far? =A0On 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. =A0Then again, >> ShareUpdateExclusiveLock doesn't interfere with routine queries, so >> maybe it's no big deal. =A0Given 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company