Re: How about to have relnamespace and relrole?

On 3/29/15 1:55 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> I have just claimed this as committer in the CF, but on reviewing the
>> emails it looks like there is disagreement about the need for it at all,
>> especially from Tom and Robert.
>
>> I confess I have often wanted regnamespace, particularly, and
>> occasionally regrole, simply as a convenience. But I'm not going to
>> commit it against substantial opposition.
>
>> Do we need a vote?
>
> My concern about it is basically that I don't see where we stop.
> The existing regFOO alias types are provided for object classes which
> have nontrivial naming conventions (schema qualification, overloaded
> argument types, etc), so that you can't just do "select ... from
> catalog where objectname = 'blah'". That doesn't apply to namespaces
> or roles. So I'm afraid that once this precedent is established,
> there will be demands for regFOO for every object class we have,
> and I don't want that much clutter.

IMHO the real issue here is it's just a royal PITA to query the catalogs
in so many cases, especially on an ad-hoc basis. information_schema
helps in many cases, but it sometimes doesn't have PG-specific stuff
that you need. And if you're writing code you can't depend on it
actually being there. (I've also heard it's horribly slow...)

I don't see a good way to really solve that other than reviving
pg_sysview and pulling that in.

BTW, it would arguably be better if we just exposed the function that
deals with quoting and schemas; IIRC it doesn't actually need the catalog.

> It may be that these two cases are so much more useful than any other
> conceivable cases that we can do them and stop, but I don't think that
> argument has been made convincingly.

Short of fixing the underlying problem I think regnamspace would
absolutely be worth it. I find myself wanting that all the time.

I generally don't care about roles too much, but maybe that's just me.

FWIW, (and off-topic...) the other thing I find very painful is dealing
with ACLs. has_*_privilege does what it does well, but if you want to do
anything else (such as ensure permissions on an object are specified as
you expect) you're stuck resorting to things like

NOT EXISTS( SELECT has_*_privilege() FROM pg_roles WHERE rolname NOT IN() )

which is awkward because you actually need one of those for every
permission you want to check.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com