it seems that if postgresql needs to acquire several locks, it does that in OID order. which causes deadlock if pgcompactor's index gets OID less than existing one's.
this is what happened in real life:
… [20583]: […] from=…(…),user=…,db=… STATEMENT:
SELECT * FROM … FOR UPDATE
… [20583]: […] from=…(…),user=…,db=… ERROR: deadlock detected
… [20583]: […] from=…(…),user=…,db=… DETAIL:
Process 20583 waits for AccessShareLock on relation 3879503128 of database 24314; blocked by process 12662.
Process 12662 waits for AccessExclusiveLock on relation 43045091 of database 24314; blocked by process 20583.
Process 12662: ALTER INDEX public.pgcompact_index_12644 RENAME TO …;
the only way I've managed to reproduce that is using pg_resetwal to manually set cluster's OID counter.
create table t (id serial primary key, date timestamp(0) without time zone);
it seems that if postgresql needs to acquire several locks, it does that in OID order. which causes deadlock if pgcompactor's index gets OID less than existing one's.
this is what happened in real life:
the only way I've managed to reproduce that is using pg_resetwal to manually set cluster's OID counter.
stop postgresql.
pg_resetwal -o 4294967294 [data_directory]
. start postgresql[1] acquires AccessExclusiveLock on t_idx
[2] acquires AccessShareLock on pgcompact_index and waits for AccessShareLock on t_idx
waits for AccessExclusiveLock on pgcompact_index. deadlock
I think it would be safer to lock entire table when swapping indexes