Re: [bug fix] PG10: libpq doesn't connect to alternative hosts when some errors occur

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tsunakawa,
> Takayuki
> Done. I'll examine whether we can use SQLSTATE.

I tried conceivable errors during connection. Those SQLSTATEs are as follows:

[transient error (after which you may want to try next host)]
53300 FATAL: too many connections for role "tuna"
57P03 FATAL: the database system is starting up

[configuration error (after which you may give up connection without other hosts. Really?)]
55000 FATAL: database "template0" is not currently accepting connections
3D000 FATAL: database "aaa" does not exist
28000 FATAL: no pg_hba.conf entry for host "::1", user "tunakawa", database "postgres", SSL off
28000 FATAL: role "nouser" does not exist
28P01 FATAL: password authentication failed for user "tuna"
28P01 DETAIL: Password does not match for user "tuna".

I looked through the SQLSTATEs, and thought the below ones could possibly be returned during connection:

https://www.postgresql.org/docs/devel/static/errcodes-appendix.html

[transient error]
Class 08 - Connection Exception
Class 40 - Transaction Rollback
Class 53 - Insufficient Resources
Class 54 - Program Limit Exceeded
Class 55 - Object Not In Prerequisite State
Class 57 - Operator Intervention
Class 58 - System Error (errors external to PostgreSQL itself)
Class XX - Internal Error

[configuration error]
Class 28 - Invalid Authorization Specification
Class 3D - Invalid Catalog Name
Class 42 - Syntax Error or Access Rule Violation

So, how about trying connection to the next host when the class code is neither 28, 3D, nor 42?

Honestly, I'm not happy with this approach, for a maintenance reason that others are worried about. Besides, when the connection target is not postgres and returns invalid data, no SQLSTATE is available. I'm sorry to repeat myself, but I believe PgJDBC's approach is practically good. If you think the SQLSTATE is the only way to go, I will put up with it. It would be disappointing if nothing is done.

Regards
Takayuki Tsunakawa