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> 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
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: 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
On Thu, Nov 18, 2010 at 10:28 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
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: 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
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? 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
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. 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
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> 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