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.