Can a SELECT performed with READ COMMITTED trigger an exception in Postgresql?

Question

I need to take a snapshot (SELECT) of some table rows for display. These rows will never be modified or deleted in that same transaction, or transmitted to another transaction which could modify those rows. I only want a simple snapshot-like read in a Postgresql function.

For example:

SELECT * FROM "my_table" WHERE "amount" < 1000;

I was thinking about setting the READ COMMITTED transaction level in my Postgresql function:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Will this make sure I won't ever face an exception with my SELECT, no matter if other heavy and complex transactions are running simultaneously? I believe that yes, but I am not a Postgresql expert.

UPDATE:

After doing more reading, I am wondering whether this could to the trick too?

SET TRANSACTION ISOLATION LEVEL READ ONLY DEFERRABLE

That would be `REPEATABLE READ` rather than the default `READ COMMITTED`, but this can't be set in a function. Why not just open a `CURSOR` based on the query and close it when you're done? — Daniel Vérité, Mar 28 '14 at 18:49
Retrieving all rows at once is fine in my case, so I don't need a cursor. So, are you saying that SET TRANSACTION ISOLATION LEVEL REPEATED READ; solves my issue? If yes, create a solution and I'll approve it! — Jérôme Verstrynge, Mar 28 '14 at 18:54
A single SQL statement works with a snapshot so if you're retrieving all rows at once I don't think there's an issue to begin with. If however you wanted to change the isolation level this can't be done from within a function. If you try it will error out. — Daniel Vérité, Mar 28 '14 at 18:58
Ok, then how can I change the isolation level on a transaction performed within a function? Is this possible? — Jérôme Verstrynge, Mar 28 '14 at 19:11
Client-side, before calling the function. But you really want to read and digest [SET TRANSACTION](http://www.postgresql.org/docs/current/static/sql-set-transaction.html) in the doc. — Daniel Vérité, Mar 28 '14 at 19:16
@Daniel I have created a solution based on your feedback. Can you double-check it to make sure I am not missing something? I understand setting transaction level might be overkill in my case, but I want to make sure this is the right procedure in general. Thanks! — Jérôme Verstrynge, Mar 28 '14 at 19:40
It's the right procedure to ensure that the function will not write anything to the database. However in your example it's clearly not needed. — Daniel Vérité, Mar 28 '14 at 20:29

score 0 · Accepted Answer · answered Mar 28 '14 at 19:36

Using Daniel's information, if I perform the following to create the table and to fill it:

CREATE TABLE IF NOT EXISTS "my_table" (amount integer);
TRUNCATE "my_table";

INSERT INTO "my_table" VALUES (100);
INSERT INTO "my_table" VALUES (200);
INSERT INTO "my_table" VALUES (1000);
INSERT INTO "my_table" VALUES (2000);

CREATE OR REPLACE FUNCTION my_function()
    RETURNS SETOF "my_table" AS $$
BEGIN
    RETURN QUERY SELECT * FROM "my_table" WHERE "amount" < 1000;
END; $$
LANGUAGE plpgsql;

I can set an transaction level on the client's side to retrieve the rows I want with the isolation I want (though this might be redundant in my case for a simple SELECT):

SET TRANSACTION ISOLATION LEVEL READ ONLY DEFERRABLE;
SELECT * FROM my_function();

Can a SELECT performed with READ COMMITTED trigger an exception in Postgresql?

1 Answers1