Re: ALTER TABLE SET STATISTICS requires AccessExclusiveLock

2010/3/3 Bruce Momjian <bruce(at)momjian(dot)us>:
> Peter Eisentraut wrote:
>> On m?n, 2010-02-22 at 10:32 -0500, Bruce Momjian wrote:
>> > Simon Riggs wrote:
>> > > On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote:
>> > > > Simon Riggs wrote:
>> > > > >
>> > > > > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote:
>> > > > > > Tom Lane wrote:
>> > > > > > > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> > > > > > > > Is there a good reason for $subject, other than that the code is entangled
>> > > > > > > > with other ALTER TABLE code?
>> > > > > > >
>> > > > > > > I think it could be lower, but it would take nontrivial restructuring of
>> > > > > > > the ALTER TABLE support.  In particular, consider what happens when you
>> > > > > > > have a list of subcommands that don't all require the same lock level.
>> > > > > > > I think you'd need to scan the list and find the highest required lock
>> > > > > > > level before starting ...
>> > > > > >
>> > > > > > IIRC there was a patch from Simon to address this issue, but it had some
>> > > > > > holes which he didn't have time to close, so it sank.  Maybe this can be
>> > > > > > resurrected and fixed.
>> > > > >
>> > > > > I was intending to finish that patch in this release cycle.
>> > > >
>> > > > Since you're busy with Hot Standby, any chance you could pass it on?
>> > >
>> > > If you'd like. It's mostly finished, just one last thing to finish:
>> > > atomic changes to pg_class via an already agreed API.
>> >
>> > 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 Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support