Re: set-level update fails with unique constraint violation

Tom Lane wrote:

> "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.

Per Dean's explanation upthread, It looks like an additional cost for #2
would occur mostly when temporary conflicts occur, that is, when it's needed.

I've tried UPDATEs of a primary key in batches of 1M rows with 8.5, and in
the general case of no conflict, I get #2 being about 8-15% slower than #1.
I've seen no difference for INSERTs.
When there are temporary conflicts, #2 is slower but succeeds whereas #1
fails, so #2 is the winner.
When there are persistant conflicts, #2 fails slower than #1, but do we
really care?

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

Apparently, the occurrence of conflicts during the execution is mostly
unpredictable anyway, from the point of view of the end user. For example I
was under the illusion that UPDATE...SET pk=pk-1 always worked, but I've
discovered while testing that it wasn't the case. Conversely depending on it
to fail, for this update or a similar update, thats seems insane for an app.

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