Groups > comp.databases.postgresql > #464

Re: How do I avoid nextval() colliding with existing entries on wrapping?

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail
From	Dimitri Fontaine <dimitri@2ndQuadrant.fr>
Newsgroups	comp.databases.postgresql
Subject	Re: How do I avoid nextval() colliding with existing entries on wrapping?
Date	Sun, 09 Jun 2013 10:28:26 +0200
Organization	A noiseless patient Spider
Lines	21
Message-ID	<m2ehcbhemt.fsf@2ndQuadrant.fr> (permalink)
References	<51b20cb4$0$14017$426a34cc@news.free.fr> <m2y5alhnf3.fsf@2ndQuadrant.fr> <51b2191b$0$2035$426a74cc@news.free.fr> <m2r4gdhhiv.fsf@2ndQuadrant.fr> <51b2fd52$0$14018$426a74cc@news.free.fr>
Mime-Version	1.0
Content-Type	text/plain
Injection-Info	mx05.eternal-september.org; posting-host="8da1d3a971b7bd1218a57efad80890f1"; logging-data="24767"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18144sNV2rUBm8zzRQsm0MG"
User-Agent	Gnus/5.130006 (Ma Gnus v0.6) Emacs/24.3 (darwin)
Cancel-Lock	sha1:awj7A5t98h8dVPGyhWv9J07FNco= sha1:dwg1ZcVmyFYh/NzeUBHLTnqycos=
Xref	csiph.com comp.databases.postgresql:464

Show key headers only | View raw

Mateusz Viste <mateusz@viste-family.net> writes:
> I understand the idea now (I'm not a quick catcher sometimes). :)
> I believe I could even use the before trigger thing for checking the 
> availability of the id, but still rely on a sequence for the concurency 
> aspect, to avoid dealing with the whole locking stuff (ie. the BEFORE 
> TRIGGER asks the SEQUENCE for an id, checks if the id is available, if not 
> it asks for the next one, etc in a loop).

That's the basic idea. The race condition exists between checking of the
id is available and using it: you can have a case where it's known to be
available but will still cause a unique_violation error when used,
because a concurrent transaction has been doing the same steps at the
same time, just won in the usage.

To protect against that race condition you can use another loop or an
advisory lock.

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

How do I avoid nextval() colliding with existing entries on wrapping? Mateusz Viste <mateusz@viste-family.net> - 2013-06-07 18:39 +0200
  Re: How do I avoid nextval() colliding with existing entries on wrapping? Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2013-06-07 18:54 +0200
    Re: How do I avoid nextval() colliding with existing entries on wrapping? Mateusz Viste <mateusz.viste@border6.com> - 2013-06-07 19:32 +0200
      Re: How do I avoid nextval() colliding with existing entries on wrapping? Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2013-06-07 21:01 +0200
        Re: How do I avoid nextval() colliding with existing entries on wrapping? Mateusz Viste <mateusz@viste-family.net> - 2013-06-08 11:44 +0200
          Re: How do I avoid nextval() colliding with existing entries on wrapping? Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2013-06-09 10:28 +0200

csiph-web