Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock - Mailing list pgsql-hackers

On Wed, Jul 7, 2010 at 9:04 PM, Cédric Villemain
<cedric.villemain.debian@gmail.com> wrote:
>>> > I assume this did not get done for 9.0.  Do we want a TODO item?
>>>
>>> Yes.
>>
>> Added:
>>
>>        Reduce locking required for ALTER commands
>
> I just faced production issue where it is impossible to alter table to
> adjust autovacuum settings in a pg8.4. (5K tps, 260M rows table, lock
> too much)
>
> Can we add some mechanism to prevent that situation also in the TODO item ?
>
> (alternative is actualy to alter other tables and adjust the
> postgresql.conf for biggest tables, but not an ideal solution anyway)
>
>>
>>            * http://archives.postgresql.org/pgsql-hackers/2009-08/msg00533.php
>>            * http://archives.postgresql.org/pgsql-hackers/2009-10/msg01083.php
>>            * http://archives.postgresql.org/pgsql-hackers/2010-01/msg02349.php

Bruce, that last link is about something else completely.  Here are
some better ones:

http://archives.postgresql.org/pgsql-hackers/2008-10/msg01248.php
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00242.php

All,

Rereading the thread, I'm a bit confused by why we're proposing to use
a SHARE lock; it seems to me that a self-conflicting lock type would
simplify things.  There's a bunch of discussion on the thread about
how to handle pg_class updates atomically, but doesn't using a
self-conflicting lock type eliminate that problem?

It strikes me that for the following operations, which don't affect
queries at all, we could use a SHARE UPDATE EXCLUSIVE, which is likely
superior to SHARE for this purpose because it wouldn't lock out
concurrent DML write operations:

ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )

(Of the above list, arguably SET STORAGE and [RE]SET (fillfactor) do
in fact affect DML writes, but it seems like changing them on the fly
should still be safe.)

The remaining commands which Simon proposed to downgrade to share-locks were:

ALTER [ COLUMN ] column SET DEFAULT expression
CREATE RULE (only non-ON SELECT rules)
CREATE TRIGGER
ALTER [ COLUMN ] column SET NOT NULL (but not DROP NOT NULL)
ADD table_constraint (but not DROP CONSTRAINT)
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name

Setting a column default, creating a non-select RULE, and
creating/disabling a trigger shouldn't affect SELECT statements, so as
long as we lock out all updates we should be OK.  For these it seems
we could use SHARE ROW EXCLUSIVE, which will conflict with any other
DML command and with any data change, but not with SELECTs.

I am somewhat fuzzy on what the correct locking is for SET NOT NULL
and ADD table_constraint.  I believe that the idea here is that a
query plan might rely on the existence of a constraint for
correctness, so we must lock out all queries when dropping one; but a
query plan can't rely on the absence of a constraint for correctness
(since the constraint could be true anyway), so it's safe to allow one
to be added even when there are queries in flight.  If that's correct
then it seems like we could use SHARE ROW EXCLUSIVE for these command
types as well.  However, these two particular commands have another
distinguishing characteristic also: they might run for a while, so it
would be useful to be able to do more than one at once.  So maybe it's
worth thinking a little harder about how to weaken those two in
particular to some non-self-conflicting lock type.  Then again, even
SHARE ROW EXCLUSIVE is a big improvement over ACCESS EXCLUSIVE, so
maybe that would be enough for a first go at the problem.

Thoughts?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company