increment number table postgresql thread safe

Question

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?

Laurenz Albe · Accepted Answer · 2018-10-12 03:59:16Z

3

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.

answered Oct 12, 2018 at 3:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

increment number table postgresql thread safe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related