Re: unique & update

On 31 Jan 2002, Doug McNaught wrote:

> "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il> writes:
>
> > How can I rollback in a function ?
> > I mean after update has been done, I do some checking in the Function and
> > then decide that I want to quit and rollback all what inserted/updated in
> > the start of the function ?
>
> A function is always executed inside a transaction. You can roll back
> the whole transaction by throwing an error, but you can't roll back
> just what you did in the function.
>
> There is talk of adding savepoints to PG (possibly in 7.3) which would
> allow this, but right now there is no mechanism for it.
>
> > Also if I attach trigger that check column uniqueness to an update will it
> > check it per updated row or at the end of the action after all the table got
> > updated ?
>
> Why not just create a unique index rather than using a trigger?

The issue is that unique indexes check their uniqueness at the "wrong"
time. Constraints should AFAICS be checked after the statement, not
during, so cases like foo=foo+1 (where foo is unique) should succeed, but
in postgres it will only succeed if the values of foo are checked in an
order such that you never have an overlapping value (where one's been
changed and the other hasn't).