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

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 .

>
> > 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
>