Re: Best approach for a "gap-less" sequence - Mailing list pgsql-general

elein wrote:

> On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
>
>>On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
>>
>>>On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
>>>>Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
>>>>locking the table?
>
> If this is true the solution for a transactional, gapless sequence ...
> I may publish the gapless sequence technique on general bits if there is no
> discrepancy in the understanding of the status of the second transaction's
> row value (updated).


/*
Hi Elein, I'm an avid reader of your General Bits column.

One of my favorite sayings is "nothing beats empirical evidence", so
regardless of what people interpret the documentation to say, here is a
simplified description of an actual working implementation of how it is
done:

The background:

A business requirement is to generate table rows that have uniformly
increasing, whole number sequences, i.e., the "gap-less" sequence. In
this particular case the situation requires multiple such sequences
within the same table -- for each employee, there is a
uniformly-sequenced set of expense reports. I use the term "compound
sequence" for this situation because the expense reports are sequenced
independently on a per-employee basis.

Specifically, I have employee data in
*/

CREATE SCHEMA test;
SET search_path = test, public, pg_catalog;

CREATE TABLE employee
(
    employee_pk SERIAL, -- Identifies the employee.
    /*
    ...lots of non-relevent columns omitted ...
    */
    expense_report_seq int4 DEFAULT 0, -- Compound sequence control.
       CONSTRAINT employee_pkey PRIMARY KEY (employee_pk)
);

/*
The expense_report_seq column stores the most-recently-used expense
report number for each employee, i.e., it is the control value for the
compound sequences that appear in
*/

CREATE TABLE expense
(
    employee_pk int4 NOT NULL,
    expense_report_pk int4 NOT NULL,
    /*
    ...lots of non-relevent columns omitted ...
    */
   CONSTRAINT expense_report_pkey PRIMARY KEY (employee_pk,
expense_report_pk),
    CONSTRAINT expense_fkey FOREIGN KEY (employee_pk)
        REFERENCES employee (employee_pk)
);


/*
A before-insert trigger handles the compound sequence:
*/

CREATE OR REPLACE FUNCTION expense_bit()
   RETURNS "trigger" AS
'
BEGIN
    UPDATE employee
        SET expense_report_seq = (expense_report_seq + 1)
        WHERE employee_pk = NEW.employee_pk;
    SELECT INTO NEW.expense_report_pk expense_report_seq
        FROM employee WHERE employee_pk = NEW.employee_pk;
   RETURN new;
END;
'
   LANGUAGE 'plpgsql' VOLATILE;

/*
Other triggers handle allowed deletion and correction of some expense
report data under certain circumstances.
*/

CREATE TRIGGER expense_bit
   BEFORE INSERT
   ON expense
   FOR EACH ROW
   EXECUTE PROCEDURE expense_bit();


/*
Turns out the SELECT ... FOR UPDATE syntax is not even required because
code inside functions, particularly trigger functions as illustrated
here, is treated as a transaction and the UPDATE statement locks the
effected row until the trigger completes.
*/

-- Then test it:

INSERT INTO employee DEFAULT VALUES;
INSERT INTO employee DEFAULT VALUES;

-- In two separate sessions, run many competing inserts:

SET search_path = test, public, pg_catalog;
INSERT INTO expense VALUES (1);
INSERT INTO expense VALUES (1);
/*
    ...
*/
INSERT INTO expense VALUES (1);


INSERT INTO expense VALUES (2);
INSERT INTO expense VALUES (2);
/*
    ...
*/
INSERT INTO expense VALUES (2);

-- And check your results:
SELECT * FROM expense order by 1,2;
/*
Regards,
Berend Tober
*/