CODEWITHSUNDEEP
postgresql
Maksim Ekimovskii
Maksim Ekimovskii

Reputation: 13

PostgreSQL ALTER TABLE explicit locking doesn't work as expected

I'm trying to perform ALTER TABLE on a huge table without full access locking. According to ALTER TABLE documentation:

An ACCESS EXCLUSIVE lock is held unless explicitly noted

Hence I do the following:

BEGIN;
LOCK TABLE MyTable IN SHARE MODE;
ALTER TABLE MyTable ALTER COLUMN size SET DATA TYPE BIGINT;
COMMIT;

I expect that it gives me a possibility to perform SELECT queries during the upgrade. But in fact it doesn't. Looking at pg_locks during the transaction I've found that:

SELECT c.relname, l.mode, l.granted, l.pid
  FROM pg_locks as l JOIN pg_class as c on c.oid = l.relation 
  WHERE relname='MyTable' AND granted IS TRUE;

 relname  |        mode         | granted | pid  
----------+---------------------+---------+------
 MyTable  | ShareLock           | t       | 2277
 MyTable  | AccessExclusiveLock | t       | 2277

So AccessExclusiveLock unexpectedly was taken as well and it explains why my SELECTs are hanging until the end of the transaction

I use PostgreSQL 9.4

Upvotes: 1

Views: 395

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61506

You seem to misinterpret the unless explicitly noted bit.

It means that, given that there are different actions grouped under ALTER TABLE, for some of them it's possible that a lock weaker than ACCESS EXCLUSIVE might be sufficient, and when it's in the case, it's explicity noted in the documentation.

For instance (from https://www.postgresql.org/docs/9.5/static/sql-altertable.html ):

SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.

...

Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE lock.

...

Validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint

It doesn't mean that the actions that require an ACCESS EXCLUSIVE lock (such as changing the type of a column) could be influenced by a previous explicit weaker lock grabbed on the table in the same transaction. They are going to need an ACCESS EXCLUSIVE lock no matter what.

Upvotes: 2

Related Questions