Thread: Comments on Database Broken
Patricia Holben (pholben@greatbridge.com) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description Comments on Database Broken Long Description When comments are added to a database (testdb in this example) (either at the point of creation or using the "comment ondatabase ..." command) they may be stored either at the system level or within the individual database storage area. Dependingon how the user is connected, the "\l+" command may show the system comment on testdb (connected to template1 forexample) OR the self-applied comment on testdb (connected to testdb) OR the local comment on testdb (connected to a differentdb which has made comments on what the other dbs are). When a pg_dumpall command is executed, the dbs are dropped,and then the dump file is reloaded, only the self-applied comments exist anymore. Mainly this is just confusing. Maybe they should only exist in the system area but be accessible to all. Sample Code No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
> When comments are added to a database (testdb in this example) (either
> at the point of creation or using the "comment on database ..."
> command) they may be stored either at the system level or within the
> individual database storage area.
There is no such thing as comments "stored at the system level".
Comments live in pg_description which is a per-database table.
I think you are confusing the behavior of comments stored in template1
(which will get copied to subsequently-created databases) with a
true installation-wide table.
We could theoretically answer this by making a pg_global_description
table that is installation-wide like pg_database, but I'm not convinced
that it's worth worrying about. I'm not even convinced that the
behavior is wrong: why shouldn't each DB be able to have its own comment
on the other DBs?
regards, tom lane
Patricia Holben <pholben@greatbridge.com> writes:
> I only logged it today because I just
> realized that they didn't all come back after the dump/restore.
Hmm ... yeah, you're right, because pg_dump dumps comments along with
the objects they refer to --- and a database is not an object within
any other database.
The same would probably happen to comments on users and groups (do we
support those?).
Not clear to me if this is fixable, or worth fixing, within the
separate-dump-per-database framework of pg_dump. Philip, any thoughts?
regards, tom lane
Good analysis. Database comments are particularly tricky because pg_description exists in every database, but is unique. To fix this, we would need a global pg_description table that would be used _just_ for database comments. > Patricia Holben (pholben@greatbridge.com) reports a bug with a > severity of 3 The lower the number the more severe it is. > > Short Description Comments on Database Broken > > Long Description When comments are added to a database (testdb > in this example) (either at the point of creation or using the > "comment on database ..." command) they may be stored either at > the system level or within the individual database storage area. > Depending on how the user is connected, the "\l+" command may > show the system comment on testdb (connected to template1 for > example) OR the self-applied comment on testdb (connected to > testdb) OR the local comment on testdb (connected to a different > db which has made comments on what the other dbs are). When a > pg_dumpall command is executed, the dbs are dropped, and then > the dump file is reloaded, only the self-applied comments exist > anymore. Mainly this is just confusing. Maybe they should only > exist in the system area but be accessible to all. > > Sample Code > > > No file was uploaded with this report > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an > appropriate subscribe-nomail command to majordomo@postgresql.org > so that your message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026