Thread: Why an array in pg_group?

Hi,

Is there any reason why the grolist field in the table pg_group is 
implemented as an array and not as a separate table?

According to the documentation:

<quote source="Postgresql 7.2 User Manual, chapter 6 near the end">
Arrays are not sets; using arrays in the manner described in the previous 
paragraph is often a sign of database misdesign.
</quote>

I have trouble implementing a way to easily check whether a user is part 
of a group. (I use Apache::AuthDBI to implement authentication and wanted 
to make a view with columns username, userid , groupname. And installing 
the contrib/array give's me a postgresql that is different from all the 
others :-(


-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________

Reinoud van Leeuwen kirjutas K, 20.11.2002 kell 17:03:
> Hi,
> 
> Is there any reason why the grolist field in the table pg_group is 
> implemented as an array and not as a separate table?

most likely for performance reasons.

> According to the documentation:
> 
> <quote source="Postgresql 7.2 User Manual, chapter 6 near the end">
> Arrays are not sets; using arrays in the manner described in the previous 
> paragraph is often a sign of database misdesign.
> </quote>
> 
> I have trouble implementing a way to easily check whether a user is part 
> of a group. (I use Apache::AuthDBI to implement authentication and wanted 
> to make a view with columns username, userid , groupname. And installing 
> the contrib/array give's me a postgresql that is different from all the 
> others :-(

not from those who also have installed contrib/array ;)

but you should actually be using contrib/intagg (and perhaps contrib
intarray) for performance reasons ;)

-- 
Hannu Krosing <hannu@tm.ee>

On Tuesday 26 November 2002 09:05 pm, Hannu Krosing wrote:
> Reinoud van Leeuwen kirjutas K, 20.11.2002 kell 17:03:
> > Hi,
> >
> > Is there any reason why the grolist field in the table pg_group is
> > implemented as an array and not as a separate table?
>
> most likely for performance reasons.
>
> > According to the documentation:
> >
> > <quote source="Postgresql 7.2 User Manual, chapter 6 near the end">
> > Arrays are not sets; using arrays in the manner described in the previous
> > paragraph is often a sign of database misdesign.
> > </quote>
> >
> > I have trouble implementing a way to easily check whether a user is part
> > of a group. (I use Apache::AuthDBI to implement authentication and wanted
> > to make a view with columns username, userid , groupname. And installing
> > the contrib/array give's me a postgresql that is different from all the
> > others :-(
>
> not from those who also have installed contrib/array ;)
>
> but you should actually be using contrib/intagg (and perhaps contrib
> intarray) for performance reasons ;)
Can You make syntax (operator or function) like :

scalar integer IN array integers
for join scalar and array fileds ?

in base PostgreSQL ?

regards
Haris Peco