Re: Doubt about AccessExclusiveLock in ALTER TABLE .. SET ( .. );

On Wed, Apr 1, 2015 at 1:45 AM, Noah Misch <noah@leadboat.com> wrote:
>
> On Tue, Mar 31, 2015 at 01:17:03PM -0400, Robert Haas wrote:
> > On Tue, Mar 31, 2015 at 9:11 AM, Fabrízio de Royes Mello
> > <fabriziomello@gmail.com> wrote:
> > > Attached a very WIP patch to reduce lock level when setting autovacuum
> > > reloptions in "ALTER TABLE .. SET ( .. )" statement.
> >
> > I think the first thing we need to here is analyze all of the options
> > and determine what the appropriate lock level is for each, and why.
>
> Agreed.  Fabrízio, see this message for the discussion that led to the code
> comment you found (search for "relopt_gen"):
>
>   http://www.postgresql.org/message-id/20140321034556.GA3927180@tornado.leadboat.com

Ok guys. The attached patch refactor the reloptions adding a new field "lockmode" in "relopt_gen" struct and a new method to determine the required lock level from an option list.

We need determine the appropriate lock level for each reloption:

- boolRelopts:
  * autovacuum_enabled  (AccessShareLock)
  * user_catalog_table  (AccessExclusiveLock)
  * fastupdate  (AccessExclusiveLock)
  * security_barrier  (AccessExclusiveLock)

- intRelOpts:
  * fillfactor (heap)  (AccessExclusiveLock)
  * fillfactor (btree)  (AccessExclusiveLock)
  * fillfactor (gist)  (AccessExclusiveLock)
  * fillfactor (spgist)  (AccessExclusiveLock)
  * autovacuum_vacuum_threshold  (AccessShareLock)
  * autovacuum_analyze_threshold  (AccessShareLock)
  * autovacuum_vacuum_cost_delay  (AccessShareLock)
  * autovacuum_vacuum_cost_limit  (AccessShareLock)
  * autovacuum_freeze_min_age  (AccessShareLock)
  * autovacuum_multixact_freeze_min_age  (AccessShareLock)
  * autovacuum_freeze_max_age  (AccessShareLock)
  * autovacuum_multixact_freeze_max_age  (AccessShareLock)
  * autovacuum_freeze_table_age  (AccessShareLock)
  * autovacuum_multixact_freeze_table_age  (AccessShareLock)
  * log_autovacuum_min_duration  (AccessShareLock)
  * pages_per_range  (AccessExclusiveLock)
  * gin_pending_list_limit  (AccessExclusiveLock)

- realRelOpts:
  * autovacuum_vacuum_scale_factor  (AccessShareLock)
  * autovacuum_analyze_scale_factor  (AccessShareLock)
  * seq_page_cost  (AccessExclusiveLock)
  * random_page_cost  (AccessExclusiveLock)
  * n_distinct  (AccessExclusiveLock)
  * n_distinct_inherited  (AccessExclusiveLock)

- stringRelOpts:
  * buffering  (AccessExclusiveLock)
  * check_option  (AccessExclusiveLock)

In the above list I just change lock level from AccessExclusiveLock to AccessShareLock to all "autovacuum" related reloptions because it was the motivation of this patch.

I need some help to define the others.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello