CODEWITHSUNDEEP
postgresqltransactions
Thiago Ribeiro
Thiago Ribeiro

Reputation: 83

Lock row in function POSTGRESQL

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!

Upvotes: 1

Views: 112

Answers (1)

Jasen
Jasen

Reputation: 12442

    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;

Upvotes: 1

Related Questions