transaction_isolation vs. default_transaction_isolation

On Mon, 2009-10-12 at 22:13 -0700, Josh Berkus wrote:

However, for *two* settings, and two settings only, we distinguish that
by naming an identical setting "default_*" in postgresql.conf. This is
confusing and inconsistent with the rest of the GUCS. Namely:

default_transaction_isolation
default_transaction_read_only

I think they are named "default_" because whatever you specify at the
beginning of a transaction overrides the GUC.

For example, in:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET default_transaction_isolation=serializable;
...

the "default_" makes it more clear which setting overrides the other.

Regards,
Jeff Davis

Josh Berkus <josh@agliodbs.com> wrote:

default_transaction_isolation
default_transaction_read_only

They are settings of transaction_isolation and transaction_read_only
for *next* transactions, no?

transaction_isolation
transaction_read_only

Non-default versions are almost read-only variables
because we can set them at the beginning of transactions.

BEGIN;
SET transaction_isolation = 'serializable';
SET default_transaction_isolation = 'read committed';
SHOW transaction_isolation;
=> serializable
SHOW default_transaction_isolation;
=> read committed
COMMIT;
-- next transaction uses default_transaction_isolation
SHOW transaction_isolation;
=> read committed

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Itagaki-san,

BEGIN;
SET transaction_isolation = 'serializable';
SET default_transaction_isolation = 'read committed';
SHOW transaction_isolation;
=> serializable
SHOW default_transaction_isolation;
=> read committed
COMMIT;
-- next transaction uses default_transaction_isolation
SHOW transaction_isolation;
=> read committed

Thank you; that was very informative.

--Josh

On Mon, 2009-10-12 at 22:22 -0700, Jeff Davis wrote:

On Mon, 2009-10-12 at 22:13 -0700, Josh Berkus wrote:

However, for *two* settings, and two settings only, we distinguish that
by naming an identical setting "default_*" in postgresql.conf. This is
confusing and inconsistent with the rest of the GUCS. Namely:

default_transaction_isolation
default_transaction_read_only

I think they are named "default_" because whatever you specify at the
beginning of a transaction overrides the GUC.

For example, in:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET default_transaction_isolation=serializable;
...

the "default_" makes it more clear which setting overrides the other.

Yeah, they basically have semantics specified by the SQL standard that
are not compatible with anything else in GUC land. They are more like
SET LOCAL settings, but again not quite.

Yeah, they basically have semantics specified by the SQL standard that
are not compatible with anything else in GUC land. They are more like
SET LOCAL settings, but again not quite.

Mind you, transaction_isolation and transaction_read_only aren't
documented anywhere in our docs *as settings*, even though they show up
in pg_settings.

Doc patch coming ...

--Josh Berkus

Josh Berkus wrote:

Yeah, they basically have semantics specified by the SQL standard that
are not compatible with anything else in GUC land. They are more like
SET LOCAL settings, but again not quite.

Mind you, transaction_isolation and transaction_read_only aren't
documented anywhere in our docs *as settings*, even though they show up
in pg_settings.

Doc patch coming ...

What are we doing with this?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +