I'm currently working on Create/Alter/Drop Group statements. I have all
the framework set up, I can create empty groups and drop them, and create
all sorts of notices about unimplemented functionality.

First, the syntax I had in mind:

CREATE GROUP name [ WITH [ SYSID id ] [ USER name1, name2, ... ] ]
ALTER GROUP name WITH SYSID id      /* changes sysid */
ALTER GROUP name ADD USER name1, name2, ...
ALTER GROUP name DROP USER name1, name2, ...
DROP GROUP name

Please protest now or hold your peace for at least one release. :)


Here's a tricky problem:
=> insert into pg_group values ('one', 1, NULL);
=> create group two;
both create groups of identical fashion. The create group uses
heap_insert().

Now I do
template1=> alter group one add user foo;
NOTICE:  Cannot add users to a group, yet.
ALTER USER
/* OK */
template1=> alter group two add user foo;
AlterGroup: Group "two" does not exist.
/* Huh? */

This is caused by this statement:
if (!HeapTupleIsValid(
        SearchSysCacheTuple(GRONAME, PointerGetDatum(stmt->name), 0, 0, 0))
   )
{
	heap_close(pg_group_rel, AccessExclusiveLock);
		UserAbortTransactionBlock();
		elog(ERROR, "AlterGroup: Group \"%s\" does not exist.",
stmt->name);
	}


However:

template1=> select * from pg_group;
groname,grosysid,grolist
one,0,
two,1,

However however:

template1=> select * from pg_group where groname='two';
groname,grosysid,grolist
(0 rows)

BUT:

template1=> drop group two;
DROP GROUP
template1=> drop group two;
ERROR:  DropGroup: Group "two" does not exist.
as expected.

DropGroup does a normal heap_scan checking for the existence of the
groups. I'm not sure, is there some subtle binary off-by-one-bit problem,
are the strings encoded differently, etc.?

Interestingly, this similar statement works:

	tuple = SearchSysCacheTuple(SHADOWNAME, PointerGetDatum(stmt->user), 0, 0, 0);
	if (!HeapTupleIsValid(tuple))
	{
		heap_close(pg_shadow_rel, AccessExclusiveLock);
		UserAbortTransactionBlock();
		elog(ERROR, "AlterUser: user \"%s\" does not exist", stmt->user);
	}

Also, why can pg_group not be vacuumed? (pg_shadow can.) With all this
testing, mine is filling up.

Perhaps related, but just out of curiosity: Why is pg_group a system
relatation (pg_class.relkind='s')? Only three other ones have this
set: pg_variable, pg_log, and pg_xactlog.

Any help will be appreciated. I'll be back when I need to figure out the
arrays. :)

(Patch is included for those that don't have a clue what I'm talking about
but would like to find out anyway.)

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden
Mon, 13 Dec 1999 21:48:16 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Create Group
Peter Eisentraut <peter_e@gmx.net> writes:
> Now I do
> template1=> alter group one add user foo;
> NOTICE:  Cannot add users to a group, yet.
> ALTER USER
> /* OK */
> template1=> alter group two add user foo;
> AlterGroup: Group "two" does not exist.
> /* Huh? */

I'll bet you forgot to update the indexes on pg_group.  heap_insert is
not sufficient for a table that has indexes; you have to do a little
number that typically looks like (this example from the COMMENT code):

    if (RelationGetForm(description)->relhasindex) {
      Relation idescs[Num_pg_description_indices];
      
      CatalogOpenIndices(Num_pg_description_indices, 
			 Name_pg_description_indices, idescs);
      CatalogIndexInsert(idescs, Num_pg_description_indices, description, 
			 desctuple);
      CatalogCloseIndices(Num_pg_description_indices, idescs);
    }

> Also, why can pg_group not be vacuumed? (pg_shadow can.) With all this
> testing, mine is filling up.

> Perhaps related, but just out of curiosity: Why is pg_group a system
> relatation (pg_class.relkind='s')?

That seems wrong, wrong, wrong --- and it probably explains why VACUUM
won't touch it.  's' is for special relations not system relations, and
pg_group is not special.  I'm surprised it works at all...

			regards, tom lane

Mon, 13 Dec 1999 17:35:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Create Group 
On 1999-12-13, Tom Lane mentioned:

> > Also, why can pg_group not be vacuumed? (pg_shadow can.) With all this
> > testing, mine is filling up.
> 
> > Perhaps related, but just out of curiosity: Why is pg_group a system
> > relatation (pg_class.relkind='s')?
> 
> That seems wrong, wrong, wrong --- and it probably explains why VACUUM
> won't touch it.  's' is for special relations not system relations, and
> pg_group is not special.  I'm surprised it works at all...

NOTICE:  Vacuum: can not process index and certain system tables

Feel free to change this sooner rather than later because it also throws
off a few other things (e.g., psql and pg_dump probably). I couldn't even
find the place where this is specified in the catalogs. I really assume
this is an accident that has gone unnoticed because of the lack of usage.

Afterthought: The last claim seems to be supported by code fragments such
as this:

#define Natts_pg_group                  1
#define Anum_pg_group_groname   1
#define Anum_pg_group_grosysid  2
#define Anum_pg_group_grolist   3

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Tue, 14 Dec 1999 20:26:01 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Create Group 