Re: Deadlock between VACUUM and ALTER TABLE commands

Hello Kevin,

On 06/04/16 23:22, Kevin Burke wrote:

Why mess around with DISABLE/ENABLE TRIGGER instead of TRUNCATE? We observed that TRUNCATE took about 200ms, but this method takes about 13ms. Over a our test suite TRUNCATE is going to more or less double the length of the suite.

We could resolve some kind of foreign key dependency tree, issue the DELETE FROM's in the right order, and skip DISABLE TRIGGER etc., but haven't invested the time to do this or figured out how to maintain it/update it as we add new tables.

I'd suggest one more method: SET CONSTRAINTS ALL DEFERRED and DELETE FROM all tables in arbitrary order (will require making constraints deferrable)

Specifically we kick off the ALTER TABLE ... DELETE, a second later (our deadlock_timeout) a log message appears that the ALTER TABLE is waiting for an AccessExclusiveLock held by process 16936... which is later revealed to be an autovacuum process of the "events" table.

this only indicates that 16936 does not allow ALTER TABLE to acquire AccessExclusiveLock
this does not necessarily mean that 16936 has acquired AccessExclusiveLock, it means that 16936 has acquired some lock conflicting with AccessExclusiveLock you can use this table to determine what locks are conflicting one with another Regards, Alexey Bashtanov