Re: Sequences without blank holes

> On Thu, Nov 06, 2003 at 05:01:54 -0300,
> MaRcElO PeReIrA <gandalf_mp(at)yahoo(dot)com(dot)br> wrote:
>>
>> $ select * from products;
>> prod_id | description
>> --------+---------------------
>> 1 | S470DXBLM
>> 12 | S470DXABM
>> 33 | RG250DX
>> --------+---------------------
>> (3 rows)
>>
>> and it is ok to me, but not to the users.
>

Instead of using the MAX aggregate function, or the SELECT with LIMIT
clause, another approach is to use a stored procedure to increment a
sequence counter column you keep in a separate table. I have a database
that has a "master-detail" type relationship between a supplier table and
an employee table (zero or more employees work for one supplier). And I
keep a separately-incremented employee primary key sequence for the
employees of each supplier. To do that I define a column in the supplier
table holding the value of the most-recently issued employee key value,
and increment that inside a stored procedure using a trigger when a new
employee is inserted for a given supplier.

The supplier table is defined in part as

CREATE TABLE supplier
(
supplier_pk int4 NOT NULL,
...
employee_seq int4 NOT NULL DEFAULT 0,
CONSTRAINT supplier_pkey PRIMARY KEY (supplier_pk)
);

The employee table is defined in part as

CREATE TABLE paid.employee
(
supplier_pk int4 NOT NULL,
employee_pk int4 NOT NULL,
...
CONSTRAINT employee_pkey PRIMARY KEY (supplier_pk, employee_pk),
);

The sequencing procedure looks like:

CREATE OR REPLACE FUNCTION employee_seq_next(int4)
RETURNS int4 AS
'
DECLARE
l_supplier_pk ALIAS FOR $1;
BEGIN
UPDATE supplier
SET
employee_seq = (employee_seq + 1)
WHERE (supplier_pk = l_supplier_pk);

RETURN (SELECT employee_seq FROM supplier
WHERE (supplier_pk = l_supplier_pk));
END;'
LANGUAGE 'plpgsql' VOLATILE;

and the trigger procedure which calls the sequencing function looks like

CREATE OR REPLACE FUNCTION employee_bit()
RETURNS trigger AS
'
BEGIN
if new.employee_pk IS NULL THEN
SELECT INTO NEW.employee_pk employee_seq_next(new.supplier_pk);
END IF;
RETURN new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

I'm told that doing the UPDATE first inside the trigger creates a lock on
the supplier table until the trigger transaction completes, so (I would
suppose, but I'm not expert enough to assert this for sure that) this
would assure you of getting one sequence increment at a time.

This seems like a workable paradigm which I used in other cases as well.

Still end up with holes in the sequence, though, if an employee row is
deleted, for example. Using the MAX function or LIMIT clauses would
protect against that in the cases where the most-recently-added employee
row were deleted.

Something else you can do, is define all your foreign key constraints
with the ON UPDATE CASCADE clause, so that you can manually change your
primary key values to fill in the holes.

~Berend Tober