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

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?

~Berend Tober

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

> NO,
> Functions and triggers doesn't start any transaction,
> it must be in a upper-level transaction, this since Postgresql
> doesn'n support nested transactions.
>
> therefore it is unsafe in multi-user enviroments :(

Thank you. But so how can I do this. Can I add a BEGIN TRANSACTION ...
COMMIT block around the call to my sequence-updater procedure inside the
trigger which invokes it?

~Berend Tober

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?
>>

btober@seaworthysys.com wrote:

>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?
>
*no* Don't even think about it.:-)
You are running this from inside a trigger, right?
So the user executes a statement like

insert into foo values (bar);

If the user did begin before that, you are already in transaction, and
it will be committed when the user commits explicitly. If there was no
explicit begin, there is still an implicit transaction around your
insert statement (imagine that there is begin; before the insert, and
commit immediately after it).
So, either way, your trigger function is running inside a transaction,
that will be committed at the right time. You don't want to screw that
up by committing too early.

>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?
>
When you UPDATE a row, it gets locked (just like when you do
select...for update), and stays locked until the end of the transaction.
So, once you UPDATE it, nobody else can until your transaction is finished.

I hope, it helps...

Dima

> btober@seaworthysys.com wrote:
>
>>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?
>>
> *no* Don't even think about it.:-)
> You are running this from inside a trigger, right?

So, to re-iterate then, will simply adding the FOR UPDATE clause to my
original select statement make the procedure as originally written with
the SELECT first multi-user safe?

>
>>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?
>>
> When you UPDATE a row, it gets locked (just like when you do
> select...for update), and stays locked until the end of the
> transaction.  So, once you UPDATE it, nobody else can until your
> transaction is finished.
>
> I hope, it helps...

Now I see. That helps a lot. I was not aware of that behavior. Thank you.

~Berend Tober