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.
»
The first ("default") notion makes sense because its account uses the word "new". The implication is that it's legal to
setit during an ongoing txn; but that doing this won't have any effect until it has been ended. On the other hand, the
"settransaction" 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",
thenyou 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?
