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

On Wed, Jul 13, 2016 at 9:21 AM, <pgerber@tocco.ch> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14247
> Logged by:          Peter Gerber
> Email address:      pgerber@tocco.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 =3D 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';
>

=E2=80=8BI'd posit that attempting to issue the COMMENT command without the=
 user
specifying "pg_restore --create" =E2=80=8Bis 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.