Restart increment to 0 each year = re-invent the sequences mecanism?

[Bruno Baguette]

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 - pg******@baguet te.net

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[John Sidney-Woollett]

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(yearNum berTxt) 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[John Sidney-Woollett]

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(yearNum berTxt) 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Clodoaldo Pinto Neto]

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_curre nt_date
order by ID desc limit 1 ) + 1;

Regards,
Clodoaldo

--- Bruno Baguette <pg******@bague tte.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 - pg******@baguet te.net

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Clodoaldo Pinto Neto]

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_curre nt_date
order by ID desc limit 1 ) + 1;

Regards,
Clodoaldo

--- Bruno Baguette <pg******@bague tte.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 - pg******@baguet te.net

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[btober]

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_rep ort_year IS NULL THEN
SELECT INTO NEW.expense_rep ort_year date_part(\'yea r\', current_date);
END IF;
IF NEW.expense_rep ort_pk IS NULL THEN
SELECT INTO NEW.expense_rep ort_pk expense_report_ next(new.employ ee_pk);
END IF;
RETURN new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER expense_bit
BEFORE INSERT
ON expense
FOR EACH ROW
EXECUTE PROCEDURE paid.expense_bi t();

-- Resetting the report sub-sequence values for each employee
-- at the start of a new year is left as a student exercise.

--Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[btober]

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_rep ort_year IS NULL THEN
SELECT INTO NEW.expense_rep ort_year date_part(\'yea r\', current_date);
END IF;
IF NEW.expense_rep ort_pk IS NULL THEN
SELECT INTO NEW.expense_rep ort_pk expense_report_ next(new.employ ee_pk);
END IF;
RETURN new;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER expense_bit
BEFORE INSERT
ON expense
FOR EACH ROW
EXECUTE PROCEDURE paid.expense_bi t();

-- Resetting the report sub-sequence values for each employee
-- at the start of a new year is left as a student exercise.

--Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Bill Moran]

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_curre nt_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 <pg******@bague tte.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bill Moran]

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_curre nt_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 <pg******@bague tte.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[btober]

> 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_curre nt_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: "concurrenc y". 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster