Multixact and prepared transactions

From: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
To: PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Multixact and prepared transactions
Date: 2009-11-16 09:04:27
Message-ID: 4B01159B.7080501@iki.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

While reviewing the hot standby patch, I noticed a little existing bug
with multixacts and prepared transactions. If a prepared transaction has
a shared lock on a tuple, represented by a multixact, other backends can
sometimes update the tuple anyway.

Steps to reproduce, using two psql sessions:

CREATE TABLE foo (id int4);
INSERT INTO foo VALUES (1);

In session 1:

-- lock the tuple for the first time.
postgres=# begin; SELECT xmax,xmin, * from foo FOR SHARE;
BEGIN
xmax │ xmin │ id
──────┼──────┼────
713 │ 667 │ 1
(1 row)

-- leave the transaction open

In session 2:

-- lock the tuple the 2nd time, turning xmax into a multixact.
postgres=# begin; SELECT * FROM foo FOR SHARE; PREPARE TRANSACTION 'foo';
BEGIN
id
────
1
(1 row)

PREPARE TRANSACTION

In session 1:

postgres=# commit;
COMMIT
-- the prepared transaction is now the only live member of the multixact

postgres=# UPDATE foo SET id=id; -- This should block waiting on the
prepared xact!
UPDATE 1

We seem to have neglected prepared transactions in the logic that tracks
the oldest visible multixact. OldestMemberMXactId doesn't contain
entries for prepared transactions, so the UPDATE incorrectly considers
the multixact as an old obsolete one.

A straightforward fix is to enlarge OldestMemberMXactId to make room for
max_prepared_transactions extra entries, and at prepare transfer the
value of the current backend to one of those slots.

- Heikki

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2009-11-16 09:19:13 psql command line variables are unknown when -c SQL statement are executed
Previous Message Magnus Hagander 2009-11-16 08:30:28 Re: BUG #5065: pg_ctl start fails as administrator, with "could not locate matching postgres executable"