BUG #17111: Database created, cannot be created, but reported as inexist

Hello

Please take a look at the register of names.

FATAL: database "Database" does not exist
ERROR: database "database" already exists

According SQL syntax https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS "DROP DATABASE Database;" means "DROP DATABASE database;"

regards, Sergei

On Fri, 16 Jul 2021 at 06:04, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17111
Logged by: Bernaridho Hutabarat
Email address: bernaridho@outlook.co.id
PostgreSQL version: 12.7
Operating system: CentOS
Description:

postgres=# \c Database
FATAL: database "Database" does not exist
Previous connection kept
postgres=# CREATE DATABASE Database;
ERROR: database "database" already exists
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# CREATE DATABASE Database OWNER ServiceUser1;
CREATE DATABASE
postgres=# \c Database;
FATAL: database "Database" does not exist
Previous connection kept
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# CREATE DATABASE Database OWNER postgres;
CREATE DATABASE
postgres=# \c Database;
FATAL: database "Database" does not exist
Previous connection kept
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# \q

This might be the \c command doesn't convert "Database" to "database",
OTOH, the CREATE DATABASE & DROP DATABASE convert "Database" to "database" by default.

Should we convert the string to lower case read_connect_arg() if it doesn't quoted?

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

On Fri, 16 Jul 2021 at 17:11, Japin Li <japinli@hotmail.com> wrote:

On Fri, 16 Jul 2021 at 06:04, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17111
Logged by: Bernaridho Hutabarat
Email address: bernaridho@outlook.co.id
PostgreSQL version: 12.7
Operating system: CentOS
Description:

postgres=# \c Database
FATAL: database "Database" does not exist
Previous connection kept
postgres=# CREATE DATABASE Database;
ERROR: database "database" already exists
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# CREATE DATABASE Database OWNER ServiceUser1;
CREATE DATABASE
postgres=# \c Database;
FATAL: database "Database" does not exist
Previous connection kept
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# CREATE DATABASE Database OWNER postgres;
CREATE DATABASE
postgres=# \c Database;
FATAL: database "Database" does not exist
Previous connection kept
postgres=# DROP DATABASE Database;
DROP DATABASE
postgres=# \q

This might be the \c command doesn't convert "Database" to "database",
OTOH, the CREATE DATABASE & DROP DATABASE convert "Database" to "database" by default.

Should we convert the string to lower case read_connect_arg() if it doesn't quoted?

I find read_connect_arg() has following code:

/*
* Ideally we should treat the arguments as SQL identifiers. But for
* backwards compatibility with 7.2 and older pg_dump files, we have to
* take unquoted arguments verbatim (don't downcase them). For now,
* double-quoted arguments may be stripped of double quotes (as if SQL * identifiers). By 7.4 or so, pg_dump files can be expected to
* double-quote all mixed-case \connect arguments, and then we can get rid
* of OT_SQLIDHACK.
*/
result = psql_scan_slash_option(scan_state, OT_SQLIDHACK, &quote, true);

IIUC, this code is just for compatibility with 7.2 or older. If yes, is it
necessary for us to be compatible with 7.2 or older?

I try to change OT_SQLIDHACK to OT_SQLID, and all test case passed.

Can we remove the code for compatibility with 7.2 or older?

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

Japin Li <japinli@hotmail.com> writes:

Can we remove the code for compatibility with 7.2 or older?

I'm kind of inclined not to, because most other ways that you can
specify a database name for a connection also don't downcase.

$ psql Postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5440" failed: FATAL: database "Postgres" does not exist

$ psql postgres
...
postgres=# \c "dbname=Postgres"
connection to server on socket "/tmp/.s.PGSQL.5440" failed: FATAL: database "Postgres" does not exist
Previous connection kept

This largely stems from an ancient decision that we shouldn't
auto-downcase names coming from a program's command line.
The interaction of SQL and shell quoting conventions is messy
enough that specifying a non-lower-case database, user, etc name
would be pretty annoying if we did do that.

regards, tom lane

On Sat, 17 Jul 2021 at 01:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Japin Li <japinli@hotmail.com> writes:

Can we remove the code for compatibility with 7.2 or older?

I'm kind of inclined not to, because most other ways that you can
specify a database name for a connection also don't downcase.

$ psql Postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5440" failed: FATAL: database "Postgres" does not exist

$ psql postgres
...
postgres=# \c "dbname=Postgres"
connection to server on socket "/tmp/.s.PGSQL.5440" failed: FATAL: database "Postgres" does not exist
Previous connection kept

This largely stems from an ancient decision that we shouldn't
auto-downcase names coming from a program's command line.
The interaction of SQL and shell quoting conventions is messy
enough that specifying a non-lower-case database, user, etc name
would be pretty annoying if we did do that.

Thanks for your explanation.

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.