| The query bulldozer's purpose is to clear out transactions that are holding onto locks that the GC operation needs, but in its current state it is not guaranteed to make progress. Firstly, if a transaction is idle pg_cancel_backend doesn't result in aborting the transaction, meaning none of the locks are freed. code
=> BEGIN TRANSACTION;
BEGIN
=*> select * from reports; pg_cancel_backend called here from another terminal =*> select * from certnames;
=*> COMMIT;
COMMIT
code using pg_terminate_backend solves that by immediately causing a ROLLBACK and terminating the connection
=> BEGIN TRANSACTION; |
BEGIN |
I puppetdb=*> select * from reports; |
|
terminate called here -- pg_locks reports locks are immediately freed |
|
=*> select * from certnames; |
FATAL: terminating connection due to administrator command |
server closed the connection unexpectedly |
This probably means the server terminated abnormally |
before or while processing the request. |
The connection to the server was lost. Attempting reset: Succeeded. |
=> COMMIT; |
WARNING: there is no transaction in progress |
COMMIT
|
Secondly, the bulldozer has a thread sleep of 1 second, and the Postgres deadlock detector also waits for 1 second, so we only get one chance to free a deadlocked drop table command before it is terminated by postgres. I lazily acquires multiple locks in the GC routine, so one attempt may not be enough. We should sleep for a shorter interval to give ourselves multiple chances to "free" the deadlocked GC query. In the latest version of PE we could also consider increasing the deadlock detectors timeout. |
