proposal: schema variables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
172 messages Options
1234 ... 9
Reply | Threaded
Open this post in threaded view
|

proposal: schema variables

Pavel Stehule
Hi,

I propose a  new database object - a variable. The variable is persistent object, that holds unshared session based not transactional in memory value of any type. Like variables in any other languages. The persistence is required for possibility to do static checks, but can be limited to session - the variables can be temporal.

My proposal is related to session variables from Sybase, MSSQL or MySQL (based on prefix usage @ or @@), or package variables from Oracle (access is controlled by scope), or schema variables from DB2. Any design is coming from different sources, traditions and has some advantages or disadvantages. The base of my proposal is usage schema variables as session variables for stored procedures. It should to help to people who try to port complex projects to PostgreSQL from other databases.

The Sybase  (T-SQL) design is good for interactive work, but it is weak for usage in stored procedures - the static check is not possible. Is not possible to set some access rights on variables.

The ADA design (used on Oracle) based on scope is great, but our environment is not nested. And we should to support other PL than PLpgSQL more strongly.

There is not too much other possibilities - the variable that should be accessed from different PL, different procedures (in time) should to live somewhere over PL, and there is the schema only.

The variable can be created by CREATE statement:

CREATE VARIABLE public.myvar AS integer;
CREATE VARIABLE myschema.myvar AS mytype;

CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.

The access rights is controlled by usual access rights - by commands GRANT/REVOKE. The possible rights are: READ, WRITE

The variables can be modified by SQL command SET (this is taken from standard, and it natural)

SET varname = expression;

Unfortunately we use the SET command for different purpose. But I am thinking so we can solve it with few tricks. The first is moving our GUC to pg_catalog schema. We can control the strictness of SET command. In one variant, we can detect custom GUC and allow it, in another we can disallow a custom GUC and allow only schema variables. A new command LET can be alternative.

The variables should be used in queries implicitly (without JOIN)

SELECT varname;

The SEARCH_PATH is used, when varname is located. The variables can be used everywhere where query parameters are allowed.

I hope so this proposal is good enough and simple.

Comments, notes?

regards

Pavel


Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Nico Williams
On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> Comments, notes?

I like it.

I would further like to move all of postgresql.conf into the database,
as much as possible, as well as pg_ident.conf and pg_hba.conf.

Variables like current_user have a sort of nesting context
functionality: calling a SECURITY DEFINER function "pushes" a new value
onto current_user, then when the function returns the new value of
current_user is "popped" and the previous value restored.

It might be nice to be able to generalize this.

Questions that then arise:

 - can one see up the stack?
 - are there permissions issues with seeing up the stack?

I recently posted proposing a feature such that SECURITY DEFINER
functions could observe the _caller_'s current_user.

Nico
--


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule
Hi

2017-10-27 0:07 GMT+02:00 Nico Williams <[hidden email]>:
On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> Comments, notes?

I like it.

I would further like to move all of postgresql.conf into the database,
as much as possible, as well as pg_ident.conf and pg_hba.conf.

Variables like current_user have a sort of nesting context
functionality: calling a SECURITY DEFINER function "pushes" a new value
onto current_user, then when the function returns the new value of
current_user is "popped" and the previous value restored.

My proposal doesn't expecting with nesting, because there is only one scope - schema / session - but I don't think so it is necessary

current_user is a function - it is based on parser magic in Postgres. The origin from Oracle uses the feature of ADA language. When function has no parameters then parenthesis are optional. So current_user, current_time are functions current_user(), current_time().


It might be nice to be able to generalize this.

Questions that then arise:

 - can one see up the stack?
 - are there permissions issues with seeing up the stack?

these variables are pined to schema - so there is not any relation to stack. It is like global variables.

Theoretically we can introduce "functional" variables, where the value is based on immediate evaluation of expression. It can be very similar to current current_user.
 

I recently posted proposing a feature such that SECURITY DEFINER
functions could observe the _caller_'s current_user.

your use case is good example - this proposed feature doesn't depend on stack, depends on security context (security context stack) what is super set of call stack

