Re: Bug #880: COMMENT ON DATABASE depends on current database

Marcin Kaminski (alternative(at)maxiu(dot)com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
COMMENT ON DATABASE depends on current database

Long Description
PostgreSQL has mechanism for commenting databases.
Database comments can by read by obj_description(oid),
psql \l+ command use it. Database comments should be
global, but they are not, when we do \l+ on one database,
and then on other, results will be different.
I consider it is a bug, database is global object (You
can connect to it from any database) but their comments
are not.

Sample Code

No file was uploaded with this report

I've upgraded my DB to 7.3.1 from 7.2. The following update causes the SEQ
SCAN instead of INDEX SCAN (in 7.2 there was no bug like that):

create table machines(
i_sprzetx int,
mod char(10),
type char(30)
);

create table sprzetx(
rowid int,
ident char(50)
);

create index i_sprzetx on sprzetx(ident);

inserts....

vacuum analyze sprzetx;

update machines set i_sprzetx=(
select g.rowid from sprzetx g
where g.ident=( trim(f.type) || trim(f.mod) )
);

When optimizer meets ANY expression int the query clause switches from INDEX
to SEQUENTIAL disregarding the expression is constant for row.

Helps ALTER COLUMN to machines, UPDATE to TRIM || TRIM but sure it's not
solution.

----------------------------------------------------------------------
Czego nie wykorzystujesz, procentuje! mBIZNES Konto i mBIZNES MAX.
Efektywne oprocentowanie 5.64% i 6.59%. > http://link.interia.pl/f16c0

"Ace" <a_s(at)poczta(dot)fm> writes:
> I've upgraded my DB to 7.3.1 from 7.2. The following update causes the SEQ
> SCAN instead of INDEX SCAN (in 7.2 there was no bug like that):

I don't believe that. No version of Postgres would have generated an
index scan on this query, because you've got a type mismatch: ident
is declared char(50) but the result of the trim()||trim() expression
will be of type text.

I'd recommend changing all the column datatypes from char(N) to text.
Then you could get rid of the trim() calls, and save yourself a bunch
of disk space too. Those trailing spaces aren't free.

regards, tom lane

> Long Description PostgreSQL has mechanism for commenting databases.
> Database comments can by read by obj_description(oid), psql \l+ command
> use it. Database comments should be global, but they are not, when we do
> \l+ on one database, and then on other, results will be different. I
> consider it is a bug, database is global object (You can connect to it
> from any database) but their comments are not.
>

I too consider it as a bug. But why no response? I hope someone is
hearing.

regards,
bhuvaneswaran

On Fri, 2003-01-24 at 06:16, Bhuvan A wrote:
> > Long Description PostgreSQL has mechanism for commenting databases.
> > Database comments can by read by obj_description(oid), psql \l+ command
> > use it. Database comments should be global, but they are not, when we do
> > \l+ on one database, and then on other, results will be different. I
> > consider it is a bug, database is global object (You can connect to it
> > from any database) but their comments are not.
>
> I too consider it as a bug. But why no response? I hope someone is
> hearing.

It has been discussed in the past:

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&threadm=04f201c1e7cc%243479b2a0%248001a8c0%40jester&rnum=1&prev=/groups%3Fq%3Dcomment.on.database%2Bgroup:comp.databases.postgresql.hackers%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26safe%3Doff%26selm%3D04f201c1e7cc%25243479b2a0%25248001a8c0%2540jester%26rnum%3D1
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Bhuvan A wrote:
> > Long Description PostgreSQL has mechanism for commenting databases.
> > Database comments can by read by obj_description(oid), psql \l+ command
> > use it. Database comments should be global, but they are not, when we do
> > \l+ on one database, and then on other, results will be different. I
> > consider it is a bug, database is global object (You can connect to it
> > from any database) but their comments are not.
> >
>
> I too consider it as a bug. But why no response? I hope someone is
> hearing.

You can only create a comment on the current database:

test=> comment on database template1 is 'xx';
ERROR: Database comments may only be applied to the current database

Do we have psql -l to connect to all the databases to collect comments?
I guess we could _try_ to connect to as many databases as possible, but
it seems a little overly complex to me. What do others think?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian writes:

> Do we have psql -l to connect to all the databases to collect comments?
> I guess we could _try_ to connect to as many databases as possible, but
> it seems a little overly complex to me. What do others think?

I tend to think that the functionality to give comments to databases
should either be redone to work right (for example by storing the comment
in a global table (but think about the encoding problems)) or be ripped
out. Right now the feature to give a comment to a database you presumably
already know (since you connected to it) does not seem to justify the
confusion it causes.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > Do we have psql -l to connect to all the databases to collect comments?
> > I guess we could _try_ to connect to as many databases as possible, but
> > it seems a little overly complex to me. What do others think?
>
> I tend to think that the functionality to give comments to databases
> should either be redone to work right (for example by storing the comment
> in a global table (but think about the encoding problems)) or be ripped
> out. Right now the feature to give a comment to a database you presumably
> already know (since you connected to it) does not seem to justify the
> confusion it causes.

Good analysis. Is removal actually the best solution?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> > I tend to think that the functionality to give comments to databases
> > should either be redone to work right (for example by storing the comment
> > in a global table (but think about the encoding problems)) or be ripped
> > out. Right now the feature to give a comment to a database you presumably
> > already know (since you connected to it) does not seem to justify the
> > confusion it causes.
>
> Good analysis. Is removal actually the best solution?

Front-ends like pg_admin actually make pretty good use out of it.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc