4

I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit. Each time during the error, the primary key of SERIAL increased. After fixing the bug, the table measurements is 

 measurement_id | measurement_size_in_bytes |             time              
----------------+---------------------------+-------------------------------
              1 |                     77777 | 2015-07-14 18:29:56.858703+03
              2 |                       888 | 2015-07-14 18:29:56.882552+03
              3 |                       888 | 2015-07-14 18:30:15.505957+03
              4 |                       888 | 2015-07-14 18:41:01.878106+03
             39 |                     77777 | 2015-07-15 12:11:21.21391+03
             40 |                     77777 | 2015-07-15 12:11:59.551973+03
             41 |                     77777 | 2015-07-15 12:12:05.48982+03
             42 |                     77777 | 2015-07-15 12:13:02.402053+03
             43 |                     77777 | 2015-07-15 12:13:02.419412+03
             44 |                       888 | 2015-07-15 12:13:02.434728+03
         ...

where the amount of error statements has been 35 (= 39-4). This jump in IDs may become a challenge later when I want to integrate this table with a partial index where order matters.

Table

CREATE TABLE measurements
    (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL,
        time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time)
    );

What are the challenges of keys with gaps?

Improve this question
5
  • 3
    You simply should not rely on the order of an 'artificial' key (even the sequence can restart, if defined so). Furthermore, a serial is not supposed to be gapless - if you need this, you have to implement it somehow differently. As possibly the easiest way, you can reset the sequence to max(measurement_id) before each insert. Furthermore, you have a nice timestamp for ordering...
    – András Váczi
    Commented Jul 15, 2015 at 9:44
  • 1
    @dezso Can there be any problems with key with gaps in partial indexes?
    – Léo Léopold Hertz 준영
    Commented Jul 15, 2015 at 9:45
  • 1
    No, but show your (planned) index to get a real answer. BTW, your unique constraint does not make much sense as measuerment_id is already unique.
    – András Váczi
    Commented Jul 15, 2015 at 9:47
  • 3
    max(measurement_id) +1 is a death sentence in case of concurrency. never ever do that ! in case two people do this concurrently it ends up in disaster. first of all i doubt that a normal sequence with no meaning has to be without gaps. in case you really need one: create a second table with just one entry, which serves as serialization tool
    – Hans-Jürgen Schönig
    Commented Jul 15, 2015 at 9:50
  • 2
    @Hans-JürgenSchönig as the column is the PK, the problems would be prevented by the constraint. One has to handle the resulting error for sure :) Other than that, you are right - some sort of serialization (be it explicit locking and/or lookups to another table or else) is needed.
    – András Váczi
    Commented Jul 15, 2015 at 9:54

1 Answer 1

Reset to default
7

What you see is completely normal and expected.

Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back.

Unless you really need a gapless series of values, you don't have to worry about it. This is how most (well over 99%, I guess) database tables work.

If you need it, be careful if you have concurrent inserts on the table - you will need some locking to exclude the possibility of choosing the same value by two or more sessions running in parallel.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.