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

2015-11-05 21:29 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2015-11-05 13:31 GMT+01:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:
>
>> On 5 November 2015 at 14:36, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>> > 1. The encapsulation and local scope - all objects in schema are
>> accessible
>> > from other objects in schema by default (can be rewritten by explicit
>> > granting). Local objects are visible only from objects in schema. This
>> needs
>> > enhancing of our search_path mechanism.
>>
>> Yep. It's as if, within function packagename.funcname, packagename is
>> implicitly prefixed to search_path .
>>
>> I can see that being handy, but not especially important.
>>
>> > 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.
>>
>> 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.
>>
>> 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.
>>
>
> I mentioned "local schema session variables", but I had to say "local
> schema variables", because I don't think using GUC is good idea.
>
> Personally I am inclined to use different mechanism than GUC - GUC is
> untyped and slow, and I don't prefer T-SQL syntax - it is foreign element -
> and it can do false believe about relation between T-SQL and Postgres.
>
> The local schema variables can be accessed only from PL functions - and it
> can have usual syntax for any specific PL language.
>
> So some extension can looks like
>
> DECLARE [ VARIABLE ] schema.myvar AS integer;
>
> CREATE LOCAL FUNCTION schema.init()
> RETURNS void AS $$
> BEGIN
> myvar := 0;
> END;
>
> CREATE OR REPLACE FUNCTION schema.current_var()
> RETURNS integer AS $$
> BEGIN
> RETURN myvar;
> END;
>
> CREATE OR REPLACE FUNCTION schema.set_var(myvar integer)
> RETURNS void AS $$
> BEGIN
> schema.myvar := var; -- using qualified name as name collision solution
> END;
>
> Outside schema the access should be via functions schema.current_var() and
> schema.set_var().
>
> The advantage of this design - we don't need to modify a SQL parser for
> DQL and DML, and we don't need to introduce any nonstandard behave (syntax)
> to SQL .
>

probably we can adopt concept ANSI/SQL MODULEs enhanced about the
variables. It is relative similar to proposed code.

>
>
>>
>> > 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.
>>
>> --
>> Craig Ringer http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>