Re: set-level update fails with unique constraint violation

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?

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.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org