Advisory lock never cleared, and connection not closed on interrupt leads to wedged locks

[fake-name]

I'm experimenting with pgcompacttable. However, it appears trivial to wedge the table processing state machine:

durr@postgres-server ~> sudo -H -u postgres pgcompacttable --verbose --dbname webarchive -t web_pages
[Tue Oct 27 02:02:25 2020] (webarchive) Connecting to database
[Tue Oct 27 02:02:25 2020] (webarchive) Postgres backend pid: 8880
[Tue Oct 27 02:02:25 2020] (webarchive) Handling tables. Attempt 1
[Tue Oct 27 02:02:25 2020] (webarchive:public.web_pages) Start handling table public.web_pages
^C⏎                                                  
durr@postgres-server ~> sudo -H -u postgres pgcompacttable --verbose info --dbname webarchive -t web_pages
[Tue Oct 27 02:08:55 2020] (webarchive) Connecting to database
[Tue Oct 27 02:08:55 2020] (webarchive) Postgres backend pid: 23824
[Tue Oct 27 02:08:55 2020] (webarchive) Handling tables. Attempt 1
[Tue Oct 27 02:08:55 2020] (webarchive:public.web_pages) Start handling table public.web_pages
[Tue Oct 27 02:08:55 2020] (webarchive:public.web_pages) Skipping processing: another instance is working with table public.web_pages
[Tue Oct 27 02:08:55 2020] (webarchive:public.web_pages) Finish handling table public.web_pages
[Tue Oct 27 02:08:55 2020] (webarchive) Processing complete.
[Tue Oct 27 02:08:55 2020] (webarchive) Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Tue Oct 27 02:08:55 2020] (webarchive) Disconnecting from database
[Tue Oct 27 02:08:55 2020] Processing complete: 1 retries to process has been done
[Tue Oct 27 02:08:55 2020] Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.

So basically, if you run pgcompacttable, interrupt it with Ctrl+C, and then re-run it, it leaves the advisory lock active. This basically reveals two things:

• I don't have connection keepalives (so the database isn't noticing the connection is gone), and the lock is persisting.
• pgcompacttable apparently does no cleanup of the locks or graceful closing of the connection on Ctrl + C.

Once I figured this out, it was straightforward enough to go and release the lock:

webarchive=# select * from pg_locks WHERE locktype = 'advisory';
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid |   objid   | objsubid | virtualtransaction | pid  |     mode      | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-----------+----------+--------------------+------+---------------+---------+----------
 advisory |    18623 |          |      |       |            |               |    1259 | 705758310 |        2 | 19/1370012         | 8880 | ExclusiveLock | t       | f
(1 row)

webarchive=# SELECT pg_terminate_backend(8880);
 pg_terminate_backend
----------------------
 t
(1 row)

webarchive=# select * from pg_locks WHERE locktype = 'advisory';
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------
(0 rows)

Though doing the above meant I had to spend 20 minutes trying to figure out where the lock was, digging about in the source to figure out how it was being acquired, and reading up on what the deuce a advisory lock even was.

I'm not sure what the best solution here would be. Personally, I like to have the first Ctrl+C start a graceful shutdown, and any further Ctrl+Cs raise the exception harder, but that's a opinion thing.

In any event, this seems to be a pretty annoying footgun that should probably be documented. Or there should be a way to override/clear the advisory lock in the CLI.