Creating sequential certificate numbers with postgres

Question

I have a table that holds certificate numbers. Each certificate consists of a series letter and a serial number. Certificates must be unique and no gaps are allowed, i.e. if A-1234 exists, also A-1233 must exist. Each series has its own serials, i.e. A-1234 and B-1234 can happily coexist.

The table holding the information is this:

CREATE TABLE "Certificate"
(
    id serial NOT NULL,
    series character(1) NOT NULL,
    serial integer NOT NULL,
    CONSTRAINT "Certificate_pkey" PRIMARY KEY (id ),
    CONSTRAINT "Certificate_series_serial_key" UNIQUE (series , serial )
)
WITH (
    OIDS=FALSE
);

Now the problem is how to create the next serial number, given a specific series. Postgres sequences doesn't seem to be the way to go, as these would create gaps if an insert fails for whatever reason or if a transaction, which required a new value from a sequence, is rolled back.

My idea was the following:

#! /usr/bin/env python3.2

import postgresql.driver.dbapi20 as pgdb

credentials = #my db credentials

SERIES = 'B'

dbcon = pgdb.connect (**credentials)
cursor = dbcon.cursor ()

cursor.execute ('''insert into "Certificate" ("series", "serial")
    (select %s, coalesce (max ("serial"), 0) + 1
    from "Certificate"
    where "series" = %s) ''', [SERIES] * 2)

input () #just to keep the transaction open some time

cursor.close ()
dbcon.commit ()
dbcon.close ()

Unfortunately this is not thread safe. If I start this script twice on two shells (say A and B) the following happens:

I start the script on shell A, it holds inside the transaction at input ().
I start the script on shell B, it holds inside the transaction at input ().
I press enter on shell A and the transaction commits.
At this precise moment shell B throws an (expected) postgresql.exceptions.UniqueError: duplicate key value violates unique constraint "Certificate_series_serial_key".

How can I insert the next certificate into this table, based on a given series letter, in a thread safe manner?

Clodoaldo Neto · Answer 1 · 2012-12-18T16:38:58.577

2

Just catch the exception and try again

while True:
    try:
        cursor.execute ('''
            insert into "Certificate" ("series", "serial")
            select %s, coalesce (max ("serial"), 0) + 1
            from "Certificate"
            where "series" = %s
            ''', [SERIES] * 2)
        dbcon.commit ()
        break
    except postgresql.exceptions.UniqueError:
        continue

edited Dec 18 '12 at 16:38

answered Dec 18 '12 at 16:22

Clodoaldo Neto

118,695
26
233
260

Hm, I'm not sure. This looks a not like a sledge hammer solution to me. – Hyperboreus Dec 18 '12 at 17:00
A bit like a sledge hammer solution, I mean. Spell check screwed up. – Hyperboreus Dec 18 '12 at 17:03
@Hyperboreus If the number of inserts is not huge it will work smoothly. You can add a bit of waiting before continuing. There are very complex solutions to this problem but they are only worth when dealing with huge concurrency. – Clodoaldo Neto Dec 18 '12 at 17:08
You have to trap errors anyway. There are a *lot* of things that can keep an INSERT statement from succeeding. – Mike Sherrill 'Cat Recall' Dec 18 '12 at 19:30
@Clodoaldo Thanks for your answer and comments. Could you point to me to one of those complex solutions. – Hyperboreus Dec 18 '12 at 20:05
@Hyperboreus The search term is _gap-less sequence_ – Clodoaldo Neto Dec 18 '12 at 20:11
Thank you. Unfortunately googling "postgres gap-less sequence" only dug up one-eyed helping blind. I will go with your try except approach with increasing random timeouts (like ethernet) after each fail. – Hyperboreus Dec 18 '12 at 23:06

score 2 · Accepted Answer · edited May 23 '17 at 12:18

You're trying to create a gapless sequence. There's lots of info about it out there. Here's an answer I wrote on the topic a while ago and another here.

You have the right idea with the Certificate table, though I'd consider a name that makes it clearer that it's a table of certificate series numbers, not of certificates. certificate_series_number for example.

What you want to do to reliably generate a new ID is, within a transaction:

    curs.execute("UPDATE certificate SET serial = serial + 1 WHERE series = %s RETURNING serial")
    newserial = curs.fetchone()[0]

The UPDATE will take a lock on the row for that series that causes other updates to that series to block until this transaction commits or rolls back. It does not interfere with concurrent transactions affecting other series.

This is basically equivalent to doing a SELECT ... FOR UPDATE followed by a regular UPDATE without RETURNING, it's just more convenient and simpler.

You will notice that this does not create a new series if it did not already exist. Honestly, the best approach there in your case is to pre-allocate all the series you might use since the space is so constrained. In more complex cases you can use upsert logic, but it's rather difficult to make it work smoothly, and you have to be prepared for transaction retries. See http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

Note that if transactions use more than one series in a single transaction they can deadlock with each other unless they're extremely careful to always acquire the serials from series in the same order, eg alphabetically by series ID.

@Hyperboreus Achieving consistency and the appearance of serial execution in a concurrent environment *is* complicated, there's really no way around that. — Craig Ringer, Dec 19 '12 at 00:03
Update works IF the table will not be used in a foreign key relationship. It is not clear from the question. Otherwise all the certificate numbers must exist. — Clodoaldo Neto, Dec 19 '12 at 00:09
@Clodoaldo I see how you read it. Yes, you're quite right. If that's an issue I would personally use a separate table for certificate number generation that contains only the latest values. — Craig Ringer, Dec 19 '12 at 00:17
What do you think about having a seperate process (singleton) that receives requests for certificates, queues them, processes them sequentially and returns the certId to the caller? — Hyperboreus, Dec 20 '12 at 02:58

Creating sequential certificate numbers with postgres

2 Answers2