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

1

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!

  • thanks for the answer...it is interesting and i dont know why but if i add only one line "EXCEPTION WHEN OTHERS THEN" inside foofunction and still keep set transaction isolation level line, and then the problem goes away. And if i do what u said then it still works either way. I really dont get the logic about that! – lowdegeneration Mar 3 '16 at 23:26
  • note: i have changed my comment third time, i guess i need some sleep! Anyway another question: is it possible to retry updating if somehow any locks happen inside foofunction so that it will finish its update job programmatically, second question ; do i need to cancel dead lock manually each time it happens? – lowdegeneration Mar 3 '16 at 23:43
  • When you add only the exception of line, the interpreter will treat your SQL statements block, and if any statement fails, the exception is executed! Rerun your update after failure is entirely possible! See the links below for reference: – Alvaro Neto Mar 4 '16 at 1:52
  • Links - link link link – Alvaro Neto Mar 4 '16 at 1:56
  • Also missed the following ...: after a preset time, about 1 second by default, a deadlock is detected and reported with this error message for the transactions are aborted. Therefore PostgreSQL itself comes deadlocks. – Alvaro Neto Mar 4 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.