Re: Restart increment to each year = re-invent the - Mailing list pgsql-general

> btober@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