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:

begin;
insert into s.t(v) values(19);
set transaction isolation level serializable;

then you get the 25001 error "SET TRANSACTION ISOLATION LEVEL must be called 
before any query".

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;

But this:

begin;
insert into s.t(v) values(19);
set transaction_isolation = serializable;

causes the 25001 error.

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?






Reply via email to