Regards

Pavel



Nico
--

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Tatsuo Ishii-3
In reply to this post by Pavel Stehule
> Hi,
>
> I propose a  new database object - a variable. The variable is persistent
> object, that holds unshared session based not transactional in memory value
> of any type. Like variables in any other languages. The persistence is
> required for possibility to do static checks, but can be limited to session
> - the variables can be temporal.
>
> My proposal is related to session variables from Sybase, MSSQL or MySQL
> (based on prefix usage @ or @@), or package variables from Oracle (access
> is controlled by scope), or schema variables from DB2. Any design is coming
> from different sources, traditions and has some advantages or
> disadvantages. The base of my proposal is usage schema variables as session
> variables for stored procedures. It should to help to people who try to
> port complex projects to PostgreSQL from other databases.
>
> The Sybase  (T-SQL) design is good for interactive work, but it is weak for
> usage in stored procedures - the static check is not possible. Is not
> possible to set some access rights on variables.
>
> The ADA design (used on Oracle) based on scope is great, but our
> environment is not nested. And we should to support other PL than PLpgSQL
> more strongly.
>
> There is not too much other possibilities - the variable that should be
> accessed from different PL, different procedures (in time) should to live
> somewhere over PL, and there is the schema only.
>
> The variable can be created by CREATE statement:
>
> CREATE VARIABLE public.myvar AS integer;
> CREATE VARIABLE myschema.myvar AS mytype;
>
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.
>
> The access rights is controlled by usual access rights - by commands
> GRANT/REVOKE. The possible rights are: READ, WRITE
>
> The variables can be modified by SQL command SET (this is taken from
> standard, and it natural)
>
> SET varname = expression;
>
> Unfortunately we use the SET command for different purpose. But I am
> thinking so we can solve it with few tricks. The first is moving our GUC to
> pg_catalog schema. We can control the strictness of SET command. In one
> variant, we can detect custom GUC and allow it, in another we can disallow
> a custom GUC and allow only schema variables. A new command LET can be
> alternative.
>
> The variables should be used in queries implicitly (without JOIN)
>
> SELECT varname;
>
> The SEARCH_PATH is used, when varname is located. The variables can be used
> everywhere where query parameters are allowed.
>
> I hope so this proposal is good enough and simple.
>
> Comments, notes?

Just q quick follow up. Looks like a greate feature!

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Tsunakawa, Takayuki
In reply to this post by Pavel Stehule
From: [hidden email]

> [mailto:[hidden email]] On Behalf Of Pavel Stehule
> I propose a  new database object - a variable. The variable is persistent
> object, that holds unshared session based not transactional in memory value
> of any type. Like variables in any other languages. The persistence is
> required for possibility to do static checks, but can be limited to session
> - the variables can be temporal.
>
>
> My proposal is related to session variables from Sybase, MSSQL or MySQL
> (based on prefix usage @ or @@), or package variables from Oracle (access
> is controlled by scope), or schema variables from DB2. Any design is coming
> from different sources, traditions and has some advantages or disadvantages.
> The base of my proposal is usage schema variables as session variables for
> stored procedures. It should to help to people who try to port complex
> projects to PostgreSQL from other databases.

Very interesting.  I hope I could join the review and testing.

How do you think this would contribute to easing the port of Oracle PL/SQL procedures?  Would the combination of orafce and this feature promote auto-translation of PL/SQL procedures?  I'm curious what will be the major road blocks after adding the schema variable.

Regards
Takayuki Tsunakawa



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule


2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki <[hidden email]>:
From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Pavel Stehule
> I propose a  new database object - a variable. The variable is persistent
> object, that holds unshared session based not transactional in memory value
> of any type. Like variables in any other languages. The persistence is
> required for possibility to do static checks, but can be limited to session
> - the variables can be temporal.
>
>
> My proposal is related to session variables from Sybase, MSSQL or MySQL
> (based on prefix usage @ or @@), or package variables from Oracle (access
> is controlled by scope), or schema variables from DB2. Any design is coming
> from different sources, traditions and has some advantages or disadvantages.
> The base of my proposal is usage schema variables as session variables for
> stored procedures. It should to help to people who try to port complex
> projects to PostgreSQL from other databases.

