Re: set-level update fails with unique constraint violation

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com>
To: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: set-level update fails with unique constraint violation
Date: 2010-01-06 14:47:34
Message-ID: 8e2dbb701001060647l40bb37acpcbe52dcfd2aafaf5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

2010/1/5 Roman Neuhauser <neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz>:
> # Jayadevan(dot)Maymala(at)ibsplc(dot)com / 2010-01-04 10:03:29 +0530:
>> This seems to work..
>> UPDATE x  set i=i+1
>> from  (select i as m from x order by m desc) y   where x.i = y.m
>> Jayadevan
>
> Thanks, that nicely achieves the illusion of atomic immediate checking.
>
> --
> Roman Neuhauser

That is not guaranteed to work. Depending on how the optimiser does
the join, the reverse ordering may not be preserved in the update. Try
it for larger tables (for me it fails at 100000 rows).

Regards,
Dean

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John T. Dow 2010-01-06 14:53:28 Optimistic locking with multiple rows
Previous Message Daniel Verite 2010-01-06 14:37:43 Re: set-level update fails with unique constraint violation