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
|
