Re: auto-reconnect: temp schemas, sequences, transactions
От | Marek Więckowski |
---|---|
Тема | Re: auto-reconnect: temp schemas, sequences, transactions |
Дата | |
Msg-id | 201105041757.37323.wieckom@foxi.nl обсуждение исходный текст |
Ответ на | Re: auto-reconnect: temp schemas, sequences, transactions (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: auto-reconnect: temp schemas, sequences, transactions
|
Список | 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
Вложения
В списке pgsql-general по дате отправления: