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
*/
