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