Re: [HACKERS] [bug fix] PG10: libpq doesn't connect to alternativehosts when some errors occur - Mailing list pgsql-hackers

From: Michael Paquier [mailto:michael.paquier@gmail.com]
> On Fri, May 12, 2017 at 10:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I would not really expect that reconnection would retry after
> > arbitrary failure cases.  Should it retry for "wrong database name", for
> instance?
> > It's not hard to imagine that leading to very confusing behavior.
> 
> I guess not as well. That would be tricky for the user to have a different
> behavior depending on the error returned by the server, which is why the
> current code is doing things right IMO. Now, the feature has been designed
> similarly to JDBC with its parametrization, so it could be surprising for
> users to get a different failure handling compared to that. Not saying that
> JDBC is doing it wrong, but libpq does nothing wrong either.

I didn't intend to make the user have a different behavior depending on the error returned by the server.  I meant
attemptingconnection to alternative hosts when the server returned an error. I thought the new libpq feature tries to
connectto other hosts when a connection attempt fails, where the "connection" is the *database connection* (user's
perspective),not the *socket connection* (PG developer's perspective).  I think PgJDBC meets the user's desire better
--"Please connect to some host for better HA if a database server is unavailable for some reason."
 

By the way, could you elaborate what problem could occur if my solution is applied?  (it doesn't seem easy for me to
imagine...) FYI, as below, the case Tom picked up didn't raise an issue:
 

[libpq]
$ psql -h localhost,localhost -p 5450,5451 -d aaa
psql: FATAL:  database "aaa" does not exist
$


[JDBC]
$ java org.hsqldb.cmdline.SqlTool postgres
SqlTool v. 3481.
2017-05-15T10:23:55.991+0900  SEVERE  Connection error:
org.postgresql.util.PSQLException: FATAL: database "aaa" does not exist Location: File: postinit.c, Routine:
InitPostgres,Line: 846 Server SQLState: 3D000       at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)      at
org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2538)      at
org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:122)      at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:227)      at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)      at
org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)      at
org.postgresql.Driver.makeConnection(Driver.java:431)      at org.postgresql.Driver.connect(Driver.java:247)       at
java.sql.DriverManager.getConnection(DriverManager.java:664)      at
java.sql.DriverManager.getConnection(DriverManager.java:247)      at org.hsqldb.lib.RCData.getConnection(Unknown
Source)      at org.hsqldb.cmdline.SqlTool.objectMain(Unknown Source)       at org.hsqldb.cmdline.SqlTool.main(Unknown
Source)

Failed to get a connection to 'jdbc:postgresql://localhost:5450,localhost:5451/aaa' as user "tunakawa".
Cause: FATAL: database "aaa" does not exist Location: File: postinit.c, Routine: InitPostgres, Line: 846 Server
SQLState:3D000
 
$

Regards
Takayuki Tsunakawa