Re: [GENERAL] PLPGSQL how to get transaction isolation level info

[Janning Vygen]

Am Freitag, 15. Juli 2005 19:19 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > How can a function determine in which isolation level it runs?
>
> select current_setting('transaction_isolation');

Thank you for the hint. 

I didn't find it myself because tab completion on SHOW doesn't show it.

But i read the docs again carfully and found "SHOW all" which shows 
"transaction_isolation". I guess it would be fine if the refernce page for 
SHOW and current_setting() list all the possible configuration settings. 

And it should be added in bin/psql/tab-complete.c  but maybe it is already 
done. i dont have the cvs repository source code at hand. Maybe someon can 
pick it up.

kind regards,
janning



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PLPGSQL how to get transaction isolation level info

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> How can a function determine in which isolation level it runs?

select current_setting('transaction_isolation');

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] PLPGSQL how to get transaction isolation level info

[Janning Vygen]

i have a function which calculates some aggregates (like a materialized view). 
As my aggregation is made with a temp table and 5 SQL Queries, i need a 
consistent view of the database. Therefor i need transaction isolation level  
SERIALIZABLE, right? Otherwise the second query inside of the function could 
read data which differs from the first query (Nonrepeatable Read or Phantom 
Read)

ok. so far so good. But know i would like my function to abort if it is not 
running inside ransaction isolation level SERIALIZABLE.

How can a function determine in which isolation level it runs?

I looked at the SHOW statement but didn't find anything. i dont wnat to know 
the default_transaction_isolation but the current one used.

The reason is mainly for preventing some mistakes inside the caller app. Of 
course the app should know what it does and wrap the function call inside a 
serializable transaction, but to be sure that my materialized view is 
consistent with the rest of the data i would like to enforce it.

Is it possible to get info about the current transaction isolation level? 

kind regards,
janning 

---(end of broadcast)---
TIP 6: explain analyze is your friend