set isolation level for postgresql stored procedures

Question

Hopefully a simple question, but one for which I haven't readily found a decent answer. I'm reliably informed that stored procedures (user-defined DB functions) in PostgreSQL (specifically, version 9.0.4) are inherently transactional, inasmuch as they are called through a SELECT statement which itself is a transaction. So how does one choose the isolation level of the stored procedure? I believe in other DBMSs the desired transactional block would be wrapped in a START TRANSACTION block for which the desired isolation level is an optional parameter.

As a specific made-up example, say I want to do this:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

And imagine I want to make sure this function is always performed as a serializable transaction (yes, yes, PostgreSQL SERIALIZABLE isn't proper serializable, but that's not the point). I don't want to require it to be called as

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row('foo');
COMMIT;

So how do I push the required isolation level down into the function? I believe I cannot just put the isolation level in the BEGIN statement, as the manual says

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in.

The most obvious approach to me would be to use SET TRANSACTION somewhere in the function definition, e.g.,:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

While this would be accepted, it's not clear than I can rely on this to work. The documentation for SET TRANSACTION says

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it will appear to have no effect, since the transaction will immediately end.

Which leaves me puzzled, since if I call a solitary SELECT add_new_row('foo'); statement I would expect (provided I haven't disabled autocommit) the SELECT to be running as a single-line transaction with the session default isolation level.

The manual also says:

The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH, or COPY) of a transaction has been executed.

So what happens if the function is called from within a transaction with a lower isolation level, e.g.,:

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row('foo');
COMMIT;

For a bonus question: does the language of the function make any difference? Would one set the isolation level differently in PL/pgSQL than in plain SQL?

I'm a fan of standards and documented best practices, so any decent references would be appreciated.

Answer 1

You can't do that.

What you could do is have your function check what the current transaction isolation level is and abort if it's not the one you want. You can do this by running SELECT current_setting('transaction_isolation') and then checking the result.

Answer 2

The language of the function makes no difference whatsoever.

This fails:

test=# create function test() returns int as $$
  set transaction isolation level serializable;
  select 1;
$$ language sql;
CREATE FUNCTION
test=# select test();
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL function "test" statement 1

Note that in your particular example, you could do this using a trigger on your first table. Just make sure that row count updates are done in a consistent order to avoid dead-locks, and you'll do fine in repeatable-read mode.

I'm a fan of standards

The PL/languages are platform specific.

Answer 3

Transaction isolation means which changes made in other concurent transactions you can access.

If you want to serialize execution you have to use locks.

You may use after row trigger and update count. "UPDATE row_counts_table" will lock table and all transactions will be serialized. It is slow.

In your example you have two statements. Insert is executed but update have to wait other transactions and count is not valid in this period.

Answer 4

In PG your procedures aren't separate transactions. That is the stored procedure takes part in an existing transaction.

BEGIN TRAN

SELECT 1;
SELECT my_proc(99);

ROLLBACK TRAN;

With that said you have to set the transaction level where the transaction starts which is outside the stored procedure.

One option would be to configure the server to run in the isolation you mostly want to use and do a SET for the edge cases where it differs from your server setting.