Re: Restart increment to each year = re-invent the

btober(at)computer(dot)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