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

<div dir="ltr"><br /><br />On Mon, Mar 30, 2015 at 7:41 PM, Jim Nasby <<a
href="mailto:Jim.Nasby@bluetreble.com">Jim.Nasby@bluetreble.com</a>>wrote:<br />><br />> On 3/27/15 2:23 PM,
Fabríziode Royes Mello wrote:<br />>><br />>> Hi all,<br />>><br />>> I'm tweaking some
autovacuumsettings in a table with high write usage<br />>> but with ALTER TABLE .. SET ( .. ) this task was
impossible,so I did a<br />>> catalog update  (pg_class) to change reloptions.<br />>><br />>> Maybe
it'sa stupid doubt, but why we need to get an AccessExclusiveLock<br />>> on relation to set reloptions if we
justtouch in pg_class tuples<br />>> (RowExclusiveLock) ?<br />><br />><br />> For a very long time
catalogaccess was not MVCC safe. I think that's been changed, so at this point it may be OK to relax the lock, at least
inthe case of autovac settings. There may well be other settings in there where it would not be safe.<br />><br
/><br/>Hummm.... There are a comment in AlterTableGetLockLevel:<br /><br /> 3017                 /*<br /> 3018        
        * Rel options are more complex than first appears. Options<br /> 3019                  * are set here for
tables,views and indexes; for historical<br /> 3020                  * reasons these can all be used with ALTER TABLE,
sowe can't<br /> 3021                  * decide between them using the basic grammar.<br /> 3022                  *<br
/> 3023                 * XXX Look in detail at each option to determine lock level,<br /> 3024                  * e.g.
cmd_lockmode= GetRelOptionsLockLevel((List *)<br /> 3025                  * cmd->def);<br /> 3026                
 */<br/> 3027             case AT_SetRelOptions:      /* Uses MVCC in getIndexes() and<br /> 3028                      
                  * getTables() */<br /> 3029             case AT_ResetRelOptions:    /* Uses MVCC in getIndexes()
and<br/> 3030                                          * getTables() */<br /> 3031                 cmd_lockmode =
AccessExclusiveLock;<br/> 3032                 break;<br /><br /><br />Maybe it's time to implement
"GetRelOptionsLockLevel"to relax the lock to autovac settings (AccessShareLock). To other settings we continue using
AccessExclusiveLock.<br/><br />There are some objection to implement in that way?<br /><br />Regards,<br /><br />--<br
/>Fabríziode Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/>>> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div>