alter_table_set_access_method AccessExclusiveLock

[jprudent]

Hello,

When I call alter_table_set_access_method there is some period of time where there is an
AccessExclusiveLock set on the parent table.

This lock can be set for a while because I'm working on very large 0.3T bytes partitions.

# SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    l.locktype,
    l.mode,
    l.granted
FROM
    pg_locks l
JOIN
    pg_class c ON l.relation = c.oid
JOIN
    pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relname = 'calls';
 schema_name | table_name | locktype |        mode         | granted 
-------------+------------+----------+---------------------+---------
 v1          | calls      | relation | AccessShareLock     | t
 v1          | calls      | relation | AccessExclusiveLock | t
 v1          | calls      | relation | AccessShareLock     | f

Even table metadata are not accessible: \dt+ v1.calls will just wait for the lock release.

From PG documentation:

Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

This is problematic in my usecase because PG cannot serve data to end user.

I forgot to mention that I use citus on single node, I'm mainly interested on columnar storage.

Here is a few suggestions, not sure what they imply:

• Add a parameter to choose less drastic lock mechanism that permits read. I may have read inconsistencies for a short period, but I would be fine with it I think.
• Reduce the scope of the lock (maybe it's mandatory for the partition swapping ?). My application can guarantee it won't write in old partition. If so that's a bug on my side, I can take the blame.

PS: I found this bug, somehow related #7177 (I think the deadlock comes from the same lock I describe here)

Thanks

[jprudent]

After investigating further ...

It looks like this is not Citus that explicitely set the lock.

This is pseudo SQL of the what does Citus when a table is converted

begin;

-- create the target partition with new storage without any index, constraints, ...
create table new_partition;
-- copy the data
insert into new_partition select * from partition;
-- 🔒 the lock is set here 
alter table parent detach partition
drop table partition;
alter table new_partition rename to partition;
-- ⌚ this can be very long:
alter table partition create index, constraints;
alter table parent attach partition;
-- 🔓 the lock is released here
commit;

The lock on the parent table is granted automatically by PG when we detach the partition (which is ok); and it's released once the tx ends.

If we could exclude the creation of index, constraints, ... from the time the lock is granted, that would greatly reduce the time my application freeze to a few seconds, just to do:

alter table parent detach partition;
drop table partition;
alter table new_partition rename to partition;
alter table parent attach partition;

Is it possible to create the index and constraints just after the copy of the data, (and just before the detach that creates the lock) ? That would considerably decrease the locking time.