Re: BUG #14247: COMMENT is restored on wrong database

On Wed, Jul 13, 2016 at 9:21 AM, <pgerber(at)tocco(dot)ch> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14247
> Logged by: Peter Gerber
> Email address: pgerber(at)tocco(dot)ch
> PostgreSQL version: 9.5.3
> Operating system: Linux
> Description:
>
> Comments on a databases are restored on the database named the same the
> dump
> was made from. For instance, if a dump is made from a database called
> 'source_db' and restored into 'target_db' the comment is restored on the
> wrong database, 'source_db'.
>
> How to reproduce:
> 1: CREATE DATABASE source_db;
> 2. COMMENT ON DATABASE source_db IS 'test';
> 3. pg_dump -Fc -d source_db -f dump;
> 4. COMMENT ON DATABASE source_db IS null;
> 5. CREATE DATABASE target_db;
> 6. pg_restore -d target_db dump
> 7. SELECT datname, description FROM pg_shdescription
> JOIN pg_database ON objoid = pg_database.oid
> WHERE datname in ('source_db', 'target_db');
> datname | description
> -----------+-------------
> source_db | test
> (1 row)
>
>
> Also, if pg_dump is done without -Fc it contains this line:
> COMMENT ON DATABASE dbrefactoring_new_210 IS 'test';
>

​I'd posit that attempting to issue the COMMENT command without the user
specifying "pg_restore --create" ​is the bug. pg_restore shouldn't go
about altering globals that it did not itself create. If --create is
specified you don't get to rename the database and the OP's problem cannot
happen.

pg_dump plain text should adhere to the same rule - the COMMENT command
should be omitted if pg_dump is not provided a "--create" flag.

Thus, if you want to rename the database it is your responsibility to
provide a valid comment for it. There is no way for pg_dump/pg_restore to
rename the database during the restoration procedure and adding logic to
them to detect when the user intended a rename and change the scripts
accordingly doesn't seem wise.

David J.