CODEWITHSUNDEEP
postgresqlprimary-keyauto-increment
Kayo
Kayo

Reputation: 11

Values missing in postgres serial field

I run a small site and use PostgreSQL 8.2.17 (only version available at my host) to store data. In the last few months there were 3 crashes of the database system on my server and every time it happened 31 ID's from a serial field (primary key) in one of the tables were missing. There are now 93 ID's missing.

Table:

CREATE TABLE "REGISTRY"
(
  "ID" serial NOT NULL,
  "strUID" character varying(11),
  "strXml" text,
  "intStatus" integer,
  "strUIDOrg" character varying(11),
)

It is very important for me that all the ID values are there. What can I do to to solve this problem?

Upvotes: 1

Views: 3625

Answers (3)

Kayo
Kayo

Reputation: 11

Thanks to the answers from Matthew Wood and Frank Heikens i think i have a solution.

Instead of using serial field I have to create my own sequence and define CACHE parameter to 1. This way postgres will not cache values and each one will be taken directly from the sequence :)

Thanks for all your help :)

Upvotes: -2

Tometzky
Tometzky

Reputation: 23890

You can not expect serial column to not have holes.

You can implement gapless key by sacrificing concurrency like this:

create table registry_last_id (value int not null);
insert into registry_last_id values (-1);

create function next_registry_id() returns int language sql volatile
as $$
    update registry_last_id set value=value+1 returning value
$$;

create table registry ( id int primary key default next_registry_id(), ... )

But any transaction, which tries to insert something to registry table will block until other insert transaction finishes and writes its data to disk. This will limit you to no more than 125 inserting transactions per second on 7500rpm disk drive.

Also any delete from registry table will create a gap.

This solution is based on article Gapless Sequences for Primary Keys by A. Elein Mustain, which is somewhat outdated.

Upvotes: 3

Frank Heikens
Frank Heikens

Reputation: 126971

Are you missing 93 records or do you have 3 "holes" of 31 missing numbers?

A sequence is not transaction safe, it will never rollback. Therefor it is not a system to create a sequence of numbers without holes.

From the manual:

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values. setval operations are never rolled back, either.

Upvotes: 1

Related Questions