2015-11-05 13:31 GMT+01:00 Craig Ringer <cr...@2ndquadrant.com>: > On 5 November 2015 at 14:36, Pavel Stehule <pavel.steh...@gmail.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 >