Very interesting.  I hope I could join the review and testing.

you are welcome. I wrote a prototype last year based on envelope functions. But the integration must be much more close to SQL to be some clear benefit of this feature. So there is lot of work. I hope so I have a prototype after this winter. It is my plan for winter.
 

How do you think this would contribute to easing the port of Oracle PL/SQL procedures?  Would the combination of orafce and this feature promote auto-translation of PL/SQL procedures?  I'm curious what will be the major road blocks after adding the schema variable.

It depends on creativity of PL/SQL developers. Usual .. 80% application is possible to migrate with current GUC - some work does ora2pg. But GUC is little bit slower (not too important) and is not simple possibility to secure it.

So work with variables will be similar like GUC, but significantly more natural (not necessary to build wrap functions). It should be much better when value is of some composite type. The migrations will need some inteligence still, but less work and code will be more readable and cleaner.

I talked already about "schema pined" functions (schema private/public objects) - but I didn't think about it more deeply. There can be special access right to schema variables, the pined schema can be preferred before search_path. With this feature the schema will have very similar behave like Oracle Modules. Using different words - we can implement scope access rights based on schemas. But it is far horizon. What is important - proposal doesn't block any future enhancing in this case, and is consistent with current state. In future you can work with schema private functions, tables, variables, sequences. So variables are nothing special.

Regards

Pavel


Regards
Takayuki Tsunakawa



Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Gilles Darold
In reply to this post by Pavel Stehule
Le 26/10/2017 à 09:21, Pavel Stehule a écrit :

> Hi,
>
> I propose a  new database object - a variable. The variable is
> persistent object, that holds unshared session based not transactional
> in memory value of any type. Like variables in any other languages.
> The persistence is required for possibility to do static checks, but
> can be limited to session - the variables can be temporal.
>
> My proposal is related to session variables from Sybase, MSSQL or
> MySQL (based on prefix usage @ or @@), or package variables from
> Oracle (access is controlled by scope), or schema variables from DB2.
> Any design is coming from different sources, traditions and has some
> advantages or disadvantages. The base of my proposal is usage schema
> variables as session variables for stored procedures. It should to
> help to people who try to port complex projects to PostgreSQL from
> other databases.
>
> The Sybase  (T-SQL) design is good for interactive work, but it is
> weak for usage in stored procedures - the static check is not
> possible. Is not possible to set some access rights on variables.
>
> The ADA design (used on Oracle) based on scope is great, but our
> environment is not nested. And we should to support other PL than
> PLpgSQL more strongly.
>
> There is not too much other possibilities - the variable that should
> be accessed from different PL, different procedures (in time) should
> to live somewhere over PL, and there is the schema only.
>
> The variable can be created by CREATE statement:
>
> CREATE VARIABLE public.myvar AS integer;
> CREATE VARIABLE myschema.myvar AS mytype;
>
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.
>
> The access rights is controlled by usual access rights - by commands
> GRANT/REVOKE. The possible rights are: READ, WRITE
>
> The variables can be modified by SQL command SET (this is taken from
> standard, and it natural)
>
> SET varname = expression;
>
> Unfortunately we use the SET command for different purpose. But I am
> thinking so we can solve it with few tricks. The first is moving our
> GUC to pg_catalog schema. We can control the strictness of SET
> command. In one variant, we can detect custom GUC and allow it, in
> another we can disallow a custom GUC and allow only schema variables.
> A new command LET can be alternative.
>
> The variables should be used in queries implicitly (without JOIN)
>
> SELECT varname;
>
> The SEARCH_PATH is used, when varname is located. The variables can be
> used everywhere where query parameters are allowed.
>
> I hope so this proposal is good enough and simple.
>
> Comments, notes?
>
> regards
>
> Pavel
>
>

