Thread: pgplsql SELECT INTO ... FOR UPDATE (transaction/locking question)

I'm trying to write a stored procedure in plpgsql that selects a row
and possibly increments one of its fields. I thought I would do SELECT
INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
plpgsql doesn't like the FOR UPDATE in a stored procedure. Does
plpgsql automatically lock any rows I read until the stored procedure
exits? I'm just not sure how to get the functionality I'm looking for
and not have to concern myself with concurrency.


Example:

<fixed><fontfamily><param>Courier New</param>create table t_test (x
int, y int);

create or replace function f_test(int) returns void as '

declare r record;

begin

  select into r *, oid from t_test -- FOR UPDATE

    where x = $1;

  if found then

    update t_test set y=y+1 where oid = r.oid;

  end if;

  return;

end' language plpgsql;

insert into t_test values (1,1);

select f_test(1);

</fontfamily></fixed>
I'm trying to write a stored procedure in plpgsql that selects a row
and possibly increments one of its fields. I thought I would do SELECT
INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
automatically lock any rows I read until the stored procedure exits?
I'm just not sure how to get the functionality I'm looking for and not
have to concern myself with concurrency.

Example:
create table t_test (x int, y int);
create or replace function f_test(int) returns void as '
declare r record;
begin
   select into r *, oid from t_test -- FOR UPDATE
     where x = $1;
   if found then
     update t_test set y=y+1 where oid = r.oid;
   end if;
   return;
end' language plpgsql;
insert into t_test values (1,1);
select f_test(1);

I've got a few plpgsql stored functions (in 7.4.x) that use the

select x into y from table where condition for update

syntax without any problem.

Maybe there's something else going on?

John Sidney-Woollett

Eric Brown wrote:

> I'm trying to write a stored procedure in plpgsql that selects a row and
> possibly increments one of its fields. I thought I would do SELECT INTO
> my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql
> doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
> automatically lock any rows I read until the stored procedure exits? I'm
> just not sure how to get the functionality I'm looking for and not have
> to concern myself with concurrency.
>
> Example:
> create table t_test (x int, y int);
> create or replace function f_test(int) returns void as '
> declare r record;
> begin
>   select into r *, oid from t_test -- FOR UPDATE
>     where x = $1;
>   if found then
>     update t_test set y=y+1 where oid = r.oid;
>   end if;
>   return;
> end' language plpgsql;
> insert into t_test values (1,1);
> select f_test(1);
>

On Wed, 2004-12-15 at 01:38 -0800, Eric Brown wrote:
>
>
> ______________________________________________________________________
>
> I'm trying to write a stored procedure in plpgsql that selects a row
> and possibly increments one of its fields. I thought I would do SELECT
> INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
> plpgsql doesn't like the FOR UPDATE in a stored procedure.

the docs seem to imply that the FOR UPDATE clause should follow the
WHERE clause.
and indeed, it would seem that your should follow the '*'
try
SELECT * INTO my_record WHERE ... FOR UPDATE

gnari

> I'm trying to write a stored procedure in plpgsql that selects a row
> and possibly increments one of its fields. I thought I would do SELECT
> INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
> plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
> automatically lock any rows I read until the stored procedure exits?
> I'm just not sure how to get the functionality I'm looking for and not
> have to concern myself with concurrency.
>
> Example:
> create table t_test (x int, y int);
> create or replace function f_test(int) returns void as '
> declare r record;
> begin
>    select into r *, oid from t_test -- FOR UPDATE
>      where x = $1;
>    if found then
>      update t_test set y=y+1 where oid = r.oid;
>    end if;
>    return;
> end' language plpgsql;
> insert into t_test values (1,1);
> select f_test(1);
>

Would it be better to just write

update t_test set y=y+1 where x = $1;