Lock row in function POSTGRESQL

Question

I'm trying the simulating the "nextval" function, i need the next value generated based on specific id. In some cases, the function is returned the same value.

CREATE OR REPLACE FUNCTION public.nextvalue(character,integer) 
RETURNS integer AS
$BODY$
    DECLARE
    p_id ALIAS FOR $1;
    p_numero integer;
    BEGIN

        p_numero = (SELECT numero FROM "TnextValue" WHERE id = p_id FOR UPDATE);

        IF p_numero is null THEN
            INSERT INTO "TnextValue" (numero,id) VALUES (1,p_id);
            p_numero = 1;
        END IF;

        UPDATE "TnextValue" SET numero = p_numero + 1 where id = p_id;

        RETURN p_numero;

    END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

I tried add the statement FOR UPDATE, but the problem persist. I thinking the add one line above the statament (SELECT) the line

LOCK TABLE "TnextValue" IN ROW EXCLUSIVE MODE;

But i think this line block the table for others id obtain the next value in same time.

Thanks!

This should work (although it is not pretty). Can you demonstrate a case where it doesn't work as expected? — Laurenz Albe
– Laurenz Albe, Commented Aug 3, 2018 at 14:52

Jasen · Accepted Answer · 2018-08-04 10:34:21Z

1

    IF p_numero is null THEN
        INSERT INTO "TnextValue" (numero,id) VALUES (1,p_id);
        p_numero = 1;
    END IF;

not going to work there's no locking on that row if two callers want the same new sequence one of them will get an error.

    IF p_numero is null THEN
        BEGIN
            INSERT INTO "TnextValue" (numero,id) VALUES (1,p_id);
            p_numero = 1;
        EXCEPTION WHEN UNIQUE_VIOLATION THEN
            RETURN public.nextvalue($1,$2);
        END;
    END IF;

answered Aug 4, 2018 at 10:34

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Lock row in function POSTGRESQL

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