Thread: Restart increment to 0 each year = re-invent the sequences mecanism ?
Hello, I have to design a table wich will store some action reports. Each report have an ID like this 1/2004, 2/2004, ... and each years, they restart to 1 (1/2004, 1/2005, 1/2006,...). So, I was thinking to split that in two fields : one with the increment and one with the year. But I don't know how can I manage the sequences since I have to restart to 0 each year... Do you think I should re-invent the sequences mecanism with a second table and a stored procedure, only to manage theses 'home-made' sequences ? Or should I create some sequences like myseq_2004, myseq_2004, my_seq_2005,... and use a concatenation of the myseq_ string and the current year when calling nextval and curvall ? Or is there another way to do that ? Thanks in advance :-) -- Bruno Baguette - pgsql-ml@baguette.net
Bruno Baguette said: > I have to design a table wich will store some action reports. Each > report have an ID like this 1/2004, 2/2004, ... and each years, they > restart to 1 (1/2004, 1/2005, 1/2006,...). > > So, I was thinking to split that in two fields : one with the increment > and one with the year. But I don't know how can I manage the sequences > since I have to restart to each year... > > Do you think I should re-invent the sequences mecanism with a second > table and a stored procedure, only to manage theses 'home-made' sequences > ? > > Or should I create some sequences like myseq_2004, myseq_2004, > my_seq_2005,... and use a concatenation of the myseq_ string and the > current year when calling nextval and curvall ? Of the options available, I would use the sequence solution; one sequence object for each year. You can do something along the following lines in a stored procedure (which could be used to create the report records): ... -- pass in the year value to the procedure -- or determine year from year part of current date yearNumberTxt = '2004'; -- get the next ID for the year select nextval(yearNumberTxt) into seqNum; -- and generate your report number reportID := seqNum || '/' yearNumberTxt; -- insert into your ReportTable using new report ID ... If you use an extra table and manage the incrementing field yourself (your other suggestion), then you need to be aware of concurrency issues when accessing and updating the counter (for the year). You need to lock the row in a function which generates the next number for the year, and this will block any other processing wanting a number at the same time. Of course if you don't create reports frequently, or concurrently then this isn't an issue. The sequence solution will not block, but could leave you with gaps in your numbering if a record fails to insert for some reason after you issue the nextval function on the sequence object. John Sidney-Woollett
Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
From
Clodoaldo Pinto Neto
Date:
You don't have to mess with sequences. If there are two fields ID and year then the next number is: next_number := ( select ID from table_name where year = year_from_current_date order by ID desc limit 1 ) + 1; Regards, Clodoaldo --- Bruno Baguette <pgsql-ml@baguette.net> escreveu: > Hello, > > I have to design a table wich will store some action reports. Each > report have an ID like this 1/2004, 2/2004, ... and each years, they > restart to 1 (1/2004, 1/2005, 1/2006,...). > > So, I was thinking to split that in two fields : one with the increment > and one with the year. But I don't know how can I manage the sequences > since I have to restart to 0 each year... > > Do you think I should re-invent the sequences mecanism with a second > table and a stored procedure, only to manage theses 'home-made' sequences ? > > Or should I create some sequences like myseq_2004, myseq_2004, > my_seq_2005,... and use a concatenation of the myseq_ string and the > current year when calling nextval and curvall ? > > Or is there another way to do that ? > > Thanks in advance :-) > > -- > Bruno Baguette - pgsql-ml@baguette.net > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly ______________________________________________________________________ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/
Clodoaldo Pinto Neto wrote: > You don't have to mess with sequences. > > If there are two fields ID and year then the next number is: > > next_number := ( select ID from table_name where year = year_from_current_date > order by ID desc limit 1 ) + 1; This could cause ID collision. If two seperate processes call this statement at the same time, they will get the same number, and when they try to insert their record, there will be a collision. One of the processes will error out. Sequences exist to avoid this problem. A sequence _is_ the proper way to do this. > > Regards, > Clodoaldo > > --- Bruno Baguette <pgsql-ml@baguette.net> escreveu: > Hello, > >>I have to design a table wich will store some action reports. Each >>report have an ID like this 1/2004, 2/2004, ... and each years, they >>restart to 1 (1/2004, 1/2005, 1/2006,...). >> >>So, I was thinking to split that in two fields : one with the increment >>and one with the year. But I don't know how can I manage the sequences >>since I have to restart to 0 each year... >> >>Do you think I should re-invent the sequences mecanism with a second >>table and a stored procedure, only to manage theses 'home-made' sequences ? >> >>Or should I create some sequences like myseq_2004, myseq_2004, >>my_seq_2005,... and use a concatenation of the myseq_ string and the >>current year when calling nextval and curvall ? >> >>Or is there another way to do that ? >> >>Thanks in advance :-) -- Bill Moran Potential Technologies http://www.potentialtech.com
> Bruno Baguette said:
>> I have to design a table wich will store some action reports. Each
>> report have an ID like this 1/2004, 2/2004, ... and each years, they
>> restart to 1 (1/2004, 1/2005, 1/2006,...).
>>
>> So, I was thinking to split that in two fields : one with the
>> increment and one with the year. But I don't know how can I manage
>> the sequences since I have to restart to each year...
>>
>> Do you think I should re-invent the sequences mecanism with a second
>> table and a stored procedure, only to manage theses 'home-made'
>> sequences ?
>>
>> Or should I create some sequences like myseq_2004, myseq_2004,
>> my_seq_2005,... and use a concatenation of the myseq_ string and the
>> current year when calling nextval and curvall ?
>
I use the "re-invent" option for compound sequences, as follows:
Each employee submits expense reports. Expense reports for each employee
are numbered as YYYY-NN, where "YYYY" is the current year, and "NN" is an
integer starting at one for each year for each employee.
CREATE TABLE employee
(
employee_pk int4 serial,
...
expense_report_seq int4 DEFAULT 0,
CONSTRAINT employee_pkey PRIMARY KEY (employee_pk),
);
CREATE TABLE expense
(
employee_pk int4 NOT NULL,
expense_report_year int4 NOT NULL,
expense_report_pk int4 NOT NULL,
...
CONSTRAINT expense_report_pkey PRIMARY KEY (employee_pk,
expense_report_year, expense_report_pk),
CONSTRAINT expense_fkey FOREIGN KEY (employee_pk) REFERENCES
paid.employee (employee_pk)
);
CREATE OR REPLACE FUNCTION expense_report_next(int4)
RETURNS int4 AS
'
DECLARE
l_employee_pk ALIAS FOR $1;
BEGIN
UPDATE employee
SET expense_report_seq = (expense_report_seq + 1)
WHERE employee_pk = l_employee_pk;
RETURN (SELECT expense_report_seq FROM employee WHERE employee_pk =
l_employee_pk) ;
END;'
LANGUAGE 'plpgsql' VOLATILE;
/*
NOTE: I'm informed by good sources that the stored procedure defined
above handles concurrency issues correctly because 1) the UPDATE
statment locks the record until a COMMIT is invoked, so the subsequent
SELECT will return YOUR incremented value, not someone else's, and
2) since this procedure is called from within an ON INSERT trigger, it
therefore occurs within a transaction block (which is established
implicitly by the trigger).
*/
CREATE OR REPLACE FUNCTION expense_bit()
RETURNS trigger AS
'
BEGIN
IF NEW.expense_report_year IS NULL THEN
SELECT INTO NEW.expense_report_year date_part(\'year\', current_date);
END IF;
IF NEW.expense_report_pk IS NULL THEN
SELECT INTO NEW.expense_report_pk expense_report_next(new.employee_pk);
END IF;
RETURN new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER expense_bit
BEFORE INSERT
ON expense
FOR EACH ROW
EXECUTE PROCEDURE paid.expense_bit();
-- Resetting the report sub-sequence values for each employee
-- at the start of a new year is left as a student exercise.
--Berend Tober
> You don't have to mess with sequences.
> If there are two fields ID and year then the next number is:
>
> next_number := ( select ID from table_name where year =
> year_from_current_date order by ID desc limit 1 ) + 1;
Gee, I wonder why no one else thought of that... lets see, what is this
thing I've heard about called, er, what was that word... oh yeah, I
remember: "concurrency". Hmm, I wonder...
CREATE TABLE test.test_table
(
column1 int4 DEFAULT 0,
column2 int4 DEFAULT 0
);
INSERT INTO test.test_table VALUES (1,0);
-- Process #1
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
FROM test.test_table
WHERE column1=1
ORDER BY column2 DESC
LIMIT 1);
/* ... */
-- Process #2
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
FROM test.test_table
WHERE column1=1
ORDER BY column2 DESC
LIMIT 1);
-- Note: Process #2 hangs indefinitely at this point.
COMMIT;
SELECT * FROM test.test_table;
/* ... */
-- Process #1
COMMIT;
SELECT * FROM test.test_table;
-- Result: (1,1)
/* ... */
-- Process #2
-- Result: (1,1)
-- "D-"
--Berend Tober
btober@computer.org said: > CREATE OR REPLACE FUNCTION expense_report_next(int4) > RETURNS int4 AS > ' > DECLARE > l_employee_pk ALIAS FOR $1; > BEGIN > UPDATE employee > SET expense_report_seq = (expense_report_seq + 1) > WHERE employee_pk = l_employee_pk; > RETURN (SELECT expense_report_seq FROM employee WHERE employee_pk = > l_employee_pk) ; > END;' > LANGUAGE 'plpgsql' VOLATILE; > > /* > NOTE: I'm informed by good sources that the stored procedure defined > above handles concurrency issues correctly because 1) the UPDATE > statment locks the record until a COMMIT is invoked, so the subsequent > SELECT will return YOUR incremented value, not someone else's, and > 2) since this procedure is called from within an ON INSERT trigger, it > therefore occurs within a transaction block (which is established > implicitly by the trigger). > */ Actually, I'm not sure that this procedure is safe. It might be possible to get a race condition where the RETURN (SELECT .... ) retrieves the value generated by another invocation of the procedure by another process for the same employee - but this depends on when the row is unlocked. Perhaps the following would avoid the problem (if there is such a problem) -- get current value, and lock row SELECT expense_report_seq INTO vSeqNum FROM employee WHERE employee_pk = l_employee_pk FOR UPDATE; -- increment the counter, and release the lock? UPDATE employee SET expense_report_seq = (expense_report_seq + 1) WHERE employee_pk = l_employee_pk; -- return the correct value return (1 + vSeqNum); In the above example, generation of the correct sequence and the updating of the record is effectively atomic by virtue of the row level lock (enforced by the "FOR UPDATE" statement). Whereas in your version it may be possible to get a different value from the one you just updated - again I'm not sure about this. Perhaps someone who is sure can illuminate this for me. With had nested transactions, then addition of a commit statement in the procedure would make the release of the lock explicit. Thanks John Sidney-Woollett
Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
From
Bruno Wolff III
Date:
On Mon, Apr 26, 2004 at 09:38:41 -0400, btober@computer.org wrote: > > You don't have to mess with sequences. > > If there are two fields ID and year then the next number is: > > > > next_number := ( select ID from table_name where year = > > year_from_current_date order by ID desc limit 1 ) + 1; > > Gee, I wonder why no one else thought of that... lets see, what is this > thing I've heard about called, er, what was that word... oh yeah, I > remember: "concurrency". Hmm, I wonder... Yes, you need to do a lock table if you do things that way. But that still is probably what he wants to do. Sequences can leave gaps, which he probably doesn't want. If that is true then things need to be serialized somehow. I doubt that these reports are being generated at such a rate that he needs to worry about contention problems with locking the table.
btober@computer.org said: > How "not sure" are you? Not "not sure" at all. > But I'd welcome a correction to my > thinking from anyone that really-truely-for-sure (like in source code > level intimacy) knows. Me too! Because while I was blissfully sure in my ignorance before, now I'm really not sure! :) John Sidney-Woollett
> btober@computer.org said: >> CREATE OR REPLACE FUNCTION expense_report_next(int4) >> RETURNS int4 AS >> ' >> DECLARE >> l_employee_pk ALIAS FOR $1; >> BEGIN >> UPDATE employee >> SET expense_report_seq = (expense_report_seq + 1) >> WHERE employee_pk = l_employee_pk; >> RETURN (SELECT expense_report_seq FROM employee WHERE employee_pk = >> l_employee_pk) ; >> END;' >> LANGUAGE 'plpgsql' VOLATILE; >> >> /* >> NOTE: I'm informed by good sources that the stored procedure defined >> above handles concurrency issues correctly because 1) the UPDATE >> statment locks the record until a COMMIT is invoked, so the >> subsequent SELECT will return YOUR incremented value, not someone >> else's, and 2) since this procedure is called from within an ON >> INSERT trigger, it therefore occurs within a transaction block (which >> is established implicitly by the trigger). >> */ > > Actually, I'm not sure that this procedure is safe. How "not sure" are you? > It might be > possible to get a race condition where the RETURN (SELECT .... ) > retrieves the value generated by another invocation of the procedure by > another process for the same employee - but this depends on when the > row is unlocked. It isn't. At least in my testing anyway, where I have stepped through the the sequence of events by using two separate processes and observed the fact the the second process is blocked on the UPDATE until the first completes. What was explained to me by other guru's in this forum is that the row is unlocked at the end of the trigger (which is what invokes this procedure), so a second invocation of the trigger for the same employee waits until the first is complete. It may depend on the locking level that is set for an particular database, but I've seen it working, albeit in a not-to-heavily used database. > Perhaps the following would avoid the problem (if there is such a > problem) > > -- get current value, and lock row > SELECT expense_report_seq INTO vSeqNum > FROM employee > WHERE employee_pk = l_employee_pk > FOR UPDATE; > > -- increment the counter, and release the lock? > UPDATE employee > SET expense_report_seq = (expense_report_seq + 1) > WHERE employee_pk = l_employee_pk; > > -- return the correct value > return (1 + vSeqNum); That works too, but my method accomplishes the same thing without having to declare the local variable vSeqNum. > In the above example, generation of the correct sequence and the > updating of the record is effectively atomic by virtue of the row level > lock (enforced by the "FOR UPDATE" statement). Whereas in your version > it may be possible to get a different value from the one you just > updated - again I'm not sure about this. Perhaps someone who is sure > can illuminate this for me. Yes. No it is not -- I'm pretty sure. But I'd welcome a correction to my thinking from anyone that really-truely-for-sure (like in source code level intimacy) knows. > > With had nested transactions, then addition of a commit statement in > the procedure would make the release of the lock explicit. Unnecessary, in this case. --Berend Tober