Thread: Arrays vs separate system catalogs

During my coding of the per-user/database settings, it occurred to me one
more time that arrays are evil.  Basically, the initial idea was to have a
column pg_database.datconfig that contains, say,
'{"geqo_threshold=55","enable_seqscan=off"}'.  Just inserting and deleting
in arrays is terrible, let alone querying them in a reasonable manner.
We're getting killed by this every day in the privileges and groups case.

What are people's thoughts on where (variable-length) arrays are OK in
system catalogs, and where a new system catalog should be created?

-- 
Peter Eisentraut   peter_e@gmx.net

Hello.

It's more common problem, I think. Working with array in postgres is 
very difficult and uncomfortable. For any array type it needs methods 
like a push, pop, length, splice  etc. This methods may be implemented 
for any array type. BTW, aggregates MAX and MIN works only for built-in 
types, but such aggregates may be defined for any type which supports 
compare function.

Peter Eisentraut wrote:
> During my coding of the per-user/database settings, it occurred to me one
> more time that arrays are evil.  Basically, the initial idea was to have a
> column pg_database.datconfig that contains, say,
> '{"geqo_threshold=55","enable_seqscan=off"}'.  Just inserting and deleting
> in arrays is terrible, let alone querying them in a reasonable manner.
> We're getting killed by this every day in the privileges and groups case.
> 
> What are people's thoughts on where (variable-length) arrays are OK in
> system catalogs, and where a new system catalog should be created?
> 
> 


-- 
Teodor Sigaev
teodor@stack.net

Peter Eisentraut <peter_e@gmx.net> writes:
> During my coding of the per-user/database settings, it occurred to me one
> more time that arrays are evil.  Basically, the initial idea was to have a
> column pg_database.datconfig that contains, say,
> '{"geqo_threshold=55","enable_seqscan=off"}'.  Just inserting and deleting
> in arrays is terrible, let alone querying them in a reasonable manner.
> We're getting killed by this every day in the privileges and groups case.

> What are people's thoughts on where (variable-length) arrays are OK in
> system catalogs, and where a new system catalog should be created?

Seems like an array is a perfectly fine representation, and what's
lacking are suitable operators.  Maybe we should think about inventing
some operators, rather than giving up on arrays.
        regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> 
>>During my coding of the per-user/database settings, it occurred to me one
>>more time that arrays are evil.  Basically, the initial idea was to have a
>>column pg_database.datconfig that contains, say,
>>'{"geqo_threshold=55","enable_seqscan=off"}'.  Just inserting and deleting
>>in arrays is terrible, let alone querying them in a reasonable manner.
>>We're getting killed by this every day in the privileges and groups case.
>>
> 
>>What are people's thoughts on where (variable-length) arrays are OK in
>>system catalogs, and where a new system catalog should be created?
>>
> 
> Seems like an array is a perfectly fine representation, and what's
> lacking are suitable operators.  Maybe we should think about inventing
> some operators, rather than giving up on arrays.

IMHO making arrays and relations equivalent is a real challenge. But 
this would give the full power of SQL to arrays (subselects, aggregates,  easy insertion, deletion, selection,
updates).

But if you manage to make an array accessible as a relation this would 
be a big step for mankind ;-)

(e.g. select * from pg_class.relacl where pg_class.relname='pg_stats';

insert into pg_class.relacl values 'christof=r' where 
pg_class.relname='pg_stats';

But at least the second example looks unSQLish to me
(I doubt the syntax "insert ... where" is legal))

Seemed a good idea first ... but I don't know whether it is worth the 
(syntactic, planning, non-standard) trouble.    Christof Petig