SAVEPOINT and FOR UPDATE - Mailing list pgsql-general

I'm curious to know more about the postgres implementation of subtransactions via SAVEPOINT.

If I wanted to set up a multi-statement transaction in which I needed multiple SELECT ... FOR UPDATE + UPDATE blocks, it would seem advantageous to be able to combine the SELECT ... FOR UPDATE clauses with the corresponding UPDATE clauses in a subtransaction in order to avoid locking rows for the duration of the entire outer transaction. In my experimentation, I'm not seeing this behavior, so I'm wondering if I'm misreading or overlooking something in the docs about how to use SAVEPOINT to create subtransactions.

Here's what I set up as a basic test case in psql:

postgres=# CREATE TABLE updateable1 ( id int primary key );

postgres=# INSERT INTO updateable1 VALUES ( 1 );

postgres=# START TRANSACTION;

postgres=# SAVEPOINT u1;

postgres=# SELECT id FROM updateable1 WHERE id = 1 FOR UPDATE;

Then, in a separate session, I do this:

postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;

This, appropriately, waits.

In the original session, I now do this:

postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;

postgres=# RELEASE u1;

Unfortunately, the second session is still waiting and continues to do so until I commit the transaction started in the first session. I sort of expected the release of the savepoint to be tantamount to a commit of the subtransaction, but it doesn't appear to have been.

I'd like a method for doing the following:

START TRANSACTION;

// do work

// start subtransaction

SELECT ... FOR UPDATE;

UPDATE ...;

// commit subtransaction

// do more work

COMMIT;

Is there any way to achieve the behavior I'm after?