Re: Lock levels for ALTER TABLE

EnterpriseDB reports that our documentation states that ALTER TABLE
takes SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE lock modes. However,
their testing shows only ACCESS EXCLUSIVE locks. Is this accurate?
Have we changed how ALTER TABLE locks but didn't update our docs?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Jan 10, 2013 at 11:22 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> EnterpriseDB reports that our documentation states that ALTER TABLE
> takes SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE lock modes. However,
> their testing shows only ACCESS EXCLUSIVE locks. Is this accurate?
> Have we changed how ALTER TABLE locks but didn't update our docs?
>
By looking at AlterTableGetLevelLock:tablecmds.c, you are right. All the
ALTER TABLE operations now take an AccessExclusiveLock for all the commands
since 9.1 due to issues with lower level locks involving things like
catalog using SnapshotNow.
Docs should be updated in consequence.

The 9.1-prior locks can be reenabled by using the flag
REDUCED_ALTER_TABLE_LOCK_LEVELS.
Perhaps this could also be specified in the docs.
--
Michael Paquier
http://michael.otacoo.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> EnterpriseDB reports that our documentation states that ALTER TABLE
> takes SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

Where do they see that? We certainly reverted all of the documentation
that Simon changed in the original commit for that failed feature.
Something might have slipped through the cracks though.

regards, tom lane

On Wed, Jan 9, 2013 at 10:27:54PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > EnterpriseDB reports that our documentation states that ALTER TABLE
> > takes SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE lock modes.
>
> Where do they see that? We certainly reverted all of the documentation
> that Simon changed in the original commit for that failed feature.
> Something might have slipped through the cracks though.

In mvcc.sgml, I see:

<varlistentry>
<term>
<literal>SHARE UPDATE EXCLUSIVE</literal>
</term>
<listitem>
<para>
Conflicts with the <literal>SHARE UPDATE EXCLUSIVE</literal>,
<literal>SHARE</literal>, <literal>SHARE ROW
EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
<literal>ACCESS EXCLUSIVE</literal> lock modes.
This mode protects a table against
concurrent schema changes and <command>VACUUM</> runs.
</para>

<para>
Acquired by <command>VACUUM</command> (without <option>FULL</option>),
<command>ANALYZE</>, <command>CREATE INDEX CONCURRENTLY</>, and
--> some forms of <command>ALTER TABLE</command>.
</para>
</listitem>
</varlistentry>

and

<varlistentry>
<term>
<literal>ACCESS EXCLUSIVE</literal>
</term>
<listitem>
<para>
Conflicts with locks of all modes (<literal>ACCESS
SHARE</literal>, <literal>ROW SHARE</literal>, <literal>ROW
EXCLUSIVE</literal>, <literal>SHARE UPDATE
EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE
ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
<literal>ACCESS EXCLUSIVE</literal>).
This mode guarantees that the
holder is the only transaction accessing the table in any way.
</para>

<para>
--> Acquired by the <command>ALTER TABLE</>, <command>DROP TABLE</>,
<command>TRUNCATE</command>, <command>REINDEX</command>,
<command>CLUSTER</command>, and <command>VACUUM FULL</command>
commands.
This is also the default lock mode for <command>LOCK TABLE</command>
statements that do not specify a mode explicitly.
</para>
</listitem>
</varlistentry>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Wed, Jan 9, 2013 at 10:27:54PM -0500, Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> EnterpriseDB reports that our documentation states that ALTER TABLE
>>> takes SHARE UPDATE EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

>> Where do they see that? We certainly reverted all of the documentation
>> that Simon changed in the original commit for that failed feature.
>> Something might have slipped through the cracks though.

> In mvcc.sgml, I see:
> --> some forms of <command>ALTER TABLE</command>.

A bit of "git blame" later, the culprit is commit
7212c77d0cabcc468fec0b9cd7f2413b64b77050, which I apparently didn't
recognize as part of what needed to be reverted in
a195e3c34f1eeb6a607c342121edf48e49067ea9. Will fix.

regards, tom lane

I wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> On Wed, Jan 9, 2013 at 10:27:54PM -0500, Tom Lane wrote:
>>> Something might have slipped through the cracks though.

>> In mvcc.sgml, I see:
>> --> some forms of <command>ALTER TABLE</command>.

> A bit of "git blame" later, the culprit is commit
> 7212c77d0cabcc468fec0b9cd7f2413b64b77050, which I apparently didn't
> recognize as part of what needed to be reverted in
> a195e3c34f1eeb6a607c342121edf48e49067ea9. Will fix.

Actually, I'm not sure the statement is wrong. It's true that the
*target* table of ALTER TABLE is always ex-locked, but there are some
forms such as ALTER ADD INHERIT that take ShareUpdateExclusiveLock on a
parent relation. I'm inclined to leave it alone.

Another issue is that use of ShareUpdateExclusiveLock seems to have
spread into a bunch of newfangled statements that are not mentioned at
all in mvcc.sgml --- look in CommentObject,
ExecAlterExtensionContentsStmt, ExecSecLabelStmt for instance. I
suspect this list is horridly out of date in other ways; I don't think
it even pretends to cover lock usage for non-table objects, for
instance. Perhaps we ought to insert some weasel wording to stop making
it look like the list is intended to be complete, because certainly
nobody is trying very hard to keep it so.

regards, tom lane