Re: auto-reconnect: temp schemas, sequences, transactions - Mailing list pgsql-general

On Monday 02 May 2011 17:32:26 Tom Lane wrote:
> If the client-side logic tries to re-issue these queries
> after re-connecting, it would be up to that logic to be careful about
> what to reissue or not.  Possibly this is a question for the author
> of your client library.

I see. So I have two use cases:
1) "my" client library;
2) psql :p

Let's look briefly at psql, shall we? (I would look at what needs changing in
"my" client library when I understand what is according to you a proper
solution to psql.)


If there is a script executed in psql there is no easy way to catch that psql
has reconnected in the middle of it... The simplest example that could do a
lot of damage would be a simple script executed in psql (by e.g. \i file_name
or "psql -f"):
    begin;
    update test1 set value = value+1;
    update test1 set value = value+10;
    update test1 set value = value+100;
    commit;
Obviously the intention is that all three queries succeed (values would be
increased by 111) or all three fail (values not changed).

See attached "orig" output: if backend dies just after the first query, then
the next query triggers reconnect and psql does not execute it, but ... the
third update query gets happily executed outside of transaction. So the result
is that in this case value was increased by 100 - a really not expected (and
possibly - very, very bad) result.

I think it should be considered a problem which should be solved in psql. What
follows is my rough solution => it does solve the problem, although there is
probably a much easier way to solve it.


Now, the problem is that psql (as well as any other client program/library)
* would have hard time keeping track of whether its session is in a
transaction;
* libpq already does this for us, in conn->xactStatus;
* but (because of check that conn->status == CONNECTION_OK in
PQtransactionStatus() ) there is no way to get to the value of conn-
>xactStatus once the connection is dead (i.e. to the last trans status of a
now-disconnected connection)....


You will probably have a much better idea, but what I did (see attached patch)
is I removed the part of PQtransactionStatus(const PGconn *conn) which says
that "if conn->status != CONNECTION_OK then returned value is
PQTRANS_UNKNOWN". Thus the meaning of the function PQtransactionStatus()
changes slightly (instead of "trans status of an active connection" it would
mean now "trans status of an active connection or last trans status of a
disconnected connection"), but the API and binary compatibility of libpq is
preserved.

Anyway, after this change I am able to get pre-disconnect trans status in psql
just before reconnecting. And if we were in a transaction then after
reconnecting I create immediately a trans-in-error (again, see a psql part of
the attached patch; BTW: how to trigger an error in a way nicer than "select
1/0", preferably with a message? i.e., is there some libpq equivalent of
"raise exception"?).

See the "new" output in the attachment: the result is that, in the example I
gave at the beginning of this mail, if there is a disconnect after the first
update, then the second query triggers a reconnect, but we are in trans-in-
error, so also all subsequent queries fail => it is as if a proper db
transaction was rolled back. I think this is a much, much better behaviour of
psql, is it?


PS: It would be more straightforward to change PQreset() in a similar way
instead of changing psql (PQreset has direct access to conn->xactStatus), but
ofc PQreset() it's a part of public API of libpq; client code could in
principle execute PQreset() when within a db transaction, and the expectation
would be that after the call you get a "clean" new session. Still, maybe a not
bad idea for the future would be to keep PQreset() as it is and add e.g. a
PQreconnect() which would do basically the same but in the case the old
connection was in transaction it would right away create a new trans-in-error
in the new session? Well, just an idea => it would lead to even less handling
in the client programs (like psql => it would just call PQreconnect() and not
have to issue "begin + create and error").

Best,
~Marek