Great feature that will help for migration. How will you handle CONSTANT
declaration? With Oracle it is possible to declare a constant as follow:


  varname     CONSTANT INTEGER    := 500;


for a variable that can't be changed. Do you plan to add a CONSTANT or
READONLY keyword or do you want use GRANT on the object to deal with
this case?


Regards

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule


2017-10-27 15:38 GMT+02:00 Gilles Darold <[hidden email]>:
Le 26/10/2017 à 09:21, Pavel Stehule a écrit :
> Hi,
>
> I propose a  new database object - a variable. The variable is
> persistent object, that holds unshared session based not transactional
> in memory value of any type. Like variables in any other languages.
> The persistence is required for possibility to do static checks, but
> can be limited to session - the variables can be temporal.
>
> My proposal is related to session variables from Sybase, MSSQL or
> MySQL (based on prefix usage @ or @@), or package variables from
> Oracle (access is controlled by scope), or schema variables from DB2.
> Any design is coming from different sources, traditions and has some
> advantages or disadvantages. The base of my proposal is usage schema
> variables as session variables for stored procedures. It should to
> help to people who try to port complex projects to PostgreSQL from
> other databases.
>
> The Sybase  (T-SQL) design is good for interactive work, but it is
> weak for usage in stored procedures - the static check is not
> possible. Is not possible to set some access rights on variables.
>
> The ADA design (used on Oracle) based on scope is great, but our
> environment is not nested. And we should to support other PL than
> PLpgSQL more strongly.
>
> There is not too much other possibilities - the variable that should
> be accessed from different PL, different procedures (in time) should
> to live somewhere over PL, and there is the schema only.
>
> The variable can be created by CREATE statement:
>
> CREATE VARIABLE public.myvar AS integer;
> CREATE VARIABLE myschema.myvar AS mytype;
>
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.
>
> The access rights is controlled by usual access rights - by commands
> GRANT/REVOKE. The possible rights are: READ, WRITE
>
> The variables can be modified by SQL command SET (this is taken from
> standard, and it natural)
>
> SET varname = expression;
>
> Unfortunately we use the SET command for different purpose. But I am
> thinking so we can solve it with few tricks. The first is moving our
> GUC to pg_catalog schema. We can control the strictness of SET
> command. In one variant, we can detect custom GUC and allow it, in
> another we can disallow a custom GUC and allow only schema variables.
> A new command LET can be alternative.
>
> The variables should be used in queries implicitly (without JOIN)
>
> SELECT varname;
>
> The SEARCH_PATH is used, when varname is located. The variables can be
> used everywhere where query parameters are allowed.
>
> I hope so this proposal is good enough and simple.
>
> Comments, notes?
>
> regards
>
> Pavel
>
>

Great feature that will help for migration. How will you handle CONSTANT
declaration? With Oracle it is possible to declare a constant as follow:


  varname     CONSTANT INTEGER    := 500;


for a variable that can't be changed. Do you plan to add a CONSTANT or
READONLY keyword or do you want use GRANT on the object to deal with
this case?

Plpgsql  declaration supports CONSTANT

I forgot it. Thank you

Pavel




Regards

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Chris Travers-7
In reply to this post by Pavel Stehule


On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <[hidden email]> wrote:
Hi,

I propose a  new database object - a variable. The variable is persistent object, that holds unshared session based not transactional in memory value of any type. Like variables in any other languages. The persistence is required for possibility to do static checks, but can be limited to session - the variables can be temporal.

My proposal is related to session variables from Sybase, MSSQL or MySQL (based on prefix usage @ or @@), or package variables from Oracle (access is controlled by scope), or schema variables from DB2. Any design is coming from different sources, traditions and has some advantages or disadvantages. The base of my proposal is usage schema variables as session variables for stored procedures. It should to help to people who try to port complex projects to PostgreSQL from other databases.

The Sybase  (T-SQL) design is good for interactive work, but it is weak for usage in stored procedures - the static check is not possible. Is not possible to set some access rights on variables.

The ADA design (used on Oracle) based on scope is great, but our environment is not nested. And we should to support other PL than PLpgSQL more strongly.

