PostgreSQL transaction restart

Question

I'm starting to play with PostgreSQL and noticed that sequences never rollback, even on failed INSERT.
I've read that it is as expected to prevent duplicated sequences on concurrent transactions and I found that weird as my database experience is only with GTM where transaction restarts are common and used precisely for this.

So I wanted to test restarts in PGSQL and loaded this in a database:

CREATE SEQUENCE account_id_seq;

CREATE TABLE account
(
  id integer NOT NULL DEFAULT nextval('account_id_seq'),
  title character varying(40) NOT NULL,
  balance integer NOT NULL DEFAULT 0,
  CONSTRAINT account_pkey PRIMARY KEY (id)
);

INSERT INTO account (title) VALUES ('Test Account');

CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$
DECLARE
    cc integer;
BEGIN
    cc := balance from account where id=1;

    RAISE NOTICE 'Balance: %', cc;
    perform pg_sleep(3);

    update account set balance = cc+10 where id=1 RETURNING balance INTO cc;

    return cc;
END
$$
LANGUAGE plpgsql;

So, function mytest() will retrieve balance, wait 3 seconds (to let me launch the other process) and then update balance based on the saved variable.

I now launch 2 calls to this function directly from shell:

void$ psql -c "select * from account where id=1"
 id |    title     | balance 
----+--------------+---------
  1 | Test Account |       0
(1 row)

void$ psql -c "select mytest()" & PIDA=$! && psql -c "select mytest()" && wait $PIDA
[1] 3312
NOTICE:  Balance: 0
NOTICE:  Balance: 0
 mytest 
--------
     10
(1 row)

 mytest 
--------
     10
(1 row)

[1]+  Done                    psql -c "select mytest()"
void$ psql -c "select * from account where id=1"
 id |    title     | balance 
----+--------------+---------
  1 | Test Account |      10
(1 row)

I would expect balance to be 20, not 10, as the last transaction to be committed should restart as the "view" of balance from account where id=1 changed during processing...

I've read about transaction isolation in official documentation and it sounds to me that the default read committed should enforce this behavior precisely..
I've also tested changing the isolation level to serializable and then the last transaction committed does throw an exception but I would like to know if there isn't any "transaction restart" functionality (as I described) or if I'm missing something...

Answer 1

You get a "restart" automatically if you use proper queries with row level locks. To be precise, the transaction is not restarted as a whole, it just waits its turn when trying to lock a row in default transaction isolation READ COMMITTED:

CREATE OR REPLACE FUNCTION mytest()
   RETURNS integer AS
$func$
DECLARE
   cc integer;
BEGIN
   SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE;

   RAISE NOTICE 'Balance: %', cc;
   PERFORM pg_sleep(3);

   UPDATE account SET balance = cc+10
   WHERE id = 1
   RETURNING balance
   INTO cc;

   RETURN cc;
END
$func$  LANGUAGE plpgsql;

SELECT ... FOR UPDATE takes a row level lock to announce the claim that this row is going to be updated. The same function trying the same in another transaction will be blocked and wait until the first commits or rolls back - then take the lock itself and build on the updated row so that the result of your experiment will be 20, not 10.

You can have the same much more efficiently with a plain and simple UPDATE query that takes the appropriate FOR UPDATE locks automatically:

UPDATE account
SET    balance = balance + 10
WHERE  id = 1
RETURNING  balance;

These recent questions seem to have run into similar problems. Detailed explanation and links:

• Function taking forever to run for large number of records
• Lock for SELECT so another process doesn't get old data