Re: Catalogs design question
Yes, we inherited these arrays from Berkeley and haven't had any need to
remove them. Are you trying to do things that the other interfaces like
ODBC and JDBC don't handle?
The group array is a hack but the pg_proc array would be hard to replace
becauseit acts as part of the unique key used for cache lookups.
---------------------------------------------------------------------------
Hello all!!
I'm developer of a interface for PostgreSQL for the Borland Kylix
and Delphi tools (http://www.vitavoom.com). I've run into the following
problems with catalogs:- pg_group: the grolist field is an array. How can I make a query
that tell me the usernames of a group ?
- pg_proc: the proargtypes field is an array. How can I make a query
that will link those types to the pg_types catalog ???This catalog design seems a very crude hack to make the things
working for me. Can't those relations be separated in another table ? Or
maybe a function that can search for a value in array, and make a wroking
reference for an array
element in a relation (something like "select typname from pg_type, pg_group
where oid
in grolist").
I also quote the PotgreSQL user manual
(http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html):"Tip: Arrays are not lists; using arrays in the manner described in the
previous paragraph is often a sign of database misdesign. The array field
should generally be split off into a separate table. Tables can obviously be
searched easily."Best Regards,
Steve Howe---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
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 19026Import Notes
Reply to msg id not found: 9qqo6a1rm61@news.tht.net
Hello Bruce!
Yes, we inherited these arrays from Berkeley and haven't had any need to
remove them. Are you trying to do things that the other interfaces like
ODBC and JDBC don't handle?
About the groups: I just want to write a function that will return the users
names belonged by a given group. I understand I can load the arrays in
memory, then sequentially compare the members from pg_shadow, but doing it
goes against the database priciple after all.
About the procs: the Borland's dbExpress specification demands a
input/output list of parameters for stored procedures, and I'm going to use
functions as stored procedures. But I need to make a types list to be able
list what are those params.
The group array is a hack but the pg_proc array would be hard to replace
becauseit acts as part of the unique key used for cache lookups.
This design itself bothers me.
We have no other option left ? Like arrays being referenced in relations ?
That's far from perfect, but at least would solve those issues and others
which might appear in other catalogs...
Best Regards,
Steve Howe
Steve Howe writes:
The group array is a hack but the pg_proc array would be hard to replace
becauseit acts as part of the unique key used for cache lookups.This design itself bothers me.
We have no other option left ? Like arrays being referenced in relations ?
That's far from perfect, but at least would solve those issues and others
which might appear in other catalogs...
In general, the system catalogs are far from a perfect example (or even an
example at all) for pure, normalized relational database design. A more
important concern in processing efficiency. For instance, currently the
execution of a procedure takes one catalog lookup versus (1 + nargs) in a
more normalized design. (This is an oversimplification, but you get the
idea.)
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
"Steve Howe" <howe@carcass.dhs.org> writes:
The group array is a hack but the pg_proc array would be hard to replace
becauseit acts as part of the unique key used for cache lookups.
This design itself bothers me.
We have no other option left ? Like arrays being referenced in relations ?
Sure, it *could* be done another way. As far as pg_proc goes, I agree
with Bruce: there are far too many places that know the existing
representation for us to consider changing it. The pain involved would
vastly outweigh any possible benefit.
The representation of groups is not so widely known, however. We could
probably get away with changing it, if someone wanted to propose a
better catalog schema and do the legwork to make it happen.
regards, tom lane