SELECT INTO with SELECT FOR UPDATE in PostgreSQL
Suppose I have a relationship message
where I save messages created with this command:
CREATE TABLE message
(
id serial primary key,
foo1 integer,
foo2 integer,
foo3 text
)
And we have a function that receives a message and removes it from a relationship, for example:
CREATE FUNCTION get_and_delete_message(p_foo1 integer)
RETURNS TABLE(r_id integer, r_foo1 integer, r_foo2 integer, r_foo3 text) AS $$
DECLARE
message_id integer;
BEGIN
SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1;
RETURN QUERY SELECT * FROM message WHERE foo1 = p_foo1 LIMIT 1;
DELETE FROM message where id = message_id;
END;
$$ LANGUAGE plpgsql;
Assuming READ_COMMITTED
the isolation level might be that two concurrent transactions from two users return the same message, although obviously only one is deleting / receiving It. This is not the desired behavior for my application, I want one message to be read by only one user.
Assuming REPEATABLE_READ
this won't happen.
But after reading it FOR UPDATE
I thought it might be possible to use the level READ_COMMITTED
and change the function get_and_delete_message
like this
...
BEGIN
SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1;
RETURN QUERY SELECT * FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE;
DELETE FROM message where id = message_id;
END;
...
In my opinion, using FOR UPDATE
in the second will SELECT
actually block the returned rows until the end of the transaction, so if we have two concurrent transactions, then only one will actually return and delete the message.
This is true? Or should I also do SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE
? I have not been able to find information on merging SELECT INTO
with FOR UPDATE
. Any ideas on this?
source to share
You can do it in one expression, no choice needed:
CREATE FUNCTION get_and_delete_message(p_foo1 integer)
RETURNS TABLE(r_id integer, r_foo1 integer, r_foo2 integer, r_foo3 text)
AS $$
DELETE FROM message
where foo1 = p_foo1
returning *;
END;
$$ LANGUAGE sql;
This will delete rows and then return all deleted rows as a result of the statement delete
.
source to share