Thread: UNIQUE INDEX unaware of transactions

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

I guess this can be classified as a bug, but I'm not sure how easy it 
is to fix it.

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


I tested it with the followiong python script

#!/usr/bin/python

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

def main():   import _pg   con = _pg.connect('test')   for i in range(500):       print '%d. update' % (i+1)
con.query(sql_reinsert_item)

if __name__=='__main__':   main()

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 1delete 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 2insert 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