UNIQUE INDEX unaware of transactions

Hannu Krosing <hannu@tm.ee> writes:

It seems that our current way of enforcing uniqueness knows nothing
about transactions ;(

when you

create table t(
i int4 primary key
);"""

and then run the following query

begin;
delete from t where i=1;
insert into t(i) values(1);
end;

in a loop from two parallel processes in a loop then one of them will
almost instantaneously err out with

ERROR: Cannot insert a duplicate key into unique index t_pkey

Have you tried running this test with transaction isolation set to
SERIALIZABLE?

-Doug
--
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time... --Dylan

Hi,

A bit theoretical question (sorry for spelling and maybe OT).

...

It seems that our current way of enforcing uniqueness knows nothing
about transactions ;(

...

create table t(i int4 primary key);

...

begin;
delete from t where i=1;
insert into t(i) values(1);
end;

in a loop from two parallel processes in a loop then one of them will
almost instantaneously err out with

ERROR: Cannot insert a duplicate key into unique index t_pkey

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

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???????????

// Jarmo

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

Peter Eisentraut wrote:

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.

...

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

Is there any way to make unique indexes transaction-aware ?

Are competeing updates on unique indexes transaction-aware ?

I.e. can I be sure that if I do

begin;
if select where key=1 result exists
then update where key=1
else insert(key,...)values(1,...)
end;

then this will have the expected behaviour in presence of multiple
concurrent updaters?

------------------
Hannu

Hannu Krosing writes:

Is there any way to make unique indexes transaction-aware ?
Are competeing updates on unique indexes transaction-aware ?

AFAIK, indexes are not transaction-aware at all, they only provide
information that there might be a visible row at the pointed-to location
in the table. (This is also the reason that you cannot simply fetch the
data from the index, you always need to look at the table, too.)

Personally, I think that to support proper transaction-aware and
deferrable unique contraints, this needs to be done with triggers,
somewhat like the foreign keys.

I.e. can I be sure that if I do

begin;
if select where key=1 result exists
then update where key=1
else insert(key,...)values(1,...)
end;

then this will have the expected behaviour in presence of multiple
concurrent updaters?

I guess not.

The classical example is

update t set x = x + 1;

which won't work if x is constrained to be unique.

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