Re: SQL functions not locking properly?

Forest Wilkinson wrote:
> 
> I'm having a problem with functions written in SQL.  Specifically, they
> don't seem to be adhering to Postgres locking rules.  For the record, I'm
> using postgres 7.0.2, installed from RPMs, on Red Hat 6.2.
> 
> Here's what I'm seeing:
> (psql input represented by '<<'; output represented by '>>'.)
> 
> session1<< create table idseq
> session1<<   (
> session1<<   name varchar(32) not null,
> session1<<   id int8 not null default 0
> session1<<   );
> session1>> CREATE
> 
> session1<< insert into idseq values ('myid');
> session1>> INSERT 20700 1
> 
> Each row in the table is supposed to represent a named numeric sequence,
> much like the sequences built into postgres.  (Mine use an int8 though,
> so their values can be much higher.)
> 
> session1<< create function nextid( varchar(32)) returns int8 as '
> session1<<   select * from idseq where name = $1::text for update;
> session1<<   update idseq set id = id + 1 where name = $1::text;
> session1<<   select id from idseq where name = $1::text;

Queries in a function are executed under the 
snapshot of the top level query.
So SELECT could never see changes made by
other backends once the top level query
started. If you change the definition of above
function to return setof int8,you may see 2
rows returned. 
Maybe you could add 'for update' clause to your
above query.

> session1<<   ' language 'sql';
> session1>> CREATE
>

Regards.

Hiroshi Inoue