Thread: COMMENT ON [GROUP/USER]

COMMENT ON [GROUP/USER]

From
"Clark C. Evans"
Date:
It would be wonderful to be able to create comments
on users and groups.  In particular, I need a place
to store the user's name.  Yes, I could make a user
table, but that seems overkill as all of the other
aspects of a user are already in the metadata.

Best,

Clark
-- 
Clark C. Evans                     Prometheus Research, LLC
Chief Technology Officer           Turning Data Into Knowledge
cce@prometheusresearch.com         www.prometheusresearch.com
(main) 203.777.2550                (cell) 203.444.0557


Re: COMMENT ON [GROUP/USER]

From
Tom Lane
Date:
"Clark C. Evans" <cce@clarkevans.com> writes:
> It would be wonderful to be able to create comments
> on users and groups.  In particular, I need a place
> to store the user's name.  Yes, I could make a user
> table, but that seems overkill as all of the other
> aspects of a user are already in the metadata.

This seems like a good idea, but I'd recommend leaving it as a TODO
until after we finish the planned revisions for SQL role support.
(Peter E. has made noises about working on that, but I dunno what
his timeframe for it is.)  In particular, it's not clear that there
will still be a hard and fast separation between "users" and "groups"
after that happens, so it seems premature to wire such an assumption
into the syntax.

Another small problem that would have to be faced is that users and
groups don't have OIDs.  We could physically get away with a type-cheat
of storing their integer IDs into pg_description instead, but I'm worried
that would create issues of its own.
        regards, tom lane


Re: COMMENT ON [GROUP/USER]

From
Bruce Momjian
Date:
Tom Lane wrote:
> "Clark C. Evans" <cce@clarkevans.com> writes:
> > It would be wonderful to be able to create comments
> > on users and groups.  In particular, I need a place
> > to store the user's name.  Yes, I could make a user
> > table, but that seems overkill as all of the other
> > aspects of a user are already in the metadata.
> 
> This seems like a good idea, but I'd recommend leaving it as a TODO
> until after we finish the planned revisions for SQL role support.
> (Peter E. has made noises about working on that, but I dunno what
> his timeframe for it is.)  In particular, it's not clear that there
> will still be a hard and fast separation between "users" and "groups"
> after that happens, so it seems premature to wire such an assumption
> into the syntax.
> 
> Another small problem that would have to be faced is that users and
> groups don't have OIDs.  We could physically get away with a type-cheat
> of storing their integer IDs into pg_description instead, but I'm worried
> that would create issues of its own.

Another problem is that pg_description is per-database, while
pg_user/group are global for all databases.

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


Re: COMMENT ON [GROUP/USER]

From
Andrew Dunstan
Date:
Bruce Momjian wrote:

>Another problem is that pg_description is per-database, while
>pg_user/group are global for all databases.
>
>  
>
databases are also per cluster, but we have comments on those.

Could we keep the user/group comments in those tables instead of in 
pg_description?

cheers

andrew


Re: COMMENT ON [GROUP/USER]

From
"Dave Page"
Date:
It's rumoured that Andrew Dunstan once said:
> Bruce Momjian wrote:
>
>>Another problem is that pg_description is per-database, while
>>pg_user/group are global for all databases.
>>
>>
>>
> databases are also per cluster, but we have comments on those.
>
> Could we keep the user/group comments in those tables instead of in
> pg_description?

Ahh, just like they used to live in pg_language.lancompiler for languages :-)

Regards, Dave





Re: COMMENT ON [GROUP/USER]

From
Mike Mascari
Date:
Andrew Dunstan wrote:
> Bruce Momjian wrote:
> 
>> Another problem is that pg_description is per-database, while
>> pg_user/group are global for all databases.
>>
>>  
>>
> databases are also per cluster, but we have comments on those.
> 
> Could we keep the user/group comments in those tables instead of in 
> pg_description?

The comments are stored only in the database's pg_description where 
the COMMENT ON took place. This caused dump/reload problems. I 
believe Rod Taylor added the new warning:

[estore@lexus] select count(*) from pg_description; count
-------  1541
(1 row)

[estore@lexus] COMMENT ON DATABASE test IS 'Hello';
WARNING:  database comments may only be applied to the current database
COMMENT

[estore@lexus] select count(*) from pg_description; count
-------  1541
(1 row)

[estore@lexus] COMMENT ON DATABASE estore IS 'A good comment';
COMMENT

[estore@lexus] select count(*) from pg_description; count
-------  1542
(1 row)

[test@lexus] select count(*) from pg_description; count
-------  1541
(1 row)


Mike Mascari



Re: COMMENT ON [GROUP/USER]

From
Bruce Momjian
Date:
This doesn't look good.  If we throw a WARNING, why do we not insert
anything into pg_description.  Seems we should throw an error, or do the
insert with a warning.

---------------------------------------------------------------------------

Mike Mascari wrote:
> Andrew Dunstan wrote:
> > Bruce Momjian wrote:
> > 
> >> Another problem is that pg_description is per-database, while
> >> pg_user/group are global for all databases.
> >>
> >>  
> >>
> > databases are also per cluster, but we have comments on those.
> > 
> > Could we keep the user/group comments in those tables instead of in 
> > pg_description?
> 
> The comments are stored only in the database's pg_description where 
> the COMMENT ON took place. This caused dump/reload problems. I 
> believe Rod Taylor added the new warning:
> 
> [estore@lexus] select count(*) from pg_description;
>   count
> -------
>    1541
> (1 row)
> 
> [estore@lexus] COMMENT ON DATABASE test IS 'Hello';
> WARNING:  database comments may only be applied to the current database
> COMMENT
> 
> [estore@lexus] select count(*) from pg_description;
>   count
> -------
>    1541
> (1 row)
> 
> [estore@lexus] COMMENT ON DATABASE estore IS 'A good comment';
> COMMENT
> 
> [estore@lexus] select count(*) from pg_description;
>   count
> -------
>    1542
> (1 row)
> 
> [test@lexus] select count(*) from pg_description;
>   count
> -------
>    1541
> (1 row)
> 
> 
> Mike Mascari
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

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


