Re: Does the block of code in a stored procedure execute as a transaction?

Thank you very much.

Further clarification on two points, though, please.

1) When I add the FOR UPDATE clause to the SELECT statement, do also have
to add a COMMIT statement somewhere? As in

BEGIN
  SELECT INTO l_expense_report_seq expense_report_seq+1
    FROM employee
    WHERE employee_pk = l_employee_pk
      FOR UPDATE; <- proposed modification

  UPDATE employee
    SET expense_report_seq = l_expense_report_seq
    WHERE employee_pk = l_employee_pk;
  COMMIT; <- is this modification needed also?
RETURN l_expense_report_seq;

or is commit implicit by the completion of the function code?

2) I don't see how doing UPDATE first helps. What if the other user,
calling the same function, happens to have their UPDATE statement execute
between my UPDATE and SELECT statements? Then we again both get the same
new "sequence" value, don't we?

>
> Your BEGIN/END block does not define a transaction, *however* there is
> a  (at, least, implicit) transaction surrounding the actual insert
> statement, and your trigger execution - they both either succeseed or
> fail together.
> *But* having that transaction does *not* make your code 'multiuser
> safe'  - it is still possible that another user accesses that
> 'sequence' at the  same time, and you both get the same number out of
> it. You do need to  add 'FOR UPDATE' to your select statement to avoid
> that (or you can just  do it the other way around - first update, then
> select)
>
>
> btober@seaworthysys.com wrote:
>
>>I need to "manually" keep a sequence for each row of the employee
>> table, i.e., I don't want to use postgres's built-in sequences for
>> this
>>application because the sequence numbers are used to generate expense
>> report numbers on a "per-employee" basis. That is, each employee has a
>> separate sequence counter, starting at one, to number their expense
>> reports. Since employees will come and go, I don't want to keep having
>> to create and delete postgres sequence objects as employees come and
>> go.
>>
>>Instead, I have a column of the employee table store the "last value"
>> of the corresponding expense report sequence counter, and in an ON
>> INSERT trigger to the expense report table, I call the following
>> function to get and increment the new sequence value:
>>
>>CREATE OR REPLACE FUNCTION paid.expense_report_next(int4) RETURNS
>> integer AS '
>>DECLARE
>>  l_employee_pk ALIAS FOR $1;
>>  l_expense_report_seq INTEGER;
>>BEGIN
>>  SELECT INTO l_expense_report_seq expense_report_seq+1
>>    FROM employee
>>    WHERE employee_pk = l_employee_pk;
>>
>>  UPDATE employee
>>    SET expense_report_seq = l_expense_report_seq
>>    WHERE employee_pk = l_employee_pk;
>>RETURN l_expense_report_seq;
>>END;'  LANGUAGE 'plpgsql' VOLATILE;
>>
>>
>>What I need to know is whether or not this is multi-user safe, i.e.,
>> will the block of code in the procedure execute as a transaction so
>> that if more than one clerk creates an expense report for the same
>> employee simultaneously is it possible or impossible that value of the
>>employee.expense_report_seq gets updated by the second clerk between
>> the SELECT and UPDATE statements invoked by the first clerk?
>>
>>And as a follow-up, should I add the FOR UPDATE clause to the SELECT
>> statement?
>>