Re: transaction_isolation vs. default_transaction_isolation

Hackers,

A slew of settings in postgresql.conf, including work_mem, search_path,
DateStyle, and about 80 others are effectively just defaults for new
connections, since they can be changed by any user.

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
transaction_isolation
transaction_read_only

For 8.5, I would like to consolidate these into only 2 settings and drop
the default_* settings.

--Josh Berkus

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(at)agliodbs(dot)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(at)momjian(dot)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. +