Re: COMMENT ON [GROUP/USER]

From
Rod Taylor
Date:
On Mon, 2004-03-08 at 14:46, Andrew Dunstan wrote:
> Bruce Momjian wrote:
> 
> >Another problem is that pg_description is per-database, while
> >pg_user/group are global for all databases.
> >
> >  
> >
> databases are also per cluster, but we have comments on those.
> 
> Could we keep the user/group comments in those tables instead of in 
> pg_description?

Comments longer than ~7k would need a toast table. At the moment, toast
tables don't work on a global basis.




Re: COMMENT ON [GROUP/USER]

From
Mike Mascari
Date:
Bruce Momjian wrote:

> This doesn't look good.  If we throw a WARNING, why do we not insert
> anything into pg_description.  Seems we should throw an error, or do the
> insert with a warning.

It essentially makes the behavior deprecated and allows dumps to be 
restored properly (without the extra-database comments.) Here's a 
thread on the topic:


http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=bf1obi%24u7k%241%40FreeBSD.csie.NCTU.edu.tw&rnum=7&prev=/groups%3Fq%3D%2522COMMENT%2BON%2BDATABASE%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

I don't know if Rod has plans to change attempts to COMMENT ON 
non-local databases to an ERROR in 7.5 or not. It was my fault from 
the beginning - but once I'd implemented COMMENT ON for tables and 
columns I just couldn't stop... :-)

Mike Mascari

> Mike Mascari wrote:
..
>>The comments are stored only in the database's pg_description where 
>>the COMMENT ON took place. This caused dump/reload problems. I 
>>believe Rod Taylor added the new warning:
>>
>>[estore@lexus] select count(*) from pg_description;
>>  count
>>-------
>>   1541
>>(1 row)
>>
>>[estore@lexus] COMMENT ON DATABASE test IS 'Hello';
>>WARNING:  database comments may only be applied to the current database
>>COMMENT
>>
>>[estore@lexus] select count(*) from pg_description;
>>  count
>>-------
>>   1541
>>(1 row)



Re: COMMENT ON [GROUP/USER]

From
Andrew Dunstan
Date:
Rod Taylor wrote:

>On Mon, 2004-03-08 at 14:46, Andrew Dunstan wrote:
>  
>
>>Bruce Momjian wrote:
>>
>>    
>>
>>>Another problem is that pg_description is per-database, while
>>>pg_user/group are global for all databases.
>>>
>>> 
>>>
>>>      
>>>
>>databases are also per cluster, but we have comments on those.
>>
>>Could we keep the user/group comments in those tables instead of in 
>>pg_description?
>>    
>>
>
>Comments longer than ~7k would need a toast table. At the moment, toast
>tables don't work on a global basis.
>
>  
>

Well, presumably we don't want to keep their life story ;-)

I was just thinking out loud I guess - I see there are wrinkles I hadn't 
considered.

cheers

andrew


Re: COMMENT ON [GROUP/USER]

From
Tom Lane
Date:
Rod Taylor <pg@rbt.ca> writes:
> Comments longer than ~7k would need a toast table. At the moment, toast
> tables don't work on a global basis.

Sure they do ... in fact, all the shared catalogs have one.

I think the idea of putting comments directly into pg_shadow and friends
is too icky to consider, though.  If we really wanted to support this
stuff then we'd make *one* shared table that is just like
pg_description, but is used for shared objects.
        regards, tom lane


Re: COMMENT ON [GROUP/USER]

From
Bruce Momjian
Date:
Rod Taylor wrote:
-- Start of PGP signed section.
> On Mon, 2004-03-08 at 15:46, Bruce Momjian wrote:
> > This doesn't look good.  If we throw a WARNING, why do we not insert
> > anything into pg_description.  Seems we should throw an error, or do the
> > insert with a warning.
> 
> It used to be an error, but that had problems with pg_restore (it
> doesn't like errors). When it was changed to a warning, I think it was
> still reasonable to leave it out of pg_description in anticipation of
> the syntax being changed to:
> 
> COMMENT ON DATABASE IS 'Hello';
> 
> The above always applies the comment to the current database.

OK, I added a comment to the C code:
       ereport(WARNING,    /* throw just a warning so pg_restore doesn't fail */

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


Re: COMMENT ON [GROUP/USER]

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This doesn't look good.  If we throw a WARNING, why do we not insert
> anything into pg_description.  Seems we should throw an error, or do the
> insert with a warning.

Throwing an error breaks existing pg_dump files.  Doing the insertion is
simply wrong: it will allow the former breakage to be perpetuated
forward by dump/reload.  Thus the current behavior is an unfortunate but
necessary compromise ... at least until we have better support for
comments on databases.
        regards, tom lane


Re: COMMENT ON [GROUP/USER]

From
Alvaro Herrera Munoz
Date:
On Mon, Mar 08, 2004 at 04:24:30PM -0500, Tom Lane wrote:

> I think the idea of putting comments directly into pg_shadow and friends
> is too icky to consider, though.  If we really wanted to support this
> stuff then we'd make *one* shared table that is just like
> pg_description, but is used for shared objects.

Hey, a pg_sdescription could be followed by a pg_sdepend to hold
dependency information for global objects (users ATM) ...

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Las mujeres son como hondas:  mientras m�s resistencia tienen,m�s lejos puedes llegar con ellas"  (Jonas Nightingale,
Leapof Faith)