Thread: PLPGSQL how to get transaction isolation level info

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

Janning Vygen <vygen@gmx.de> writes:
> How can a function determine in which isolation level it runs?

select current_setting('transaction_isolation');

            regards, tom lane

Am Freitag, 15. Juli 2005 19:19 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> 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