Re: Revisited: Transactions, insert unique.

Doesn't

     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

have to come within transaction - ie

     BEGIN;
     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

In this order your test seems to behave correctly - only 1 row inserted.
--
Rex McMaster                       rex@mcmaster.wattle.id.au
                     http://www.compsoft.com.au/~rmcm/pgp-pk

Ed Loehr writes:
 >
 > But the following sequence seems to contradict this:
 >
 > -- Transaction A
 > DROP TABLE foo;
 > CREATE TABLE foo (id INTEGER);
 > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 > BEGIN;
 > -- Transaction B
 > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 > BEGIN;
 > INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
 > = 1);
 > COMMIT;
 > SELECT * FROM foo;
 > -- Transaction A
 > SELECT * FROM foo;
 > INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
 > = 1);
 > SELECT * FROM foo;
 > COMMIT;
 >
 > This sequence allows B's inserts, AFTER A began, to be seen by A (at least,
 > in pgsql 7.0beta3).
 >
 > Anyone understand what's going on here?  Bug?
 >
 > Regards,
 > Ed Loehr