Bryn Llewellyn wrote:
> B.t.w., I’m guessing that the “begin” SQL command that you see in the log
> that I mentioned is actually issued by (some) clients—at least psql and
> Python-on-psycopg2—as an explicit call from the client. In other words, it
> isn’t the server that generates this. Does anyone know for sure how this
> works?
Well, that's the point of Autocommit, and moreover it does nothing
else. Maybe you're still confused about this.
* Autocommit off = the client automatically adds a "BEGIN" when
it thinks a transaction must be started on behalf of the user.
* Autocommit on = the client does nothing.
The fact that "off" implies doing something and "on" implies not
interfering is counterintuitive, but that's how it is. Autocommit is
for compatibility with other databases. If it was only for Postgres, I
guesss either it wouldn't exist in the first place or it should be
called "AutoStartTransactionBlock" or something like that, because
that's just what it really does.
Anyway, the server only know whether a BEGIN has been issued.
It never knows or cares whether it has been added implicitly or explicitly,
which is why it can be quite confusing to reason about server-side
differences in terms of Autocommit, as you do in some of your previous
messages.
It should be stressed that Autocommit is not a parameter of the
session between Postgres and the SQL client, but rather it's a
parameter of the session between the user and their SQL client.
So when you're hypothesizing that a plpgsql block in a procedure
would look at this parameter or change it temporarily (your
points #2 and #5 in your analysis of p2's execution), you should
see that it's impossible, because on the server-side, this parameter
just does not exist.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite