Re: Terminate the idle sessions - Mailing list pgsql-hackers

On Thu, Jan 7, 2021 at 3:03 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> On Thu, Jan 7, 2021 at 12:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > * The SQLSTATE you chose for the new error condition seems pretty
> > random.  I do not see it in the SQL standard, so using a code that's
> > within the spec-reserved code range is certainly wrong.  I went with
> > 08P02 (the "P" makes it outside the reserved range), but I'm not
> > really happy either with using class 08 ("Connection Exception",
> > which seems to be mainly meant for connection-request failures),
> > or the fact that ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT is
> > practically identical but it's not even in the same major class.
> > Now 25 ("Invalid Transaction State") is certainly not right for
> > this new error, but I think what that's telling us is that 25 was a
> > misguided choice for the other error.  In a green field I think I'd
> > put both of them in class 53 ("Insufficient Resources") or maybe class
> > 57 ("Operator Intervention").  Can we get away with renumbering the
> > older error at this point?  In any case I'd be inclined to move the
> > new error to 53 or 57 --- anybody have a preference which?
>
> I don't have a strong view here, but 08 with a P doesn't seem crazy to
> me.  Unlike eg 57014 (statement_timeout), 57017 (deadlock_timeout),
> 55P03 (lock_timeout... huh, I just noticed that DB2 uses 57017 for
> that, distinguished from deadlock by another error field), after these
> timeouts you don't have a session/connection anymore.  The two are a
> bit different though: in the older one, you were in a transaction, and
> it seems to me quite newsworthy that your transaction has been
> aborted, information that is not conveyed quite so clearly with a
> connection-related error class.

Hmm, on further reflection it's still more similar than different and
I'd probably have voted for 08xxx for the older one too if I'd been
paying attention.

One of the strange things about these errors is that they're
asynchronous/unsolicited, but they appear to the client to be the
response to their next request (if it doesn't eat ECONNRESET instead).
That makes me think we should try to make it clear that it's a sort of
lower level thing, and not really a response to your next request at
all.  Perhaps 08 does that.  But it's not obvious...  I see a couple
of DB2 extension SQLSTATEs saying you have no connection: 57015 =
"Connection to the local Db2 not established" and 51006 = "A valid
connection has not been established", and then there's standard 08003
= "connection does not exist" which we're currently using to shout
into the void when the *client* goes away (and also for dblink failure
to find named connection, a pretty unrelated meaning).