Re: UNIQUE INDEX unaware of transactions (a spin of question)

Jarmo Paavilainen writes:

> *I think* this is correct behaviour, ie all that one transaction does should
> be visible to other transactions.

Only in the "read uncommitted" transaction isolation level, which
PostgreSQL does not provide and isn't really that useful.

> But then a question: How is this handled by PostgreSQL? (two parallel
> threads, a row where t=1 allready exist):
>
> begin; // << Thread 1
>     delete from t where i=1;
>
>     // Now thread 1 does a lot of other stuff...
>     // and while its working another thread starts doing its stuff
>
> begin; // << Thread 2
>     insert into t(i) values(1);
> commit; // << Thread 2 is done, and all should be swell
>
>     // What happens here ????????????
> rollback; // << Thread 1 regrets its delete???????????

You can try yourself how PostgreSQL handles this, which is probably not
the right thing since unique contraints are not correctly transaction
aware.

What *should* happen is this:  In "read committed" isolation level, the
insert in the second thread would fail with a constraint violation because
the delete in the first thread is not yet visible to it.  In
"serializable" isolation level, the thread 2 transaction would be aborted
when the insert is executed because of a serialization failure.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter