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

[Bruno Baguette]

Hello,

I have to design a table wich will store some action reports. Each
report have an ID like this 1/2004, 2/2004, ... and each years, they
restart to 1 (1/2004, 1/2005, 1/2006,...).

So, I was thinking to split that in two fields : one with the increment
and one with the year. But I don't know how can I manage the sequences
since I have to restart to 0 each year...

Do you think I should re-invent the sequences mecanism with a second
table and a stored procedure, only to manage theses 'home-made' sequences ?

Or should I create some sequences like myseq_2004, myseq_2004,
my_seq_2005,... and use a concatenation of the myseq_ string and the
current year when calling nextval and curvall ?

Or is there another way to do that ?

Thanks in advance :-)

--
Bruno Baguette - pg******@baguet te.net

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

[btober]

> You don't have to mess with sequences.

If there are two fields ID and year then the next number is:

next_number := ( select ID from table_name where year =
year_from_curre nt_date order by ID desc limit 1 ) + 1;

Gee, I wonder why no one else thought of that... lets see, what is this
thing I've heard about called, er, what was that word... oh yeah, I
remember: "concurrenc y". Hmm, I wonder...

CREATE TABLE test.test_table
(
column1 int4 DEFAULT 0,
column2 int4 DEFAULT 0
);

INSERT INTO test.test_table VALUES (1,0);

-- Process #1
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
FROM test.test_table
WHERE column1=1
ORDER BY column2 DESC
LIMIT 1);

/* ... */

-- Process #2
BEGIN;
UPDATE test.test_table SET column2 = 1+(SELECT column2
FROM test.test_table
WHERE column1=1
ORDER BY column2 DESC
LIMIT 1);

-- Note: Process #2 hangs indefinitely at this point.

COMMIT;
SELECT * FROM test.test_table ;

/* ... */

-- Process #1
COMMIT;
SELECT * FROM test.test_table ;

-- Result: (1,1)
/* ... */

-- Process #2
-- Result: (1,1)

-- "D-"

--Berend Tober


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

[John Sidney-Woollett]

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

[John Sidney-Woollett]

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

[John Sidney-Woollett]

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

[John Sidney-Woollett]

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

[Bruno Wolff III]

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

Gee, I wonder why no one else thought of that... lets see, what is this
thing I've heard about called, er, what was that word... oh yeah, I
remember: "concurrenc y". Hmm, I wonder...

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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Bruno Wolff III]

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

Gee, I wonder why no one else thought of that... lets see, what is this
thing I've heard about called, er, what was that word... oh yeah, I
remember: "concurrenc y". Hmm, I wonder...

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 YourEmailAddres sHere" to ma*******@postg resql.org)

[btober]

> 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

[btober]

> 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