There is not too much other possibilities - the variable that should be accessed from different PL, different procedures (in time) should to live somewhere over PL, and there is the schema only.

The variable can be created by CREATE statement:

CREATE VARIABLE public.myvar AS integer;
CREATE VARIABLE myschema.myvar AS mytype;

CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.

The access rights is controlled by usual access rights - by commands GRANT/REVOKE. The possible rights are: READ, WRITE

The variables can be modified by SQL command SET (this is taken from standard, and it natural)

SET varname = expression;

Unfortunately we use the SET command for different purpose. But I am thinking so we can solve it with few tricks. The first is moving our GUC to pg_catalog schema. We can control the strictness of SET command. In one variant, we can detect custom GUC and allow it, in another we can disallow a custom GUC and allow only schema variables. A new command LET can be alternative.

The variables should be used in queries implicitly (without JOIN)

SELECT varname;

The SEARCH_PATH is used, when varname is located. The variables can be used everywhere where query parameters are allowed.

I hope so this proposal is good enough and simple.

Comments, notes?


I have a question on this.  Since one can issue set commands on arbitrary settings (and later ALTER database/role/system on settings you have created in the current session) I am wondering how much overlap there is between a sort of extended GUC with custom settings and variables. 

Maybe it would be simpler to treat variables and GUC settings to be similar and see what can be done to extend GUC in this way?

I mean if instead we allowed restricting SET to known settings then we could have a CREATE SETTING command which would behave like this and then use SET the same way across both.

In essence I am wondering if this really needs to be as separate from GUC as you are proposing.

If done this way then:

1.  You could issue grant or revoke on GUC settings, allowing some users but not others to set things like work_mem for their queries
2.  You could specify allowed types in custom settings.
3.  In a subsequent stage you might be able to SELECT .... INTO setting_name FROM ....;  allowing access to setting writes based on queries.



regards

Pavel





--
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com 
Saarbrücker Straße 37a, 10405 Berlin

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule
Hi

2017-10-28 16:24 GMT+02:00 Chris Travers <[hidden email]>:


On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <[hidden email]> wrote:
Hi,

I propose a  new database object - a variable. The variable is persistent object, that holds unshared session based not transactional in memory value of any type. Like variables in any other languages. The persistence is required for possibility to do static checks, but can be limited to session - the variables can be temporal.

My proposal is related to session variables from Sybase, MSSQL or MySQL (based on prefix usage @ or @@), or package variables from Oracle (access is controlled by scope), or schema variables from DB2. Any design is coming from different sources, traditions and has some advantages or disadvantages. The base of my proposal is usage schema variables as session variables for stored procedures. It should to help to people who try to port complex projects to PostgreSQL from other databases.

The Sybase  (T-SQL) design is good for interactive work, but it is weak for usage in stored procedures - the static check is not possible. Is not possible to set some access rights on variables.

The ADA design (used on Oracle) based on scope is great, but our environment is not nested. And we should to support other PL than PLpgSQL more strongly.

There is not too much other possibilities - the variable that should be accessed from different PL, different procedures (in time) should to live somewhere over PL, and there is the schema only.

The variable can be created by CREATE statement:

CREATE VARIABLE public.myvar AS integer;
CREATE VARIABLE myschema.myvar AS mytype;

CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.

The access rights is controlled by usual access rights - by commands GRANT/REVOKE. The possible rights are: READ, WRITE

The variables can be modified by SQL command SET (this is taken from standard, and it natural)

SET varname = expression;

Unfortunately we use the SET command for different purpose. But I am thinking so we can solve it with few tricks. The first is moving our GUC to pg_catalog schema. We can control the strictness of SET command. In one variant, we can detect custom GUC and allow it, in another we can disallow a custom GUC and allow only schema variables. A new command LET can be alternative.

The variables should be used in queries implicitly (without JOIN)

SELECT varname;

The SEARCH_PATH is used, when varname is located. The variables can be used everywhere where query parameters are allowed.

I hope so this proposal is good enough and simple.

Comments, notes?


