Postgresql – set serializable isolation for update query postgresql –9.4

isolation-levelpostgresqlpostgresql-9.4

CREATE TABLE xtable(xtableid integer, xcolumn integer)
CREATE TABLE ytable(ytableid integer, ycolumn integer)

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

I want to set SERIALIZABLE isolation for the UPDATE and SELECT query inside but I get an error saying

ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"

How can I use this isolation level in PostgreSQL? I don't have a live issue, just want to understand how it works.

Best Answer

A function takes part of an existing transaction. To avoid this context error, the transaction mode should be changed before the call to the function. Therefore you must remove the SET TRANSACTION statement function, and then use the code (for example):

Using your example...

function code -

--Atomically, using your example
CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

calling code to function -

    ...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT FROM foofunction (1,1);
COMMIT;
    ...

Or yet:

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
EXCEPTION WHEN others THEN
    ROLLBACK;
RETURN;
    COMMIT;
End $$ LANGUAGE plpgsql;

And

...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT FROM foofunction (1,1);
...

I hope have contributed!

Related Question