Re: set-level update fails with unique constraint violation

"Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> 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?

1. Performance. The cost of #2 is very large, and the number of cases
where you actually need it is not.

2. Backwards compatibility. Some apps might be depending on the details
of the behavior.

regards, tom lane