I have a question on this.  Since one can issue set commands on arbitrary settings (and later ALTER database/role/system on settings you have created in the current session) I am wondering how much overlap there is between a sort of extended GUC with custom settings and variables. 

Maybe it would be simpler to treat variables and GUC settings to be similar and see what can be done to extend GUC in this way?

I mean if instead we allowed restricting SET to known settings then we could have a CREATE SETTING command which would behave like this and then use SET the same way across both.

In essence I am wondering if this really needs to be as separate from GUC as you are proposing.

If done this way then:

1.  You could issue grant or revoke on GUC settings, allowing some users but not others to set things like work_mem for their queries
2.  You could specify allowed types in custom settings.
3.  In a subsequent stage you might be able to SELECT .... INTO setting_name FROM ....;  allowing access to setting writes based on queries.


The creating database objects and necessary infrastructure is the most simple task of this project. I'll be more happy if there are zero intersection because variables and GUC are designed for different purposes. But due SET keyword the intersection there is.

When I thinking about it, I have only one, but important reason, why I prefer design new type of database object -the GUC are stack based with different default granularity - global, database, user, session, function. This can be unwanted behave for variables - it can be source of hard to detected bugs. I afraid so this behave can be too messy for usage as variables.

@1 I have not clean opinion about it - not sure if rights are good enough - probably some user limits can be more practical - but can be hard to choose result when some user limits and GUC will be against
@2 With variables typed custom GUC are not necessary
@3 Why you need it? It is possible with set_config function now.

Regards

Pavel


 


regards

Pavel





--
Best Regards,
Chris Travers
Database Administrator

Tel: <a href="tel:+49%20162%209037210" value="+491629037210" target="_blank">+49 162 9037 210 | Skype: einhverfr | www.adjust.com 


Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Chris Travers-7


On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <[hidden email]> wrote:


The creating database objects and necessary infrastructure is the most simple task of this project. I'll be more happy if there are zero intersection because variables and GUC are designed for different purposes. But due SET keyword the intersection there is.

When I thinking about it, I have only one, but important reason, why I prefer design new type of database object -the GUC are stack based with different default granularity - global, database, user, session, function. This can be unwanted behave for variables - it can be source of hard to detected bugs. I afraid so this behave can be too messy for usage as variables.

@1 I have not clean opinion about it - not sure if rights are good enough - probably some user limits can be more practical - but can be hard to choose result when some user limits and GUC will be against

I was mostly thinking that users can probably set things like work_mem and possibly this might be a problem.
 
@2 With variables typed custom GUC are not necessary

I don't know about that.  For example with the geoip2lookup extension it is nice that you could set the preferred language for translation on a per user basis or the mmdb path on a per-db basis.
 
@3 Why you need it? It is possible with set_config function now.

Yeah you could do it safely with set_config and a CTE, but suppose I have:

with a (Id, value) as (values (1::Int, 'foo'), (2, 'bar'), (3, 'baz'))
SELECT set_config('custom_val', value) from a where id = 2;

What is the result out of this?  I would *expect* that this would probably run set_config 3 times and filter the output.
 

Regards

Pavel


 


regards

Pavel





--
Best Regards,
Chris Travers
Database Administrator

Tel: <a href="tel:+49%20162%209037210" value="+491629037210" target="_blank">+49 162 9037 210 | Skype: einhverfr | www.adjust.com 





--
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com 
Saarbrücker Straße 37a, 10405 Berlin

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Hannu Krosing-6
but you can always do 

with a (id, value) as (
  values (1, 'foo'), (2, 'bar'), (3, 'baz')
)
select set_config('custom.value',(select value from a where id = 2),true);

if you are worried about the evaluation order

On 29 October 2017 at 09:51, Chris Travers <[hidden email]> wrote:


On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <[hidden email]> wrote:


The creating database objects and necessary infrastructure is the most simple task of this project. I'll be more happy if there are zero intersection because variables and GUC are designed for different purposes. But due SET keyword the intersection there is.

