Re: Deadlock between VACUUM and ALTER TABLE commands

From: Alexey Bashtanov <bashtanov(at)imap(dot)cc>
To: Kevin Burke <burke(at)shyp(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Deadlock between VACUUM and ALTER TABLE commands
Date: 2016-04-07 08:27:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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
> 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
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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Lars Arvidson 2016-04-07 09:20:30 Re: Problem after replication switchover
Previous Message John R Pierce 2016-04-07 06:46:43 Re: PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND