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
acquireAccessExclusiveLock
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
<http://www.postgresql.org/docs/9.4/static/explicit-locking.html#TABLE-LOCK-COMPATIBILITY>
to determine what locks are conflicting one with another Regards, Alexey
Bashtanov