Re: Note about comparation PL/SQL packages and our schema/extensions

On 11/05/2015 01:31 PM, Craig Ringer wrote:
> On 5 November 2015 at 14:36, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> [snip]
>
>> 2. The schema variables - a server side session (can be emulated now) and
>> server side local schema session variables (doesn't exist) is pretty useful
>> for storing some temp data or high frequent change data - and can
>> significantly increase speed of some use cases. Now we emulate it via PLPerl
>> shared array, but the encapsulation is missing.
> This is the feature I feel we could really use.
>
> I see *lots* of people emulating session variables by (ab)using custom
> GUCs. The missing-ok variant of current_setting helps with this to the
> point where it's fairly OK now.

AFAICS, (ab)using  custom GUCs is the "blessed" (by Tom, no less) way to 
do it...
See http://www.postgresql.org/message-id/16931.1172871930@sss.pgh.pa.us and really made possible in 9.4  :)

Though the "usual" @@ syntax would certainly help some users migrate 
over ...

> The main advantage package variables have - IMO - are package
> permissions. You can define a variable that is writeable only by
> functions within a package. That's really handy for things like row
> security since it lets you have variables you can only set via a
> function that can do things like refuse to run again with different
> args, validate input, etc. So you can do expensive work once, then
> cheap row security checks against the preset variable. Or use it for
> things like "current customer" settings when using pooled connections.

Some sort of "packages" ---in this sense--- could be implemented as 
extensions, but I guess a more integrated approach would be welcome.

> It might make sense to extend custom GUCs for this rather than invent
> a new mechanism, since GUCs have lots of useful properties like
> global, db, user, session and transaction scoping, etc. I'm not really
> sure... I just agree that it's a good idea to be able to have
> something with similar capabilities to package variables. Especially
> security properties.
>
>> 3. The initialization routines - the routines called when any object from
>> schema is used first time.
> ... which is somewhat similar to having an "on session start" trigger.
> Also an oft-wanted feature.

Frequently requested, only because one other database requires it for 
what we do with role-level configuration via GUCs.
The other use case I see would definitively be accomodated by having 
packages with the properties you describe above.

These properties might be even emulated via some clever extension ....


Just my two cents.


Thanks,
    / J.L.