473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 - 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
18 3928
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
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
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
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
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
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
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
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
> 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
> 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
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
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
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
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
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
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
> 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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: MikeT | last post by:
I have a page that produces little thumbnails of the 3D models it finds in a specified directory (and iterates down through any sub directories). It basically scans each directory for 3D Studio...
34
by: VK | last post by:
And this script supposes to work everywhere. It allows to get the name of any function from within the function itself. <html> <head> <title>Function name</title> <meta...
12
by: Herby | last post by:
Hi, I am currently reviewing what is required to convert a current MFC, COM server application into a .NET application. The objective is to compile ultimately to clr/safe. So we are seeking a...
7
by: David Freeman | last post by:
Hi There! I'm trying to create a User Registration page in ASP.NET and wondering what is the best way to get the list of up-to-date Countries and Cities? Are there any Web Services on the web...
8
by: kenneth fleckenstein nielsen | last post by:
hi guru's I want to restart my webservice when ever it throws an exception that isn't cought. can i do that by web.config or iss or how ??
22
by: Joe Smith | last post by:
It is nothing short of embarrassing to feel the need to ask for help on this. I can't see how I would make the main control for this. What I want is a for loop and a test condition. And while I...
10
by: el__marcin | last post by:
hi I am using MS office Access 2003. ANybody knows how to convert more than 300-400K rows from text into e.g. number ? I tried to do so a few times and access told me there aint enough memory or...
1
by: lisa.engblom | last post by:
Hi, I am using matplotlib with python to generate a bunch of charts. My code works fine for a single iteration, which creates and saves 4 different charts. The trouble is that when I try to...
7
by: zb | last post by:
I am not convinced and need your opinion. My understanding is that if bin directory's content is changed then the ASP.Net application recycles (Applicatin is restarted). Is it also true for the...
10
by: =?Utf-8?B?U3RlZmFuIEJhcmxvdw==?= | last post by:
This has been working perfectly for months. Since we switched from ASP.NET 1.1 to 2.0, we have constant and sporadic issues with updating our applications. Touching the web.config works about...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.