Postgres: deadlock when ATTACH PARTITION to a table with a lots of INSERTs

Question

I have a partitioned table user_stay which has many INSERTs and UPDATEs.

db=> \d user_stay;
 Partitioned table "public.user_stay"
 Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------------------------------------
 id | bigint | | not null | nextval('user_stay_id_seq'::regclass)
 last_update_date | date | | not null | CURRENT_DATE
 custom_place_id | bigint | | |
Foreign-key constraints:
 "user_stay_custom_place_id_fkey" FOREIGN KEY (custom_place_id) REFERENCES user_custom_place(id) ON DELETE CASCADE

The partition is by last_update_date. When I try to attach the new partition table for the future date, the error occurs.

For example, today is 8/30, and I try to create the table for 9/2:

CREATE TABLE user_stay_partition_2023_09_02 
 (LIKE user_stay INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE user_stay_partition_2023_09_02 ADD CONSTRAINT "2023_09_02"
 CHECK ( last_update_date >= DATE '2023_09_02' AND
 last_update_date < DATE '2023_09_03' );
ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
 FOR VALUES FROM ('2023_09_02') to ('2023_09_03');

The error is:

[7396]:ERROR: deadlock detected at character 13
[7396]:DETAIL: Process 7396 waits for RowExclusiveLock on relation 594728 of database 18604; blocked by process 31245.
Process 31245 waits for ShareRowExclusiveLock on relation 594672 of database 18604; blocked by process 7396.
Process 7396: INSERT INTO user_stay (last_update_date, ...) VALUES ('2023-08-30'::date, ...) ON CONFLICT DO NOTHING RETURNING user_stay.id
Process 31245: ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
FOR VALUES FROM ('2023_09_02') to ('2023_09_03');

Update 1

I think the reason is, the following two SQLs:

1. INSERT INTO user_stay
2. ALTER TABLE user_stay ATTACH PARTITION

Both require the lock of user_stay & user_custom_place. If SQL 1 gets the lock of user_stay first, and SQL 2 gets the lock of user_custom_place first, then it is the deadlock and both SQLs will not be completed.

Update 2

Fix the deadlock by manually locking the table with the same order, so the deadlock is gone:

BEGIN;
LOCK TABLE user_custom_place IN SHARE ROW EXCLUSIVE MODE;
ALTER TABLE user_stay ATTACH PARTITION user_stay_partition_2023_09_02
 FOR VALUES FROM ('2023_09_02') to ('2023_09_03');
COMMIT;

• jjanes: What are relations 594728 and 594672?
• iForests: 594728 is user_stay, and 594672 is another table user_custom_place. There is a foreign key point to user_custom_place from user_stay (I have updated the original post.)
• iForests: @jjanes thank you for your hint, I think I know the issue now. I've update the original post with "Update 1", can you help me fix it? Many thanks.

Answer 1

As far as I can tell, if those queries (INSERT and ATTACH) were run in isolation the tables are locked in the correct order so that one would just block the other, but there would not be a deadlock. Presumably some earlier statement in the same transaction as one of those acquired a lock in some other order, which leads to deadlock.

You didn't show a BEGIN in your create-and-attach sequence, so I assume each one of those was run in different auto-committing transactions, and it is the INSERT transaction which is therefore the culprit. But I don't think there is a very good way to investigate this without reviewing the code that generates the INSERT, and what it might do earlier in the transaction.

• iForests: Thank for your help! Tried manually locking the tables with the same order and it worked.