Postgresql function which locks table, updates value and returns row

Question

For my Hi/Lo implementation, I need a function which will acquire exclusive table lock, update value and select one row. I came up with the following code:

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS setof record AS
$$ 
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    OPEN ref FOR SELECT hi as "Hi", lo as "Lo", "table" as "Table" FROM hilo WHERE "table" = $1;
    UPDATE hilo SET hi = hi + 1 WHERE "table" = $1
    RETURN ref;

$$ LANGUAGE plpgsql;

However, when invoking the function, it returns not a row, but one column with a content similar to "unnamed portal 3". I guess I should iterate over returned ref (but how)?

Another approach that I could use is to use UPDATE RETURNING statement, but I'm not sure if race conditions can occur in this case. Any help would be appreciated. Thanks

Why are you locking the entire table if you just want to lock a single row? — user330315
– user330315, Commented Aug 29, 2016 at 17:33
I guess there's no other way to lock readers as well. Maybe I'm wrong and misunderstood something, would be greatful for some explanation if so :) — Davita
– Davita, Commented Aug 29, 2016 at 21:31

Boris Schegolev · Accepted Answer · 2016-08-29 15:23:16Z

2

This seems far more simple to me:

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS setof record AS
$$ 
BEGIN
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    RETURN QUERY UPDATE hilo SET hi = hi + 1 WHERE "table" = $1
        RETURNING hi as "Hi", lo as "Lo", "table" as "Table";
END;
$$ LANGUAGE plpgsql;

I also usually define RETURNS TABLE (col1 varchar, col2 varchar...) instead of RETURNS setof record.

answered Aug 29, 2016 at 15:23

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

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

2 Comments

Patrick Over a year ago

This returns the updated value of "hi", unlike what the OP was trying to get.

Boris Schegolev Over a year ago

That is true, I didn't think of that. In case of +=1 it should not be much of an issue, though.

Patrick · Accepted Answer · 2016-08-29 15:31:47Z

1

You are using a cursor variable (which you did not declare, and missing some other PL/pgSQL required elements too) and you return that. Cursor references are indeed called "portals" so that explains your output. A better approach is this:

CREATE OR REPLACE FUNCTION bb_next_hi(tbl varchar(35))  
RETURNS TABLE ("Hi" int, "Lo" int, "Table" varchar(35)) AS $$
BEGIN
    LOCK TABLE hilo IN ACCESS EXCLUSIVE MODE;
    RETURN QUERY SELECT hi, lo, $1 FROM hilo WHERE "table" = $1;
    UPDATE hilo SET hi = hi + 1 WHERE "table" = $1;
    RETURN;
END;
$$ LANGUAGE plpgsql STRICT;

But do you really need an exclusive lock on the table? There are much less invasive approaches to getting a consistent table snapshot.

answered Aug 29, 2016 at 15:31

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

Postgresql function which locks table, updates value and returns row

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related