3

Assume I have a message relation where I save messages that was created with this command:

CREATE TABLE message 
(
     id serial primary key, 
     foo1 integer, 
     foo2 integer, 
     foo3 text
)

And we have a function that gets a message and deletes it from the relation, like this:

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 isolation level it could be that two concurrent transactions from two users return the same message, although obviously only ones deletes/gets 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 wouldn't happen.

But after reading about FOR UPDATE I thought perhaps it's still possible to use READ_COMMITTED level and change the get_and_delete_message function as following:

   ...
    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;
    ...

From my understanding, using FOR UPDATE in the second SELECT will actually lock the returned rows until the end of the transaction, so if we have 2 concurrent transactions only one will actually return and delete the message.

Is this the case? Or should I also do SELECT id INTO message_id FROM message WHERE foo1 = p_foo1 LIMIT 1 FOR UPDATE? I couldn't find any information on combining SELECT INTO with FOR UPDATE. Any ideas on that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
insumity
  • 5,311
  • 8
  • 36
  • 64

1 Answers1

1

You can do that in a single statement, no need for a select:

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 the rows and then return all deleted rows as a result of the delete statement.

  • 2
    Is this in `READ_COMMITTED` isolation level going to guarantee me that no 2 concurrent transactions return the same message? Something more: Actually I tried to keep my question's example simple so I have `SELECT ... ORDER BY` and it seems it's not possible to do the same with `DELETE`. At the end your answer doesn't really answer my questions :S, though I really appreciate the time you took to answer – insumity Oct 04 '14 at 23:52