Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT FOR UPDATE in function

SELECT FOR UPDATE in function

From
Oleg Bartunov
Date:
Is't possible to use SELECT FOR UPDATE in functions ?
I have function for 'insert or update' which works ok, but as I have some 
problem with duplicated records I tried as suggested by Tom Lane to use 
SELECT FOR UPDATE instead of just select. Unfortunately it doesn't works:

ERROR:  query didn't return correct # of attributes for *internal*

Here is a function:
CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS '
Declare   keyval      Alias For $1;   cnt         int4;   curtime     datetime;
Begin   curtime := ''now'';
--    Select count into cnt from hits where msg_id = keyval FOR UPDATE;   Select count into cnt from hits where msg_id
=keyval;   if Not Found then       cnt := 1;       -- first_access inserted on default, last_access is NULL
InsertInto hits (msg_id,count) values (keyval, cnt);      else       cnt := cnt + 1;       Update hits set count =
cnt,last_access= curtime where msg_id = keyval;   End If;   return cnt;
 
End;
' LANGUAGE 'plpgsql';

Regards,
    Oleg

PS. 

Just to test:

create table hits (  msg_id int4 not null primary key, count  int4 not null, first_access datetime default now(),
last_accessdatetime   
 
);   

select acc_hits(1);


_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

RE: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function

From
"Hiroshi Inoue"
Date:
>
> Is't possible to use SELECT FOR UPDATE in functions ?
> I have function for 'insert or update' which works ok, but as I have some
> problem with duplicated records I tried as suggested by Tom Lane to use
> SELECT FOR UPDATE instead of just select. Unfortunately it doesn't works:
>
> ERROR:  query didn't return correct # of attributes for *internal*
>

AFAIC,"SELECT FOR UPDATE" always causes above errors in
PL/pgSQL functions.
Could we use PL/pgSQL for update procedures in MVCC ?

ORDER/GROUP BY items that are not in the targetlist also cause
same errors in PL/pgSQL functions.
In both cases,target entries are added which are not wanted in the
final projected tuple(SELECT FOR UPDATE adds "ctid" entry).

In such cases,the # of target entries is different from the # of
final attributes estimated in pl_gram.y and above elog() in
pl_exec.c is called.

Should current check be loosen ?
Or another check is necessary ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] SELECT FOR UPDATE in (PL/pgSQL) function

From
Tom Lane
Date:
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> ERROR:  query didn't return correct # of attributes for *internal*

> AFAIC,"SELECT FOR UPDATE" always causes above errors in
> PL/pgSQL functions.

> ORDER/GROUP BY items that are not in the targetlist also cause
> same errors in PL/pgSQL functions.
> In both cases,target entries are added which are not wanted in the
> final projected tuple(SELECT FOR UPDATE adds "ctid" entry).

It sounds like the code that deals with the resulting tuple is not
smart enough to ignore resjunk attributes (or to call ExecProject
if it needs an actual junk-free tuple).  That's probably an easily
fixed bug, but I'm not familiar with the PL code...
        regards, tom lane