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 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
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
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
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 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 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
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
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
> 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
> 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 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 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 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 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
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)
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)
> 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
> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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 ??
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |