postgres oracle gap free sequence - ghdrako/doc_snipets GitHub Wiki

gap free sequence

BEGIN;
LOCK TABLE product IN ACCESS EXCLUSIVE MODE;
INSERT INTO product SELECT max(id) + 1, ... FROM product;
COMMIT;

pretty nasty way of doing this kind of operation because nobody else can read or write to the table during your operation. ACCESS EXCLUSIVE should be avoided at all costs.

Checking for locks

test=# SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activit

test=# CREATE TABLE t_invoice (id int PRIMARY KEY);
test=# CREATE TABLE t_watermark (id int);
test=# INSERT INTO t_watermark VALUES (0);
test=# WITH x AS (UPDATE t_watermark SET id = id + 1 RETURNING
*)
INSERT INTO t_invoice
SELECT * FROM x RETURNING *;

Here is only a simple row lock on the watermark table, which leads to no reads being blocked in the invoice table. Overall, this way is more scalable.

In general a sequence is the wrong tool to implement gapfree-ness. However there are some business requirements, that really want "almost" gapfree numbers.

For such scenarios a sequence might be a fast and easy way to implement it. One can consider to set the seqeunce to NOCACHE and (only in a RAC environment) to ORDER.

create sequence mySeq start with 1 increment by 1 nocache order;

This will worsen sequence performance a lot! However the number generator ensures that each time it is called, you will get a monoton increasing value.

For save gapless instead of deleting an employee, you would set his status to "INACTIVE" or something similiar.

Recommendation:

If you have such an requirement use an extra field for this number. Do NOT use the primary identifier field!

create table employee (empid number default mySeq.nextval primary key not null
                      ,empno number not null
                      ,first_name varchar2(100)
                      ,last_name varchar2(100)
);

Fill the EMPID column using a normal cached seqeunce mechanism. Fill the EMPNO column using some other mechanism that tries to keep the number gapfree. Advantage: If you renumber the empno at some day, your foreign keys will not break, because they point to the EMPID column.

If you want to have "serial numbering" for the folder columns, then don't use a sequence. You can store the "last used folder value" on a separate table, and use it manually to get a new number each time you need one. Make sure you raise the transaction level to SERIALIZABLE when you perform these updates.