When I thinking about it, I have only one, but important reason, why I prefer design new type of database object -the GUC are stack based with different default granularity - global, database, user, session, function. This can be unwanted behave for variables - it can be source of hard to detected bugs. I afraid so this behave can be too messy for usage as variables.

@1 I have not clean opinion about it - not sure if rights are good enough - probably some user limits can be more practical - but can be hard to choose result when some user limits and GUC will be against

I was mostly thinking that users can probably set things like work_mem and possibly this might be a problem.
 
@2 With variables typed custom GUC are not necessary

I don't know about that.  For example with the geoip2lookup extension it is nice that you could set the preferred language for translation on a per user basis or the mmdb path on a per-db basis.
 
@3 Why you need it? It is possible with set_config function now.

Yeah you could do it safely with set_config and a CTE, but suppose I have:

with a (Id, value) as (values (1::Int, 'foo'), (2, 'bar'), (3, 'baz'))
SELECT set_config('custom_val', value) from a where id = 2;

What is the result out of this?  I would *expect* that this would probably run set_config 3 times and filter the output.
 

Regards

Pavel


 


regards

Pavel





--
Best Regards,
Chris Travers
Database Administrator

Tel: <a href="tel:+49%20162%209037210" value="+491629037210" target="_blank">+49 162 9037 210 | Skype: einhverfr | www.adjust.com 





--
Best Regards,
Chris Travers
Database Administrator

Tel: <a href="tel:+49%20162%209037210" value="+491629037210" target="_blank">+49 162 9037 210 | Skype: einhverfr | www.adjust.com 


Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

srielau
In reply to this post by Pavel Stehule
Pavel,

I wouldn't put in the DROP option.
Or at least not in that form of syntax.

By convention CREATE persists DDL and makes object definitions visible
across sessions.
DECLARE defines session private objects which cannot collide with other
sessions.

If you want variables with a short lifetime that get dropped at the end of
the transaction that by definition would imply a session private object. So
it ought to be DECLARE'd.

As far as I can see PG has been following this practice so far.

Cheers
Serge Rielau
Salesforce.com



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule
Hi

2017-10-30 22:42 GMT+01:00 srielau <[hidden email]>:
Pavel,

I wouldn't put in the DROP option.
Or at least not in that form of syntax.

By convention CREATE persists DDL and makes object definitions visible
across sessions.
DECLARE defines session private objects which cannot collide with other
sessions.

If you want variables with a short lifetime that get dropped at the end of
the transaction that by definition would imply a session private object. So
it ought to be DECLARE'd.

As far as I can see PG has been following this practice so far.

I am thinking so there is little bit overlap between DECLARE and CREATE TEMP VARIABLE command. With DECLARE command, you are usually has not any control when variable will be destroyed. For CREATE TEMP xxxx is DROP IF EXISTS, but it should not be used.

It should be very similar to our current temporary tables, that are created in session related temp schema.

I can imagine, so DECLARE command will be introduced as short cut for CREATE TEMP VARIABLE, but in this moment I would not to open this topic. I afraid of bikeshedding and I hope so CREATE TEMP VAR is anough.

Regards

Pavel


Cheers
Serge Rielau
Salesforce.com



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

srielau
Pavel, 
I can imagine, so DECLARE command will be introduced as short cut for CREATE TEMP VARIABLE, but in this moment I would not to open this topic. I afraid of bikeshedding and I hope so CREATE TEMP VAR is anough.
Language is important because language stays. 
You choice of syntax will outlive your code and possibly yourself.

My 2 cents
Serge 
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule


2017-10-31 22:08 GMT+01:00 Serge Rielau <[hidden email]>:
Pavel, 
I can imagine, so DECLARE command will be introduced as short cut for CREATE TEMP VARIABLE, but in this moment I would not to open this topic. I afraid of bikeshedding and I hope so CREATE TEMP VAR is anough.
Language is important because language stays. 
You choice of syntax will outlive your code and possibly yourself.

sure. But in this moment I don't see difference between DECLARE VARIABLE and CREATE TEMP VARIABLE different than "TEMP" keyword.

