Thread: Setting the transaction isolation level for a stored procedure

Sorry, I forgot to put a subject title in my last email. So here it is again.

Hi ppl,

Just wondering if anyone knows if its possible to set the transaction 
isolation
level from inside a stored procedure in plpgsql. Or do I need to run the 
'set transaction
isolation level' command before I called the stored procedure? I'm not even 
sure that
would work, I assume plpgsql uses the current transaction isolation level?
Any help would be much appreciated :)

Cheers, Keith. 

Hi all,

Does anybody know the transaction isolation level default when a plpgsql 
stored procedure is called?
Is it possible to set it? Or is this controlled by SPI?

Cheers,
Keith.

Use the command "\h set" and you'll see the parameters.

test=# \h set
Command:     SET
Description: Set run-time parameters for session
Syntax:
SET variable { TO | = } { value | 'value' | DEFAULT }
SET CONSTRAINTS {ALL | constraintlist} mode
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }

test=# show transaction isolation level;
NOTICE:  TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
test=#

HTH,
Poul L. Christiansen


Keith Wong wrote:
> 
> Hi all,
> 
> Does anybody know the transaction isolation level default when a plpgsql
> stored procedure is called?
> Is it possible to set it? Or is this controlled by SPI?
> 
> Cheers,
> Keith.

Keith Wong writes:

> Does anybody know the transaction isolation level default when a plpgsql 
> stored procedure is called?
> Is it possible to set it? Or is this controlled by SPI?

It's whatever the transaction that called the function was set to.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/