Re: transaction_isolation vs. default_transaction_isolation

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> 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.
>> »
>>
>> ...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.

I believe that I see it now. Thanks. There's a Heisenberg effect lurking here. You can't observe anything, or change anything, except by using SQL. And most SQL statements have the effect that, if there's no transaction ongoing at the moment that such a statement is issued, then what I've decided to call "single statement automatic transaction mode" is started and then immediately ended. Otherwise, such a statement has its effect within the ongoing transaction—which continues to be ongoing after its completion.

Just a few statements, like "start transaction", "commit", "set transaction", and the like don't have the capacity to initiate single statement automatic transaction mode but, rather, have meanings and possible error outcomes according to whether or not a transaction is ongoing. Things would be a lot simpler if the "transaction_isolation" run-time parameter were read-only and the attempt to set it caused

55P02: parameter "transaction_isolation" cannot be changed now

Oh well, I'll simply adopt the practice never to set it and to read it only when (by virtue of the session history that I know) my session is currently in an ongoing explicitly started transaction.