sql: set ApplicationName from JDBC

[rafiss]

The PGJDBC tests connect to the database with a connection string that looks like this: jdbc:postgresql://localhost:26257/defaultdb?ApplicationName=Driver Tests

Some of the tests then expect the application_name parameter to be Driver Tests. For example, see ParameterStatusTest.java in the pgjdbc repo.

It also seems like these don't work properly:

    con.setClientInfo("ApplicationName", "my app");
    assertEquals("my app", con.getClientInfo("ApplicationName"));

    s.execute("set application_name='" + appName + "'");
    assertEquals("application_name was set to " + appName + ", and it should be visible via "
        + "con.getClientInfo", appName, con.getClientInfo("ApplicationName"));

    Properties props = new Properties();
    props.put("ApplicationName", "my app");
    con.setClientInfo(props);
    assertEquals("my app", con.getClientInfo("ApplicationName"));

[rafiss]

This might actually be a bug, since our docs say this should work already: https://www.cockroachlabs.com/docs/stable/connection-parameters.html

[otan]

This looks like a race of some sort, unsure of how to proceed from here.

Looks like the application name is "set" in pgjdbc via some listening connection thing here: https://github.com/pgjdbc/pgjdbc/blob/504bb316b91fdbc6506a2e9870453fb75fbbb083/pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java#L2652

But cockroach sends application_name on this protocol before pgjdbc sets the application name, meaning no application name has been previously sends https://sourcegraph.com/github.com/cockroachdb/cockroach@dcddfec777c9c799ed5717e8f14899cfdb03b6e6/-/blob/pkg/sql/pgwire/conn.go#L613:3. I'm unsure how this conn status thing works and what it's meant to look like - any thoughts / documentation?

[jordanlewis]

The Postgres wire protocol is documented here: https://www.postgresql.org/docs/9.3/protocol.html

I'm also unsure about what's going wrong here. I guess we're sending things in the wrong order. @mjibson would you be able to take some time to get @otan up to speed on your pgwire comparison tooling and help him figure out how to find what's making the difference here?

[otan]

I believe the issue is actually that we don't send a notification that the application name changed over pgwire :o

[rafiss]

I think there is still some support we need to add. Some of the tests in ParameterStatusTest.java are failing. (you can repro by running mvn -Dtest=ParameterStatusTest test from the pgjdbc repo.)

The test connects with the URL jdbc:postgresql://localhost:26257/defaultdb?ApplicationName=Driver Tests and some tests fail on the following assertion (i.e., transactionalParametersCommit, transactionalParametersRollback, parameterMapIsView, transactionalParametersAutocommit).:

    // Initial value assigned by TestUtil
    Assert.assertEquals("Driver Tests", ((PGConnection)con).getParameterStatus("application_name"));

[otan]

i am not seeing this - instead i see below (which has an issue already)

transactionalParametersAutocommit(org.postgresql.test.jdbc2.ParameterStatusTest)  Time elapsed: 0.01 sec  <<< ERROR!
org.postgresql.util.PSQLException: ERROR: at or near "application_name": syntax error: unimplemented: this syntax
  Detail: source SQL:
SET LOCAL application_name = 'pgjdbc_ParameterStatusTestLocal'
          ^
  Hint: You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/32562
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2503)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2238)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)

and this which is a separate issue

expectedInitialParameters(org.postgresql.test.jdbc2.ParameterStatusTest)  Time elapsed: 0.009 sec  <<< ERROR!
org.postgresql.util.PSQLException: ERROR: invalid value for parameter "TimeZone": "GMT-08:00"
  Detail: unknown time zone GMT-08:00
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2503)
	at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2615)
	at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:134)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:250)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:458)
	at org.postgresql.Driver.connect(Driver.java:260)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.postgresql.test.TestUtil.openDB(TestUtil.java:354)
	at org.postgresql.test.TestUtil.openDB(TestUtil.java:311)
	at org.postgresql.test.jdbc2.ParameterStatusTest.expectedInitialParameters(ParameterStatusTest.java:42)

[otan]

since the above are both separate, can you confirm as such (maybe rebase masters and rebuild?) and if so, close this one and file a separate issue for the TimeZone cases?

SET LOCAL seems to already have an issue (#32562)

[rafiss]

Sorry for the confusion... I also can't repro.

The timezone one also has an issue already, as well as a PR waiting for review #41776