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

Groups > comp.databases.postgresql > #457 > unrolled thread

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

Back to article view | Back to comp.databases.postgresql

Contents

  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

#457 — How do I avoid nextval() colliding with existing entries on wrapping?

Hello all,

I am kind of stuck with a problem these days. I have a table with a primary 
key managed via a sequence. like this:

db=# \d test
                             Table "public.test"
 Column |       Type        |                     Modifiers
--------+-------------------+------------------------------------------
 id     | integer           | not null default 
nextval('test_id_seq'::regclass)
 data   | character varying | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

I badly need the value of the id column to always be in the range 0..99999, 
so I added a "MAXVALUE 99999 CYCLE" directive to the sequence.

Now, the problem is this:
I have a few applications adding and removing entries from the table, 
therefore the id is steadily increasing over time, and significant 'gaps' 
are appearing. But when the sequence wraps, it starts colliding with some 
old surviving entries with same id.

Until now, I naively thought that postgresql is smart enough to hop over 
existing ids, but as it appear, it was a dumb assumption. :/

How should I deal with such situation? The only solution I though about is 
to handle ids of my table myself, by checking each time if the id is free 
before inserting it.. but this will obviously make the whole thing awfully 
slow. Is there any better method out there?

cheers,
Mateusz

[toc] | [next] | [standalone]

#458

Mateusz Viste <mateusz@viste-family.net> writes:
> I badly need the value of the id column to always be in the range 0..99999, 
> so I added a "MAXVALUE 99999 CYCLE" directive to the sequence.

You're in an uncomfortable situation then, and I'm not sure a sequence
is what you need at all. Have a read of and see what you think

  http://www.varlena.com/GeneralBits/130.php

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#459

Hi,

This is an interesting lecture, but I do not think it applies to my case - 
from what I understand, the article you link to is solving the problem of 
gaps in sequence-powered primary keys. But in fact, I am totally fine with 
gaps. My application creates gaps all the time, since it removes records 
from my table.

My problem is more about the sequence counter wraping (going back to its 
initial value), and then colliding with old entries.

To oversimplify things, let's say that I need my ids to be in the range 
0..9. By successively adding/removing records, I could end up with such 
entries in the table:

id | data
2  | ...
4  | ...
9  | ...

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?

cheers,
Mateusz




Dimitri Fontaine wrote:

> Mateusz Viste <mateusz@viste-family.net> writes:
>> I badly need the value of the id column to always be in the range
>> 0..99999, so I added a "MAXVALUE 99999 CYCLE" directive to the sequence.
> 
> You're in an uncomfortable situation then, and I'm not sure a sequence
> is what you need at all. Have a read of and see what you think
> 
>   http://www.varlena.com/GeneralBits/130.php
> 
> Regards,

[toc] | [prev] | [next] | [standalone]

#460

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

[toc] | [prev] | [next] | [standalone]

#463

Hi,

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).

I will definitely try to play in these directions.

Thank you very much for your explanations!

have a nice weekend,
Mateusz




Dimitri Fontaine wrote:

> 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,

[toc] | [prev] | [next] | [standalone]

#464

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web