SAVEPOINT and FOR UPDATE

Поиск
Список
Период
Сортировка
От Thomas F. O'Connell
Тема SAVEPOINT and FOR UPDATE
Дата
Msg-id B0E6F561-656E-4B3B-9615-D0F7A80DF027@sitening.com
обсуждение исходный текст
Ответы Re: SAVEPOINT and FOR UPDATE  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: SAVEPOINT and FOR UPDATE  (Jeff Davis <pgsql@j-davis.com>)
Список 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?

--
Thomas F. O'Connell
Sitening, LLC

3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)

В списке pgsql-general по дате отправления:

Предыдущее
От: "Carlo Stonebanks"
Дата:
Сообщение: Pl/TCL: Is my Windows install missing scripts?
Следующее
От: Jim Nasby
Дата:
Сообщение: PITR timeline question