Re: auto-reconnect: temp schemas, sequences, transactions

On Wednesday 04 May 2011 18:04:16 Tom Lane wrote:
> Marek Wieckowski <wieckom(at)foxi(dot)nl> writes:
> > If there is a script executed in psql there is no easy way to catch that
> > psql has reconnected in the middle of it...
>
> As far as psql goes, it should certainly abandon executing any script
> file if it loses the connection. I rather thought it did already.
> See the bit just above where you propose patching:
>
> if (!pset.cur_cmd_interactive)
> {
> psql_error("connection to server was lost\n");
> exit(EXIT_BADCONN);
> }

Hmm, indeed, I've missed it. Clear for psql. And yes, I agree that for psql it
is better to exit. Thanks for the answer.

But for the library which I'm using, simply exiting/aborting is not an option
(and this is why I was looking into this in the first place). There is a
danger that client programs will continue issuing queries while believing that
they are in a transaction... They do expect db errors and rolled back
transactions, but not that their begin-commit section would be executed only
partially. Solving this on the level of "my" library would solve it once for
good (and the alternative sounds more complex: it would require exposing extra
info to the programs using this library, and add handling of reconnect
situation in each of these programs etc.).

In my head, it wraps up to a following structure: In the library (which gives
access to libpq functionality):
1. anytime we use db connection we would check if connection is OK;
2. if yes, we would ask for PQtransactionStatus() and keep a copy of returned
status;
3. if not, we would try to reconnect, BUT based on (2.) we would know if
before the connection was lost we were in a trans;
4. if we were in a trans before disconnect, then immediately after
reconnecting we would create a trans-in-error.

Does the above make sense to you? Any points of attention?

Also, would you have any suggestion for how to create a trans-in-error in a
way nicer than
begin;
select 1/0;
preferably with a message? In other words, is there some libpq equivalent of
"raise exception"?

Best,
~Marek