Regards

Pavel


My 2 cents
Serge 

Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

srielau
Pavel,

There is no
DECLARE TEMP CURSOR
or
DECLARE TEMP variable in PLpgSQL
and
CREATE TEMP TABLE has a different meaning from what I understand you
envision for variables.

But maybe I'm mistaken. Your original post did not describe the entire
syntax:
CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

Especially the TEMP is not spelled out and how its presence affects or
doesn't ON TRANSACTION END.
So may be if you elaborate I understand where you are coming from.

 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Gilles Darold
Le 31/10/2017 à 22:28, srielau a écrit :

> Pavel,
>
> There is no
> DECLARE TEMP CURSOR
> or
> DECLARE TEMP variable in PLpgSQL
> and
> CREATE TEMP TABLE has a different meaning from what I understand you
> envision for variables.
>
> But maybe I'm mistaken. Your original post did not describe the entire
> syntax:
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> Especially the TEMP is not spelled out and how its presence affects or
> doesn't ON TRANSACTION END.
> So may be if you elaborate I understand where you are coming from.

I think that the TEMP keyword can be removed. If I understand well the
default scope for variable is the session, every transaction in a
session will see the same value. For the transaction level, probably the
reason of the TEMP keyword, I think the [ ON TRANSACTION END { RESET |
DROP } ] will allow to restrict the scope to this transaction level
without needing the TEMP keyword. When a variable is created in a
transaction, it is temporary if "ON TRANSACTION END DROP" is used
otherwise it will persist after the transaction end. I guess that this
is the same as using TEMP keyword?


--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Gilles Darold
Le 31/10/2017 à 23:36, Gilles Darold a écrit :

> Le 31/10/2017 à 22:28, srielau a écrit :
>> Pavel,
>>
>> There is no
>> DECLARE TEMP CURSOR
>> or
>> DECLARE TEMP variable in PLpgSQL
>> and
>> CREATE TEMP TABLE has a different meaning from what I understand you
>> envision for variables.
>>
>> But maybe I'm mistaken. Your original post did not describe the entire
>> syntax:
>> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>>   [ DEFAULT expression ] [[NOT] NULL]
>>   [ ON TRANSACTION END { RESET | DROP } ]
>>   [ { VOLATILE | STABLE } ];
>>
>> Especially the TEMP is not spelled out and how its presence affects or
>> doesn't ON TRANSACTION END.
>> So may be if you elaborate I understand where you are coming from.
> I think that the TEMP keyword can be removed. If I understand well the
> default scope for variable is the session, every transaction in a
> session will see the same value. For the transaction level, probably the
> reason of the TEMP keyword, I think the [ ON TRANSACTION END { RESET |
> DROP } ] will allow to restrict the scope to this transaction level
> without needing the TEMP keyword. When a variable is created in a
> transaction, it is temporary if "ON TRANSACTION END DROP" is used
> otherwise it will persist after the transaction end. I guess that this
> is the same as using TEMP keyword?

I forgot to say that in the last case the DECLARE statement can be used
so I don't see the reason of this kind of "temporary" variables.

Maybe the variable object like used in DB2 and defined in document :
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_createvariable.html
could be enough to cover our needs.


--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: proposal: schema variables

Pavel Stehule
In reply to this post by srielau


2017-10-31 22:28 GMT+01:00 srielau <[hidden email]>:
Pavel,

There is no
DECLARE TEMP CURSOR
or
DECLARE TEMP variable in PLpgSQL
and

sure .. DECLARE TEMP has no sense, I talked about similarity DECLARE and CREATE TEMP


CREATE TEMP TABLE has a different meaning from what I understand you
envision for variables.

But maybe I'm mistaken. Your original post did not describe the entire
syntax:
CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

Especially the TEMP is not spelled out and how its presence affects or
doesn't ON TRANSACTION END.
So may be if you elaborate I understand where you are coming from.

TEMP has same functionality (and implementation) like our temp tables - so at session end the temp variables are destroyed, but it can be assigned to transaction.







--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

1234 ... 9