Re: Static snapshot data - Mailing list pgsql-hackers

On Thu, 15 May 2003 19:39:55 -0400, Alvaro Herrera
<alvherre@dcc.uchile.cl> wrote:
>When a SERIALIZABLE subtransaction is started in a READ COMMITTED
>parent, the SerializableSnapshot is just calculated again.

I would not allow this, see below ...

>The user can
>change from READ COMMITTED to SERIALIZABLE when starting a
>subtransaction, but not the other way around.

You cannot propose this and agree to my three rules at the same time.
Rule 3 says that these two sequences of commands are equivalent:
             A                                   B
BEGIN;                              BEGIN;
SET TRANSACTION ISOLATION LEVEL     SET TRANSACTION ISOLATION LEVEL   READ COMMITTED;                     READ
COMMITTED;
SELECT version();                   SELECT version();
BEGIN;
SET TRANSACTION ISOLATION LEVEL     SET TRANSACTION ISOLATION LEVEL   SERIALIZABLE;                       SERIALIZABLE;
-- error!
 
COMMIT;
SELECT timeofday();                 SELECT timeofday();
COMMIT;                             COMMIT;

While you want A to succeed, it's clear that B results in an error.

What we *do* need is a saved_isolation_level on the transaction
information stack, so we can restore the state of the enclosing
transaction on subtransaction ROLLBACK (the same is true for
changeable GUC variables):

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- This is allowed (no query so far):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET random_page_cost = 100;
ROLLBACK;
-- main transaction still active,
-- TRANSACTION ISOLATION LEVEL is READ COMMITTED

>  (Note that it _is_
>possible to change from SERIALIZABLE to READ COMMITTED in the topmost
>transaction).

fred=# BEGIN;
BEGIN
fred=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
fred=# SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET
fred=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
fred=# SELECT version();                          version
-------------------------------------------------------------PostgreSQL 7.3 on i586-pc-linux-gnu, compiled by GCC
2.95.2
(1 row)

fred=# SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any
query

>> :d) what commands come before me in the current transaction
>> :   (curcid)
>> 
>> I propose that we don't change this, except that d) should say "... in
>> the current transaction tree"
>
>There's no need for this.  Starting a transaction should be a new
>CommandId for the parent transaction, so the tuples written by a
>transaction that's not me but belong to my transaction tree are
>effectively treated as if they were from a previous CommandId.

But if you have subtransactions in functions called by the current
query, they should be treated like *higher* commandIds.

>> It might help, if we continue to increment cid across subtransaction
>> boundaries.
>
>We don't need to, because
>
>> This will be handled by HeapTupleSatisfiesXxxx using pg_subtrans:
>> 
>>  .  We find a tuple (having p=2) with xmin=3.
>> 
>>  .  In pg_clog we find that xact 3 is a committed subtransaction.
>> 
>>  .  We lookup xact 3's parent transaction in pg_subtrans and get
>>     parent xact = 1.
>> 
>>  .  Consulting the transaction information stack we find out that
>>     xact 1 is one of our own currently active transactions (in this
>>     case the only one).
>> 
>>  .  Because the tuple's cmin (4) is less than CurrentCommandId (6)
>>     the tuple is visible.
>
>This last rule should be replaced by:
>
>   . Because the tuple's xmin is not my XID, the tuple is visible.
>
>We need to check the CurrentCommandId only if xmin (or xmax) is my own
>XID.

This sounds good as long as queries are issued by a client application
one after the other.  But will it still work when we have
subtransactions in functions?

CREATE TABLE a (i int, t text);
INSERT INTO a VALUES (1, '1');
INSERT INTO a VALUES (2, '2');
INSERT INTO a VALUES (3, '3');

CREATE OR REPLACE FUNCTION fa (int) RETURNS bool AS '
BEGIN INSERT INTO a VALUES (2 * $1, ''new''); RETURN  true;
END;
' LANGUAGE 'plpgsql';

BEGIN;
-- do some queries to raise CommandId ...
UPDATE a SET i = 3 * i, t = 'old' WHERE fa(a.i);
COMMIT;

SELECT xmin,cmin,* FROM a; xmin  | cmin | i |  t
--------+------+---+-----243871 |   12 | 2 | new243871 |   10 | 3 | old243871 |   15 | 4 | new243871 |   10 | 6 |
old243871|   17 | 6 | new243871 |   10 | 9 | old
 
(6 rows)

The 'new' rows are not seen by the UPDATE because they are inserted by
the same transaction but with a higher CommandId.

Now change fa() to wrap the INSERT statement into a subtransaction.
Per your proposal the 'new' rows would have xmin = 243872, 243873,
243874 and cmin = 0.  "Because the tuple's xmin is not my XID", they
would be visible to the UPDATE.  Halloween!

>> This looks almost like struct TransactionStateData, except that
>> commandId, startTime, and startTimeUsec belong only to the main
>> transaction.
>
>Hm, why do you want to left out the startTime and startTimeUsec?

I thought they're useless ...

Maybe the term "nested transactions" is confusing.  I'm starting to
believe that my position is better described by "multiple savepoints".
Are we still talking about the same thing?

ServusManfred