Re: transaction_isolation vs. default_transaction_isolation

On Tue, Feb 21, 2023 at 12:32 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> I found a discussion with the same title as this emails’s subject here:
>
> https://postgrespro.com/list/thread-id/1741835
>
> It dates from 2009. But it seems to be unresolved. The current PG doc here:
>
> 20.11. Client Connection Defaults
> https://www.postgresql.org/docs/15/runtime-config-client.html
>
> has an entry for each setting thus:
>
> «
> default_transaction_isolation (enum): Each SQL transaction has an
> isolation level, which can be either “read uncommitted”, “read committed”,
> “repeatable read”, or “serializable”. This parameter controls the default
> isolation level of each new transaction. The default is “read committed”.
>
> transaction_isolation (enum): This parameter reflects the current
> transaction's isolation level. At the beginning of each transaction, it is
> set to the current value of default_transaction_isolation. Any subsequent
> attempt to change it is equivalent to a SET TRANSACTION command.
> »
>
> The first ("default") notion makes sense because its account uses the word
> "new". The implication is that it's legal to set it during an ongoing txn;
> but that doing this won't have any effect until it has been ended. On the
> other hand, the "set transaction" SQL statement is legal only during an
> ongoing txn.

> (An attempt when there is none causes the 25P01 error, "SET TRANSACTION
> can only be used in transaction blocks". Moreover, if you do this:
>
>
> This implies that if, as the doc says, "set transaction_isolation: is
> equivalent to "SET TRANSACTION ISOLATION LEVEL", then you should get the
> same errors at the same moments. But you don't. This works fine when
> there's no ongoing txn:
>
> set transaction_isolation = serializable;
>
>

> This suggests that the semantics of "set transaction_isolation" is
> context-sensitive:
>
> - when there's no ongoing txn. it has the same effect as "set
> default_transaction_isolation".
> - when you're in a txn, it has the same effect as ""SET TRANSACTION
> ISOLATION LEVEL"
>
> Is my hypothesis right?
>
>
I don't think so. There is a transaction when the SET is executed, it just
immediately commits and so doing that is basically pointless.
I suppose the difference in behavior when using SET TRANSACTION versus
changing this configuration variable might be surprising but all that is
happening is the one is giving you an error when you do something pointless
and the other just does the pointless thing without complaint.

Frankly, the non-default versions are simply the one and only way you can
see what the current value is. That you can then SET them to change it is
I suppose convenient but since there is an actual SQL command to do the
same one should use that command, not the setting.

David J.