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

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)

I hope, it helps...

Dima

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