postgresqlselectread-committed-snapshot

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


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

Solution

  • 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();