Obtain the PostgREST version through SQL

[steve-chavez]

It would be nice to obtain the version like this:

select distinct application_name from pg_stat_activity where application_name like '%postgrest%';

 application_name
------------------
postgrest-10.1.2

The fallback_application_name fits this use case exactly, we can set it and still allow users to override it with application_name.

To implement this, we'd have to add ?fallback_application_name=val to configDbUri here:

postgrest/src/PostgREST/AppState.hs

Line 100 in 09249f9

SQL.acquire configDbPoolSize timeoutMilliseconds $ toUtf8 configDbUri

Where val would be the current postgREST version.

The ? should be added conditionally. If the uri already contains ?, then add &fallback_application_name=val else add ?fallback_application_name=val.

---

This function could be used for testing:

CREATE FUNCTION test.get_version() RETURNS pg_stat_activity.application_name%TYPE
AS $$
  select distinct application_name from pg_stat_activity where application_name like '%postgrest%';
$$ LANGUAGE sql;

[steve-chavez]

Just noted there's a standard PGOPTIONS env var.

We could use that to avoid parsing the connection URI.

[steve-chavez]

It's weird but PGOPTIONS doesn't work with fallback_application_name.

## works fine
PGOPTIONS="-c application_name=postgrest" postgrest-with-postgresql-15 postgrest-run

## fails
PGOPTIONS="-c fallback_application_name='postgrest'" postgrest-with-postgresql-15 postgrest-run
createuser: error: connection to server on socket "/run/user/1000/postgrest/postgrest-with-postgresql-15-g3p/socket/.s.PGSQL.5432" failed: FATAL:  no pg_hba.conf entry for host "[local]", user
 "postgres", database "template1", no encryption

Seems doing createuser inside the nix script was hiding the error:

postgrest-with-postgresql-15 postgrest-run
postgrest-with-postgresql-15: You can connect with: psql 'postgres:///postgres?host=/run/user/1000/postgrest/postgrest-with-postgresql-15-Gnc/socket' -U postgres
postgrest-with-postgresql-15: You can tail the logs with: tail -f /run/user/1000/postgrest/postgrest-with-postgresql-15-Gnc/db.log
07/Jun/2023:14:57:28 -0500: Attempting to connect to the database...
07/Jun/2023:14:57:28 -0500: {"code":"PGRST000","details":"connection to server on socket \"/run/user/1000/postgrest/postgrest-with-postgresql-15-Gnc/socket/.s.PGSQL.5432\" failed: FATAL:  unre
cognized configuration parameter \"fallback_application_name\"\n","hint":null,"message":"Database connection error. Retrying the connection."}

FATAL: unrecognized configuration parameter \"fallback_application_name\". So strange.

[wolfgangwalther]

fallback_application_name is just a libpq thing. Everything that you put into PGOPTIONS will be sent to the server on connection start. But there is no configuration setting on the server called fallback_application_name:

postgres=# SET fallback_application_name TO 'postgrest';
ERROR:  unrecognized configuration parameter "fallback_application_name"

The ? should be added conditionally. If the uri already contains ?, then add &fallback_application_name=val else add ?fallback_application_name=val.

This seems to be the correct way of doing it.