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

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(at)gmx(dot)net http://funkturm.homeip.net/~peter