Search Postgresql Archives

Re: set-level update fails with unique constraint violation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



	Tom Lane wrote:

> "Daniel Verite" <daniel@xxxxxxxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux