sql: support database-level session variable defaults

[knz]

Drupal does the following:

ALTER DATABASE "drupal" SET bytea_output = 'escape';

which is mean to set a database-level default setting for session vars.

See https://www.postgresql.org/docs/10/static/sql-alterdatabase.html:

The remaining forms change the session default for a run-time configuration variable for a PostgreSQL database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value.

Also, as an addition to #21151, users can have database-level session variable defaults, with the ALTER USER <username> IN DATABASE <database_name> SET ... syntax. See https://www.postgresql.org/docs/current/sql-alteruser.html

The information is populated in pg_db_role_setting: https://www.postgresql.org/docs/current/catalog-pg-db-role-setting.html

Epic CRDB-2507

[bdarnell]

This is slightly subtle for us to support because our "database" is a postgres "schema". In postgres, you're always connected to exactly one database and can't refer to any others, so it's unambiguous which settings should apply. In cockroach, the database you're connected to is just a default namespace, and it's fine to connect to one database (perhaps default) and exclusively refer to tables from another. For bytea_output, I guess this would have to be associated with the connection DB (although this is a really strange place to put that setting). Is that true of all settings that can be specified at the database level, or would some settings need to apply to all tables within that database?

[knz]

Ben in pg too you can make cross database requests. This feature is also super well defined, the setting is loaded based on the db in the connection string. Check out the linked doc.

…

-- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[knz]

still current in 21.1

[mauza]

Hey I'm trying to use Medusa in a K8s environment. I already have cockroach installed and have medusa talking to cockroach. The problem I'm running into is:

error: error: ALTER COLUMN TYPE from refund_reason_enum to refund_reason_enum is only supported experimentally
query: ROLLBACK
QueryFailedError: ALTER COLUMN TYPE from refund_reason_enum to refund_reason_enum is only supported experimentally
    at PostgresQueryRunner.query (/src/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  query: 'ALTER TABLE "refund" ALTER COLUMN "reason" TYPE "refund_reason_enum" USING "reason"::"text"::"refund_reason_enum"',
  parameters: undefined,
  driverError: error: ALTER COLUMN TYPE from refund_reason_enum to refund_reason_enum is only supported experimentally
      at /src/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/src/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:178:25) {
    length: 343,
    severity: 'ERROR',
    code: 'XCEXF',
    detail: undefined,
    hint: 'See: https://go.crdb.dev/issue-v/49329/v23.1\n' +
      '--\n' +
      'you can enable alter column type general support by running `SET enable_experimental_alter_column_type_general = true`',

So I'd like to set a session variable but medusa doesn't expose sessions variables (as far as I can tell) in the database_url or elsewhere. I was looking for how to set database wide or user wide sessions variables so I could set the variable outside of the app running. I guess my problem would be solved either way. Make that setting non experimental or allow database or user session variables.

[knz]

@mauza have you seen #69067? You can now use ALTER DATABASE yourdb SET ... which should help here.