By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,439 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,439 IT Pros & Developers. It's quick & easy.

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

P: n/a
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******@baguette.net

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

Nov 23 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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

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

Nov 23 '05 #2

P: n/a
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

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

Nov 23 '05 #3

P: n/a
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 <pg******@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 - pg******@baguette.net

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

---------------------------(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

Nov 23 '05 #4

P: n/a
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 <pg******@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 - pg******@baguette.net

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

---------------------------(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

Nov 23 '05 #5

P: n/a
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


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

Nov 23 '05 #6

P: n/a
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


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

Nov 23 '05 #7

P: n/a
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 <pg******@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

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

Nov 23 '05 #8

P: n/a
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 <pg******@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

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

Nov 23 '05 #9

P: n/a
> 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


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

Nov 23 '05 #10

P: n/a
> 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


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

Nov 23 '05 #11

P: n/a
bt****@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

---------------------------(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

Nov 23 '05 #12

P: n/a
bt****@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

---------------------------(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

Nov 23 '05 #13

P: n/a
bt****@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

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

Nov 23 '05 #14

P: n/a
bt****@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

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

Nov 23 '05 #15

P: n/a
On Mon, Apr 26, 2004 at 09:38:41 -0400,
bt****@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #16

P: n/a
On Mon, Apr 26, 2004 at 09:38:41 -0400,
bt****@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #17

P: n/a
> bt****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #18

P: n/a
> bt****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.