Re: proposal: regrole type?

Hello

Can we implement REGROLE type, that simplify role name <-> oid transformations?

Regards

Pavel

Hello, Pavel.

You wrote:

PS> Hello

PS> Can we implement REGROLE type, that simplify role name <-> oid transformations?

+1 from me. My old wish.

PS> Regards

PS> Pavel

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> Can we implement REGROLE type, that simplify role name <-> oid transformations?

Why? It's not any more complicated than it is for the other object
types that lack REGxxx pseudotypes. Generally speaking, we've only
bothered with pseudotypes for the cases where lookup is not trivial,
eg because there are search path considerations.

regards, tom lane

Hello

2012/12/25 Pavel Golub <pavel(at)microolap(dot)com>:
> Hello, Pavel.
>
> You wrote:
>
> PS> Hello
>
> PS> Can we implement REGROLE type, that simplify role name <-> oid transformations?
>
> +1 from me. My old wish.

I started implementation. I found a two points, that should be solved before.

we operate over roles with (without) fictive role "public". So we need
two datatypes :( I have no idea about second name :( - there should be
difference if type enables or disallow "public".

second issue is value of ACL_ID_PUBLIC, that is zero - and there is
not difference from InvalidOid - what should be acceptable via "-"
symbol.

Any ideas?

Regards

Pavel

>
> PS> Regards
>
> PS> Pavel
>
>
>
>
>
> --
> With best wishes,
> Pavel mailto:pavel(at)gf(dot)microolap(dot)com
>

2012/12/25 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> Can we implement REGROLE type, that simplify role name <-> oid transformations?
>
> Why? It's not any more complicated than it is for the other object
> types that lack REGxxx pseudotypes. Generally speaking, we've only
> bothered with pseudotypes for the cases where lookup is not trivial,
> eg because there are search path considerations.

my first motivation was a cosiness, but a second view shows so this
type(s) can be useful:

* It is relative natural - and can simplify and speed up some kind of
queries, because it directly access to cache.

* We can reduce to half lot of functions \df has_* (84 functions)

pg_catalog | pg_has_role | boolean | name, name, text | normal
pg_catalog | pg_has_role | boolean | name, oid, text | normal
pg_catalog | pg_has_role | boolean | name, text | normal
pg_catalog | pg_has_role | boolean | oid, name, text | normal
pg_catalog | pg_has_role | boolean | oid, oid, text | normal
pg_catalog | pg_has_role | boolean | oid, text | normal

these function should be replaced with more semantics descriptive headers

pg_has_role(regrole, regrole, text)
pg_has_role(regrole, text)
pg_has_role(text)

so we can drop (42 functions from catalog)

* this new datatype can be used in custom functions with implicit
validity checking - and if I can sort a importance of some internal
objects - then the roles are relative on high position.

Best regards

Pavel

p.s. A implementation should be little bit harder than I expected due
specific role "public", but I am thinking so it can has a some value.

>
> regards, tom lane

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> * We can reduce to half lot of functions \df has_* (84 functions)

Not without breaking existing queries. A function taking regrole might
look like it substitutes for one taking a text-string user name as long
as you only pass literal constants to it, but as soon as you pass
non-constants you'll find out different. (Unless your plan is to also
create an implicit cast from text to regrole, which strikes me as a
seriously bad idea.)

The reason we've not been more aggressive about using the OID-alias
pseudotypes is exactly that they're not a cure-all. Otherwise we would
already have about a dozen more of them. I don't think it's really
worth it: the notational savings is pretty marginal and the impact on
application namespace should not be ignored. (Keep in mind that any new
system type causes problems for similarly-named user tables.)

regards, tom lane

2012/12/25 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> * We can reduce to half lot of functions \df has_* (84 functions)
>
> Not without breaking existing queries. A function taking regrole might
> look like it substitutes for one taking a text-string user name as long
> as you only pass literal constants to it, but as soon as you pass
> non-constants you'll find out different. (Unless your plan is to also
> create an implicit cast from text to regrole, which strikes me as a
> seriously bad idea.)

understand

>
> The reason we've not been more aggressive about using the OID-alias
> pseudotypes is exactly that they're not a cure-all.

yes, I agree. But this type can has sense without propagation to
current functionality - and current functions can be marked as
obsolete in future. I believe so it can clean little bit this are -
mainly can solve task, where can be used pseudo role "public" and it
is more accurate and semantic design and can be used in new functions
and system views.

Otherwise we would
> already have about a dozen more of them. I don't think it's really
> worth it: the notational savings is pretty marginal and the impact on
> application namespace should not be ignored. (Keep in mind that any new
> system type causes problems for similarly-named user tables.)

9.3 has no problem

postgres=# create table regtype(a int);
CREATE TABLE

postgres=# create table regclass(a int);
CREATE TABLE

Regards

Pavel

>
> regards, tom lane

2012/12/25 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> 2012/12/25 Pavel Golub <pavel(at)microolap(dot)com>:
>> Hello, Pavel.
>>
>> You wrote:
>>
>> PS> Hello
>>
>> PS> Can we implement REGROLE type, that simplify role name <-> oid transformations?
>>
>> +1 from me. My old wish.
>
> I started implementation. I found a two points, that should be solved before.
>
> we operate over roles with (without) fictive role "public". So we need
> two datatypes :( I have no idea about second name :( - there should be
> difference if type enables or disallow "public".
>
> second issue is value of ACL_ID_PUBLIC, that is zero - and there is
> not difference from InvalidOid - what should be acceptable via "-"
> symbol.
>
> Any ideas?

one idea

regrole - defined only for "real" roles - support InvalidOid - doesn't
support "public"

regaclrole - defined for any roles, that can be used for ACL (with
"public"), doesn't support InvalidOid

Regards

Pavel

>
> Regards
>
> Pavel
>
>>
>> PS> Regards
>>
>> PS> Pavel
>>
>>
>>
>>
>>
>> --
>> With best wishes,
>> Pavel mailto:pavel(at)gf(dot)microolap(dot)com
>>

2012/12/26 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2012/12/25 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> * We can reduce to half lot of functions \df has_* (84 functions)
>>
>> Not without breaking existing queries. A function taking regrole might
>> look like it substitutes for one taking a text-string user name as long
>> as you only pass literal constants to it, but as soon as you pass
>> non-constants you'll find out different. (Unless your plan is to also
>> create an implicit cast from text to regrole, which strikes me as a
>> seriously bad idea.)

I was little bit surprised so regproc, regprocedure is not used on
SQL level in our builtin functions - and I use both types often in our
custom queries.

So it can be similar with regrole and regaclrole - it can be addressed
for more orthogonal work with roles

I am sending patch, but I will not assign to commitfest now.

Regards

Pavel