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 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: References: <51b20cb4$0$14017$426a34cc@news.free.fr> <51b2191b$0$2035$426a74cc@news.free.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 Mateusz Viste 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