e2pg: pg advisory locks may be held too long

[ryandotsmith]

I've noticed on Render that when a deploy or restart occurs, the "old" process hangs around in a degraded state. In some cases this means that there are locks being held by a process that isn't preforming any work. This means that we are deadlocked.

I have a support ticket open with Render to see why the processes are hanging around, but in any case, we should code E2PG to be resilient to this possibility.

One quick fix I found was to set a idle timeout on PG:

set idle_in_transaction_session_timeout=100ms

After setting this config, I haven't seen deadlocking. Although this solution is a little unsettling. I'd like to find the root cause. Why are the transactions in writeIndex not being committed or rolled back. What is blocking the transaction from completing?

[ryandotsmith]

I should add that setting this value will only apply to the current connection. This may help ensure it is applied to all new connections:

alter user XXX set idle_in_transaction_session_timeout='10s';