Consider adding transaction keep-alives when using psql

[skuruppu]

I came across some unexpected behavior while using PGAdapter with psql. Here's the transaction example.

Transaction T1:

1. BEGIN;
2.
3. /*@LOCK_SCANNED_RANGES=exclusive*/SELECT id, name FROM singers WHERE singerid = 2;

id | name 
---+-----------
 2 | Catalina

4.

5. UPDATE singers SET name = 'Cat' WHERE id = 2;
--> update succeeded

6. COMMIT;

Transaction T2:

1.
2. BEGIN;
3.
4. SELECT id, name FROM singers WHERE id = 2;
--> actual: slow but returns, expected: be blocked on T1

id | name 
---+-----------
 2 | Catalina

5. COMMIT;

The unexpected behavior here is that because I grabbed an exclusive lock in T1 in step 3, I expected the SELECT in step 4 of T2 to block on T1 being committed. What I observed instead is that step 4 in T2 waiting a little bit and returned a result.

It turns out this happens because PGAdapter doesn't send keep-alives to keep transactions active beyond 10 secs. So T1 internally gets aborted after 10 secs of inactivity. Then T2 is able to get a result after T1 has released the locks on those cells of the table.

The reason why PGAdapter doesn't send keep-alives to keep transactions active is to stop users from accidentally holding onto locks for longer than necessary. This makes sense for production apps.

But given that psql is a CLI where users might be trying to run long-running transactions on purpose, it would be nice to keep the transactions alive so you don't get unexpected behaviours like the example above.