Re: A bad behavior under autocommit off mode - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Actually, looking at this email, having a SET that is unchangeable would
> > fix the issues with autocommit, datestyle, and client encoding for jdbc.
> 
> > The basic issue is that certain interfaces want to set these parameters
> > as unchangeable, and that would allow this.
> 
> I'm not sure they need these parameters to be *unchangeable*.  What they
> need is to *know what they are*, with certainty.  The notion of issuing
> an automatic report message whenever the values change would seem to
> answer that.

What concerns me also about the reporting problem is that some of these
interfaces must issue queries in several places in the code, so somehow
they have to make sure they check for those _special_ values in all
those places.

> On the other hand, that only directly solves the problem for a single
> layer of client library.  Imagine, say, a middleware layer built on top
> of JDBC.  If that layer wants to track the state of DATESTYLE, or any
> other parameter, do these mechanisms help it?  No, unless JDBC exposes
> the parameter-update-reporting protocol ... which it probably won't
> because that's not part of the JDBC API spec.

Good point.

> When you look at the problem in terms of N levels of client-side code,
> I fear that none of the ideas we've discussed really provide a
> satisfactory answer.  Reporting doesn't work unless the reports
> propagate all the way up the client stack.  "Unchangeable params" don't
> work --- which level gets to dictate the actual setting, and how do the
> other levels learn what it is?
> 
> Any thoughts about it?

What it seems we need is some way to know that a SET is coming from the
interface vs. coming from the user, and as you point out, that may not
be completely clear.  I am also hoping to find something that is
generic, not tied to a specific SET variable, because I assume this will
come up in the future.

One idea I had was to have a SET INTERFACE option, that could only be
changed by another SET INTERFACE option.  It wouldn't prevent a client
from doing it, but it would make it pretty clear that the user was
messing with something the interface wants to control.

A more bizarre idea is that SET INTERFACE would have a password that
could only be changed by another SET INTERFACE with the same password!

Also, with autocommit, is the idea for autocommit logic to be in the
clients, or just control of autocommit to be in the clients, with the
logic still being in the server?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073