CODEWITHSUNDEEP
multithreadingpostgresqlfunction
이재국
이재국

Reputation: 5

increment number table postgresql thread safe

I'm creating a thread-safe function. I want to have the number incremented by 'p_sitename' and 'p_sourcename'.

CREATE OR REPLACE FUNCTION public.fn_ppp(
p_sitename character varying,
p_sourcename character varying)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
AS $BODY$

declare
    res integer ;
begin
lock table std_seq in access exclusive mode;

update 
    std_seq
set 
    post_id = (
    select 
        post_id + 1 into res
    from 
        std_seq
    where 
        sitename   = p_sitename   and 
        sourcename = p_sourcename
    limit 1
    )
where 
    sitename   = p_sitename   and
    sourcename = p_sourcename ;
return res;
end; 

$BODY$;

The error message is

ERROR:  INTO used with a command that cannot return data
CONTEXT:  PL/pgSQL function fn_ppp(character varying,character varying) line 
8 at SQL statement
SQL state: 42601

Why not?

Upvotes: 0

Views: 1187

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

SELECT ... INTO cannot be in a subquery, only on the top level.

But you can easily do what you want with UPDATE ... RETURNING:

UPDATE std_seq
SET post_id = post_id + 1
WHERE sitename = p_sitename
  AND sourcename = p_sourcename
RETURNING post_id INTO res;

Since everything happens in one statement, there is no need to explicitly lock the table at all. Every concurrent transaction that calls the same function with the same transaction will be blocked until your transaction is done, and no duplicates can occur.

The table should have a primary key constraint on (sitename, sourcename, post_id). That will prevent duplicates, but may also speed up the UPDATE.

Upvotes: 3

Related Questions