Waiting for 9.1 – Reduced lock levels for ALTER TABLE

On 28th of July, Simon Riggs committed patch which:

Log Message:
-----------
Reduce LOCK levels OF CREATE TRIGGER AND SOME ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used FOR many altering DDL commands, allowing easier
future changes OF LOCK levels. Implementation OF initial analysis ON DDL
sub-commands, so that many LOCK levels are now at ShareUpdateExclusiveLock OR
ShareRowExclusiveLock, allowing certain DDL NOT TO block reads/writes.
FIRST OF NUMBER OF planned changes IN this area; additional docs required
WHEN FULL project complete.

First of all – this is just the beginning. Final goal is to make all (most?) ALTER TABLE statements less intrusive.

Previously, all alter tables locked all kind of access to table:

Session 1 Session 2
# begin;
# begin;
*# create trigger q after insert on table_name for each row execute procedure test();
*# select * from table_name ;
sleeps 10 minutes waits for 10 minutes

This was because previously all alter tables, and create trigger requires AccessExclusiveLock on the table:

*# CREATE TRIGGER q after INSERT ON TABLE_NAME FOR each ROW EXECUTE PROCEDURE test();
CREATE TRIGGER
 
*# SELECT * FROM pg_locks WHERE pid = pg_backend_pid();
   locktype    | DATABASE | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid  |        mode         | GRANTED 
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+---------------------+---------
 virtualxid    |   [NULL] |   [NULL] | [NULL] | [NULL] | 13/674866  |        [NULL] |  [NULL] | [NULL] |   [NULL] | 13/674866          | 9492 | ExclusiveLock       | t
<b> relation      |    48535 |    69187 | [NULL] | [NULL] | [NULL]     |        [NULL] |  [NULL] | [NULL] |   [NULL] | 13/674866          | 9492 | AccessExclusiveLock | t</b>
 transactionid |   [NULL] |   [NULL] | [NULL] | [NULL] | [NULL]     |        632704 |  [NULL] | [NULL] |   [NULL] | 13/674866          | 9492 | ExclusiveLock       | t
 relation      |    48535 |    10969 | [NULL] | [NULL] | [NULL]     |        [NULL] |  [NULL] | [NULL] |   [NULL] | 13/674866          | 9492 | AccessShareLock     | t
(4 ROWS)

and as we know (or can see in docs), this particular lock type conflicts with everything!

In 9.1 obtained lock will be a bit different:

*$ SELECT * FROM pg_locks WHERE pid = pg_backend_pid();
   locktype    | DATABASE | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction |  pid  |         mode          | GRANTED 
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+-------+-----------------------+---------
 transactionid |   [NULL] |   [NULL] | [NULL] | [NULL] | [NULL]     |           752 |  [NULL] | [NULL] |   [NULL] | 2/386              | 27199 | ExclusiveLock         | t
<b> relation      |    16398 |    16419 | [NULL] | [NULL] | [NULL]     |        [NULL] |  [NULL] | [NULL] |   [NULL] | 2/386              | 27199 | ShareRowExclusiveLock | t</b>
 virtualxid    |   [NULL] |   [NULL] | [NULL] | [NULL] | 2/386      |        [NULL] |  [NULL] | [NULL] |   [NULL] | 2/386              | 27199 | ExclusiveLock         | t
 relation      |    16398 |    10987 | [NULL] | [NULL] | [NULL]     |        [NULL] |  [NULL] | [NULL] |   [NULL] | 2/386              | 27199 | AccessShareLock       | t
(4 ROWS)
 
(depesz@[LOCAL]:5900) 15:12:55 [depesz] 
*$ SELECT 16419::regclass, 10987::regclass;
  regclass  | regclass 
------------+----------
 TABLE_NAME | pg_locks
(1 ROW)

This time, we see that CREATE TRIGGER got only “ShareRowExclusiveLock", which will permit select queries to the table in question, while the lock is in place.

It will still not allow modifications (UPDATE, INSERT, DELETE), and other DDL queries on the same table in the same time, but allowing read only queries is big win.

I haven't found list of all alter table variants that got their lock level modified so far, but it's not that important – this will all get changed, and new variants will be added. So far I know that adding trigger, and setting default value on column require ShareUpdateExclusiveLock instead of AccessExclusiveLock. Which commands will be next – we'll see.

2 thoughts on “Waiting for 9.1 – Reduced lock levels for ALTER TABLE”

  1. Hi,

    This sounds good. Will it have an effect on the following query ?
    ALTER TABLE mytable SET (autovacuum_enabled=false, toast.autovacuum_enabled=false);

    In pre 8.4, setting pg_autovacuum values didn’t lock the concerned table.

    AFAIK, this change was done to be more coherent, and to keep the attributes after dump/restore. It may have been more practical to implement it like the “COMMENT ON … IS ‘…’;” order:
    ATTRIBUTE … ON … IS ‘…’;
    Maybe it is too far from SQL standard, though.

    Anyway, if your patch is keeping table locks at bay, that’s fine 🙂

    Regards,

    JC

  2. @JC Praud:

    Lock after such query is “ShareUpdateExclusiveLock” Quite nice.

    Also – it’s not *my* patch. I’m merely reporting that something got changed.

Comments are closed.