Re: transaction_isolation vs. default_transaction_isolation - Mailing list pgsql-general

> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.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
levelof each new transaction. The default is “read committed”. 
>>
>> transaction_isolation (enum): This parameter reflects the current transaction's isolation level. At the beginning of
eachtransaction, it is set to the current value of default_transaction_isolation. Any subsequent attempt to change it
isequivalent 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
basicallypointless. I suppose the difference in behavior when using SET TRANSACTION versus changing this configuration
variablemight be surprising but all that is happening is the one is giving you an error when you do something pointless
andthe 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
canthen SET them to change it is I suppose convenient but since there is an actual SQL command to do the same one
shoulduse 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
momentthat such a statement is issued, then what I've decided to call "single statement automatic transaction mode" is
startedand then immediately ended. Otherwise, such a statement has its effect within the ongoing transaction—which
continuesto be ongoing after its completion. 

Just a few statements, like "start transaction", "commit", "set transaction", and the like don't have the capacity to
initiatesingle statement automatic transaction mode but, rather, have meanings and possible error outcomes according to
whetheror not a transaction is ongoing. Things would be a lot simpler if the "transaction_isolation" run-time parameter
wereread-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
Iknow) my session is currently in an ongoing explicitly started transaction.