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(at)mcmaster(dot)wattle(dot)id(dot)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