Re: [JDBC] Problem with Serializable transactions

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> What do we do if setTransactionIsolation() is called halfway through a
> transaction?

You can't change the isolation status of an already-started transaction
(both Postgres and the SQL spec agree on this). So to the extent that
the user expects this to affect the current xact and not just subsequent
xacts, that's user error that might be best addressed through
documentation. However, it sounds like the JDBC driver is contributing
to the problem by delaying the effectiveness of the SET more than a user
would reasonably expect.

> Perhaps we should just always throw an exception if
> setTransactionIsolation() is called with autocommit off, since we know
> that doesn't work at all currently?

Might be a good quick-hack answer. In the long run I think what you
ought to do is save the requested change and apply it during the next
transaction boundary --- that is,
commit;begin
becomes
commit; SET SESSION ... ; begin
I also wonder if you could force this to happen immediately if the
current transaction hasn't actually done anything yet.

Given your comment about the JDBC spec, Robert's test program is simply
wrong as written, so his answer is to change the order of calls.

regards, tom lane