CODEWITHSUNDEEP
postgresqlpartitioningpostgresql-10
Whelchel
Whelchel

Reputation: 313

Why do partitioned tables in PostgreSQL take locks on other tables when being dropped?

This is using PostgreSQL 10.4

I have a partitioned table setup as:

                            Table "public.foo_partitioned"
   Column        |            Type             | Collation | Nullable |    Default
---------------------+-----------------------------+-----------+----------+----------------
 bar_id             | integer                     |           | not null |
 ...
Partition key: LIST (bar_id)
Number of partitions: 10 (Use \d+ to list them.)

There is a foreign key on bar_id that references the table bars.

When I try to drop a partition, foo_partitioned_1, and if an outstanding idle transaction query exists on bars, the drop blocks. This seems very strange to me, that a drop on this partition wouldn't be allowed with a query against a separate table. I tried with detaching the partition first which also doesn't work. I can query the lock tables and get information like:

  pid  |   usename   | blocked_by |       blocked_query
-------+-------------+------------+----------------------------
 59897 | my_user | {59888}    | DROP TABLE foo_partitioned_1

When I run the following

select pgl.*, relname from pg_locks pgl join pg_class pgt on pgl.relation=pgt.oid  where pid=59897;

I get:

 locktype | database | relation |...| virtualtransaction |  pid  |        mode         | granted | fastpath |                 relname
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------+-----------------------------------------
 relation |    16401 |     2620 |...| 7/758              | 60266 | RowExclusiveLock    | t       | t        | pg_trigger
 relation |    16401 |     2608 |...| 7/758              | 60266 | RowExclusiveLock    | t       | t        | pg_depend
 relation |    16401 |   940755 |...| 7/758              | 60266 | AccessExclusiveLock | t       | f        | foo_partitioned
 relation |    16401 |   941051 |...| 7/758              | 60266 | AccessExclusiveLock | t       | f        | uq_foo_partitioned_1
 relation |    16401 |   742405 |...| 7/758              | 60266 | AccessExclusiveLock | f       | f        | bars
 relation |    16401 |     2702 |...| 7/758              | 60266 | AccessShareLock     | t       | f        | pg_trigger_oid_index
 relation |    16401 |   941047 |...| 7/758              | 60266 | AccessShareLock     | t       | f        | foo_partitioned_1
 relation |    16401 |   941047 |...| 7/758              | 60266 | AccessExclusiveLock | t       | f        | foo_partitioned_1

The only lock not granted to the query is the one on bars. However, it's completely unclear to me why this lock is even required.

Thanks for all thoughts!

Upvotes: 2

Views: 2918

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246533

That has nothing to do with partitioning.

Foreign keys are implemented as triggers in PostgreSQL, and when you drop the partition, the trigger on bars that implements (one half of) the foreign key has to be dropped.

Now dropping a trigger requires an ACCESS EXCLUSIVE lock on the table, and that blocks until all concurrent transactions on the table have finished.

Upvotes: 2

Related Questions