Обсуждение: BUG #17111: Database created, cannot be created, but reported as inexist

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

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
DATABASEDatabase;" 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
expectedto
 
     * 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, "e, 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.