Message-ID: <gh-pgjdbc-pgjdbc-3390-c2393920423@github.com>
From: "SophiahHo (@SophiahHo)" <noreply+SophiahHo@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Fri, 04 Oct 2024 15:05:56 +0000
Subject: Re: [pgjdbc/pgjdbc] PR #3390: Fix PgDatabaseMetaData implementation of catalog as param and return value
In-Reply-To: <gh-pgjdbc-pgjdbc-3390@github.com>
References: <gh-pgjdbc-pgjdbc-3390@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: SophiahHo
X-GitHub-Comment-Id: 2393920423
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3390
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/pull/3390#issuecomment-2393920423
Content-Type: text/plain; charset=utf-8

Mapping the catalog name to the database name is already canonical in postgres itself, because when I `select pg_get_viewdef('information_schema.schemata', true);`, postgres returns the following view definition:
```sql
 SELECT current_database()::information_schema.sql_identifier AS catalog_name,
    n.nspname::information_schema.sql_identifier AS schema_name,
    u.rolname::information_schema.sql_identifier AS schema_owner,
    NULL::name::information_schema.sql_identifier AS default_character_set_catalog,
    NULL::name::information_schema.sql_identifier AS default_character_set_schema,
    NULL::name::information_schema.sql_identifier AS default_character_set_name,
    NULL::character varying::information_schema.character_data AS sql_path
   FROM pg_namespace n,
    pg_authid u
  WHERE n.nspowner = u.oid AND (pg_has_role(n.nspowner, 'USAGE'::text) OR has_schema_privilege(n.oid, 'CREATE, USAGE'::text));
```

An alternative to using `current_database()::information_schema.sql_identifier` for `TABLE_CATALOG` is to select `catalog_name` from the view `information_schema.schemata` to make the driver future-compatible with any design changes in postgres that would allow a postgres connection to access multiple databases. However, as you had alluded, the disadvantage would be the performance hit from the view selecting from `pg_authid`.