Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #460

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

Show all headers | View raw

Mateusz Viste <mateusz.viste@border6.com> writes:
> And now my sequence wraps, and gets reseted to 0. I will be able to insert 2 
> records (they will get ids 0 and 1), and then, on the third one, I will get 
> a collision (because the id '2' is already present from the past, and I'd 
> hope postgresql would just skip it and take the next FREE id - that is the 
> id '3').
>
> Is there any possible way to make postgresql behave like that, or am I 
> daydreaming?

You can use a BEFORE TRIGGER instead of using the sequence as a DEFAULT
value, and have your BEFORE TRIGGER code check for existing id in your
table. Given a primary key index it should be mostly fast enough.

The real problem is of course going to be dealing with concurrency,
where you want to avoid assigning the same next free id to two
concurrent queries. You can do that easily enough with LOCKing the whole
table, of course, but that's not going to cut it for you I'm sure.

The next thing you can use is an advisory lock which will only lock the
id you want to use, in a way that allows you to check for the lock being
already taken by a concurrent session without waiting.

Have a look at the documentation for triggers and advisory locks here:

  http://www.postgresql.org/docs/current/static/triggers.html
  http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

  http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS
  http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS    

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next 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