Catalogs design question

On Sat, 20 Oct 2001, Steve Howe wrote:

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):

In the contrib/ directory are procedures to search arrays for values.
This may help.

--

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant

I also quote the PotgreSQL user manual

(http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html):

In the contrib/ directory are procedures to search arrays for values.
This may help.

Thanks for the tip, but in fact I've seen them (and they're listed on the
same document I pointed on the original message).
These are sequential (slow) searches, and can't be indexed. in resume:
nothing but another crude hack :). I could even use it, but I can';t tell my
users "oh this feature works but you must compile this contrib code inyo
your servers". Many users can't do it, and many don't even know how to do it
:(

Best Regards,
Steve Howe

Hi,

I think Bruce meant contrib/intarray which provides incredibly fast
indexed access to arrays of integers, which is your case.
We use it a lot, particularly in our full text search engine (OpenFTS).

regards,

Oleg
On Sat, 20 Oct 2001, Steve Howe wrote:

I also quote the PotgreSQL user manual

(http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/arrays.html):

In the contrib/ directory are procedures to search arrays for values.
This may help.

Thanks for the tip, but in fact I've seen them (and they're listed on the
same document I pointed on the original message).
These are sequential (slow) searches, and can't be indexed. in resume:
nothing but another crude hack :). I could even use it, but I can';t tell my
users "oh this feature works but you must compile this contrib code inyo
your servers". Many users can't do it, and many don't even know how to do it
:(

Best Regards,
Steve Howe

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Hi Steve,

Your question about - pg_proc
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[0] = t.oid ;
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[1] = t.oid ;
...
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[7] = t.oid ;

As far as I understand the proargtypes entries 0 means no further parameter.
This oidvector type of proargtypes seems to have a start index of 0.
As long as there are at maximum 8 parameters allowed, this looks practicable.

Your question about - pg_group
The pg_group column is more bulky, because the int4[] type does not have
an upper limit.
So, the only solution I can see is
get the number of array elements of the group you want to query
select array_dims(grolist) from pg_group where groname = '<your_group>';

and then generate automatically a query like

select u.usename from pg_user u , pg_group g where
g.grolist[1] = u.usesysid and g.groname='<your_group>'
union
select u.usename from pg_user u , pg_group g where
g.grolist[2] = u.usesysid and g.groname='<your_group>'
union
...
select u.usename from pg_user u , pg_group g where
g.grolist[n] = u.usesysid and g.groname='<your_group>' ;

This looks very much like another crude hack you've already
complained about. Sorry, but I can't help.

Two more items I do not understand:
You said, the procedures to search arrays in contrib/ are slow.
Maybe that's true, but usually you do not have thousands of users
in a group, don't you.
You said, many users cannot compile this contrib code. Yes, and they
are not supposed to do so, because it's up to a system admin to do.
What do I miss here?

Regards, Christoph

Hello Haller!!

Your question about - pg_proc
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[0] = t.oid ;
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[1] = t.oid ;
...
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[7] = t.oid ;

As far as I understand the proargtypes entries 0 means no further

parameter.

This oidvector type of proargtypes seems to have a start index of 0.
As long as there are at maximum 8 parameters allowed, this looks

practicable.
There is such a limit ? I didn't know. This makes your code a working way.
I'll look further on this later... and even if it's not a query that I would
say it's beautiful, it's a way, thanks :).

Your question about - pg_group
The pg_group column is more bulky, because the int4[] type does not have
an upper limit.
So, the only solution I can see is
get the number of array elements of the group you want to query
select array_dims(grolist) from pg_group where groname = '<your_group>';

and then generate automatically a query like

select u.usename from pg_user u , pg_group g where
g.grolist[1] = u.usesysid and g.groname='<your_group>'
union
select u.usename from pg_user u , pg_group g where
g.grolist[2] = u.usesysid and g.groname='<your_group>'
union
...
select u.usename from pg_user u , pg_group g where
g.grolist[n] = u.usesysid and g.groname='<your_group>' ;

This looks very much like another crude hack you've already
complained about. Sorry, but I can't help.

Yes, it's ugly code. I would rather write a function, but again I can't
assume the user has pl/perl or pl/pgsql (or any other).

Two more items I do not understand:
You said, the procedures to search arrays in contrib/ are slow.
Maybe that's true, but usually you do not have thousands of users
in a group, don't you.

Yes. I would use it if I can.

You said, many users cannot compile this contrib code. Yes, and they
are not supposed to do so, because it's up to a system admin to do.
What do I miss here?

Oh, I develop an interface for PostgreSQL called
pgExpress(http://www.vitavoom.com) - it's like an ODBC driver or such. I
must provide the functionality I described for the driver users; it's not
for me. I would of course have compiled and used the contrib code. But the
driver must work "out-of-the-box", and requiring a recompile (where many
times is impossible to users) is not a solution...
Right now, I'm hardcoding that relation inside the driver, what's also not
what I dreamed about, but I seem to have no other choice.

Thanks for the ideas btw :)

Best Regards,
Steve Howe

Hello Haller!!!

Your question about - pg_proc
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[0] = t.oid ;
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[1] = t.oid ;
...
select t.typname from pg_type t , pg_proc p
where p.proname = '<your_stored_procedure>' and p.proargtypes[7] = t.oid ;

As far as I understand the proargtypes entries 0 means no further

parameter.

This oidvector type of proargtypes seems to have a start index of 0.
As long as there are at maximum 8 parameters allowed, this looks

practicable.
There is no limit on the number of arguments. An user could create a weird
function like this:

howe=# CREATE FUNCTION test2(int2, int2, int2, int2, int2, int2, int2, int2,
int2, int2, int2, int2, int2) RETURNS int4
AS 'SELECT 1 AS RESULT' LANGUAGE 'sql';
CREATE

and it would be allowed...

howe=# select proargtypes from pg_proc where proname='test';
proargtypes
----------------------------------------
21 21 21 21 21 21 21 21 21 21 21 21 21
(1 row)

Again, the problem is that I can't predict (nor limit) what users will try
to do...

Best Regards,
Steve Howe

"Steve Howe" <howe@carcass.dhs.org> writes:

As long as there are at maximum 8 parameters allowed, this looks
practicable.

There is no limit on the number of arguments.

You're both wrong: the limit is FUNC_MAX_ARGS, which hasn't been 8 in
quite some time. It's presently 16 by default, and can be configured
higher at build time.

For the purposes of a frontend application, I think it's best to assume
that the specific limit is unknown --- ie, you should be able to
interoperate with a backend regardless of the FUNC_MAX_ARGS value it
was built with.

regards, tom lane