Re: set-level update fails with unique constraint violation

2010/1/4 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>        David Fetter wrote:
>
>> The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem
>
> That fix has a drawback: when the unique constraint is violated, the rest of
> the transaction runs with data that is somehow corrupted, with duplicate
> values being visible. It may be uneasy to predict if and how the statements
> following the temporary-ignored constraint violation will misbehave.
> Generally, the transaction will ultimately fail and the mess will be cleaned
> up by the rollback, but in the worst case it may not even fail, for instance
> if the offending rows get deleted before the end.
>

No, deferrable constraints are more flexible than that, so you can
have end-of-statement checks if that's what you want.

A deferrable constraint has 2 modes of operation, depending on how you
choose to define the constraint:

1). DEFERRABLE INITIALLY IMMEDIATE will result in the constraint being
checked after each statement in the transaction. This will allow the
i=i+1 UPDATE to succeed, but any UPDATE which causes uniqueness to be
violated at the end of the statement will fail immediately, and you
will have to rollback.

2). DEFERRABLE INITIALLY DEFERRED will cause the constraint check to
be done at the end of the transaction (or when SET CONSTRAINTS is
called). This will allow the constraint to be temporarily violated by
statements inside a transaction, and if the duplicates are then
deleted, the transaction will succeed.

If you just specify DEFERRABLE, then INITIALLY IMMEDIATE is the default.
See http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html

This is all per the SQL spec, and also the same behaviour as Oracle.

So there is quite a bit of flexibility - you may choose to have the
constraint checked at any of these times:
- after each row (the default for NON DEFERRABLE constraints)
- after each statement (DEFERRABLE [INITIALLY IMMEDIATE])
- at the end of the transaction (DEFERRABLE INITIALLY DEFERRED)
- whenever you want in a transaction using SET CONSTRAINTS

Regards,
Dean