Re: set-level update fails with unique constraint violation

2010/1/6 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>        Dean Rasheed wrote:
>
>> 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
>
> Thanks for clarifying that. I've just tried the different scenarios with
> 8.5alpha3, and I find that these improvements are quite useful and welcome.
> But still I wonder why there is that difference in behavior between NON
> DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
> doesn't get deferred by using SET CONSTRAINTS.
> In the first case, we get the "after each row" behavior with the pk=pk+1
> failure, as with the previous PG versions.
> In the second case, we get the "after each statement" behavior which I
> believe complies with the standard, contrary to the first case, and
> successfully achieves the pk=pk+1 update as expected.
> Personally, I would have imagined that behavior #1 would be removed once
> behavior #2 was implemented, not that the two would co-exist. Is there a
> reason to keep #1?
>

Performance is one reason (perhaps the only one?). #1 is implemented
using a unique index, which is checked as each row is inserted. #2
uses triggers in addition to the unique index (conflicts are queued up
to be re-checked at the end of the command/transaction). So #1 will
always out-perform #2 (unless there aren't any temporary conflicts to
be re-checked).

> Also, I read in the current doc for 8.5:
> http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html
> <quote>
> DEFERRABLE
> NOT DEFERRABLE
>
>    This controls whether the constraint can be deferred. A constraint that
> is not deferrable will be checked immediately after every command
> </quote>
>
> "after every command" seems to describe behavior #2, not #1.
>

Hmm. Yes that comment is misleading in this context. Non-deferrable
unique constraints are currently checked after each row.

Regards,
Dean