Thread: Note about comparation PL/SQL packages and our schema/extensions
Hi
I had talk about possibility to implement PL/SQL packages in Postgres. The package concept is coming from ADA language and it is partially foreign/redundant element in SQL world. Oracle needs it for modularization, because schema plays different role there than in Postgres. My opinion about packages in Postgres is clean - the concept of schemas and extension is simple and just work. I don't see any big gap there. If we don't play Oracle compatibility game, then we don't need to implement class like Oracle package. But there are few features, that can help to PL/pgSQL developers - generally or with porting from Oracle.
On 5 November 2015 at 14:36, Pavel Stehule <pavel.stehule@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. > 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
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.
On Thu, Nov 5, 2015 at 9:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
All three features we can emulate relative simply in C, and probably for all mentioned points we have some workaround (less/more ugly) for PL/pgSQL. Can be nice do it cleanly in PLpgSQL too.3. The initialization routines - the routines called when any object from schema is used first time.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.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.HiI had talk about possibility to implement PL/SQL packages in Postgres.
The package concept is coming from ADA language and it is partially foreign/redundant element in SQL world. Oracle needs it for modularization, because schema plays different role there than in Postgres. My opinion about packages in Postgres is clean - the concept of schemas and extension is simple and just work. I don't see any big gap there. If we don't play Oracle compatibility game, then we don't need to implement class like Oracle package. But there are few features, that can help to PL/pgSQL developers - generally or with porting from Oracle.
I'd say go ahead ! Packages support is the one of the most requested feature of people migrating from Oracle.
PavelRegardsComments, notes?I don't think we need ADA/ | PL/SQL Syntax - we can enhance our extension mechanism to support mentioned points.
2015-11-05 13:31 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:
On 5 November 2015 at 14:36, Pavel Stehule <pavel.stehule@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
2015-11-05 21:29 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
2015-11-05 13:31 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:On 5 November 2015 at 14:36, Pavel Stehule <pavel.stehule@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 likeDECLARE [ VARIABLE ] schema.myvar AS integer;CREATE LOCAL FUNCTION schema.init()RETURNS void AS $$BEGINmyvar := 0;END;CREATE OR REPLACE FUNCTION schema.current_var()RETURNS integer AS $$BEGINRETURN myvar;END;CREATE OR REPLACE FUNCTION schema.set_var(myvar integer)RETURNS void AS $$BEGINschema.myvar := var; -- using qualified name as name collision solutionEND;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