Re: proposal: schema variables

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

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

Hi

2017-10-27 0:07 GMT+02:00 Nico Williams <nico(at)cryptonector(dot)com>:

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

> 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

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] 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

2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>:

> From: pgsql-hackers-owner(at)postgresql(dot)org
> > [mailto:pgsql-hackers-owner(at)postgresql(dot)org] 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
>
>
>

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

2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:

> 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 (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
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

Hi

2017-10-28 16:24 GMT+02:00 Chris Travers <chris(dot)travers(at)adjust(dot)com>:

>
>
> On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> 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: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
> www.adjust.com
> Saarbrücker Straße 37a, 10405 Berlin
> <https://maps.google.com/?q=Saarbr%C3%BCcker+Stra%C3%9Fe+37a,+10405+Berlin&entry=gmail&source=g>
>
>

On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
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: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
>> www.adjust.com
>> Saarbrücker Straße 37a, 10405 Berlin
>> <https://maps.google.com/?q=Saarbr%C3%BCcker+Stra%C3%9Fe+37a,+10405+Berlin&entry=gmail&source=g>
>>
>>
>

--
Best Regards,
Chris Travers
Database Administrator

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

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 <chris(dot)travers(at)adjust(dot)com> wrote:

>
>
> On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> 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: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
>>> www.adjust.com
>>> Saarbrücker Straße 37a, 10405 Berlin
>>> <https://maps.google.com/?q=Saarbr%C3%BCcker+Stra%C3%9Fe+37a,+10405+Berlin&entry=gmail&source=g>
>>>
>>>
>>
>
>
> --
> Best Regards,
> Chris Travers
> Database Administrator
>
> Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
> www.adjust.com
> Saarbrücker Straße 37a, 10405 Berlin
> <https://maps.google.com/?q=Saarbr%C3%BCcker+Stra%C3%9Fe+37a,+10405+Berlin&entry=gmail&source=g>
>
>

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

Hi

2017-10-30 22:42 GMT+01:00 srielau <serge(at)rielau(dot)com>:

> 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 (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

2017-10-31 22:08 GMT+01:00 Serge Rielau <serge(at)rielau(dot)com>:

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

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

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

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

2017-10-31 22:28 GMT+01:00 srielau <serge(at)rielau(dot)com>:

> 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 (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

" Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.”
Yeah, that’s a DECLAREd table in my book. No wonder we didn’t link up.
Cheers Serge

2017-11-01 6:07 GMT+01:00 Serge Rielau <serge(at)rielau(dot)com>:

> "Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
> standard, the effect is not the same. In the standard, temporary tables are
> defined just once and automatically exist (starting with empty contents) in
> every session that needs them. PostgreSQL instead requires each session
> to issue its own CREATE TEMPORARY TABLE command for each temporary table
> to be used. This allows different sessions to use the same temporary table
> name for different purposes, whereas the standard's approach constrains all
> instances of a given temporary table name to have the same table structure.”
> Yeah, that’s a DECLAREd table in my book. No wonder we didn’t link up.
>

This is known discussion about local / global temp tables in PostgresSQL.
And ToDo point: implementation of global temp tables in Postgres.

This temporary behave is marginal part of proposal - so I can to remove it
from proposal - and later open discussion about CREATE TEMPORARY VARIABLE
versus DECLARE VARIABLE

Regards

Pavel

Serge

>

> Comments, notes?

How would variables behave on transaction rollback?

CREATE TEMP VARIABLE myvar;
SET myvar := 1;
BEGIN;
SET myvar := 2;
COMMIT;
BEGIN;
SET myvar := 3;
ROLLBACK;
SELECT myvar;

How would variables behave when modified in a procedure
that aborts rather than returning cleanly?

mark

2017-11-01 19:03 GMT+01:00 Mark Dilger <hornschnorter(at)gmail(dot)com>:

>
> > Comments, notes?
>
> How would variables behave on transaction rollback?
>
> CREATE TEMP VARIABLE myvar;
> SET myvar := 1;
> BEGIN;
> SET myvar := 2;
> COMMIT;
> BEGIN;
> SET myvar := 3;
> ROLLBACK;
> SELECT myvar;
>
> How would variables behave when modified in a procedure
> that aborts rather than returning cleanly?
>
>
The result is 3

When you create variable like you did, then there are not any relation
between variable content and transactions. Almost every where session -
package - schema variables are untransactional. It can be changed, but with
negative impact on performance - so I propose relative simply solution -
reset to default on rollback, when variables was changed in transaction -
but it is not default behave.

Variables are variables like you know from PlpgSQL. But the holder is not
the plpgsql function. The holder is a schema in this case. The variable
(meta) is permanent. The content of variable is session based
untransactional.

Regards

Pavel

> mark
>

Le 01/11/2017 à 05:15, Pavel Stehule a écrit :
>
>
> 2017-10-31 22:28 GMT+01:00 srielau <serge(at)rielau(dot)com
> <mailto:serge(at)rielau(dot)com>>:
>
> 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.

Oh ok, I understand thanks for the precision.

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

On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> The variables can be modified by SQL command SET (this is taken from
> standard, and it natural)
>
> SET varname = expression;

Overloading SET to handle both variables and GUCs seems likely to
create problems, possibly including security problems. For example,
maybe a security-definer function could leave behind variables to
trick the calling code into failing to set GUCs that it intended to
set. Or maybe creating a variable at the wrong time will just break
things randomly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 26 October 2017 at 15:21, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hi,
>
> I propose a new database object - a variable.

Didn't we have a pretty long discussion about this already in

Yeah.

https://www.postgresql.org/message-id/flat/CAMsr%2BYF0G8_FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw%40mail(dot)gmail(dot)com#CAMsr+YF0G8_FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw(at)mail(dot)gmail(dot)com

It'd be nice if you summarised any outcomes from that and addressed
it, rather than taking this as a new topic.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Nov 02, 2017 at 06:05:54PM +0530, Robert Haas wrote:
> On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > The variables can be modified by SQL command SET (this is taken from
> > standard, and it natural)
> >
> > SET varname = expression;
>
> Overloading SET to handle both variables and GUCs seems likely to
> create problems, possibly including security problems. For example,
> maybe a security-definer function could leave behind variables to
> trick the calling code into failing to set GUCs that it intended to
> set. Or maybe creating a variable at the wrong time will just break
> things randomly.

That's already true of GUCs, since there are no access controls on
set_config()/current_setting().

Presumably "schema variables" would really just be GUC-like and not at
all like lexically scoped variables. And also subject to access
controls, thus an overall improvement on set_config()/current_setting().

With access controls, GUCs could become schema variables, and settings
from postgresql.conf could move into the database itself (which I think
would be nice).

Nico
--

2017-11-02 16:35 GMT+01:00 Nico Williams <nico(at)cryptonector(dot)com>:

> On Thu, Nov 02, 2017 at 06:05:54PM +0530, Robert Haas wrote:
> > On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > > The variables can be modified by SQL command SET (this is taken from
> > > standard, and it natural)
> > >
> > > SET varname = expression;
> >
> > Overloading SET to handle both variables and GUCs seems likely to
> > create problems, possibly including security problems. For example,
> > maybe a security-definer function could leave behind variables to
> > trick the calling code into failing to set GUCs that it intended to
> > set. Or maybe creating a variable at the wrong time will just break
> > things randomly.
>
> That's already true of GUCs, since there are no access controls on
> set_config()/current_setting().
>
> Presumably "schema variables" would really just be GUC-like and not at
> all like lexically scoped variables. And also subject to access
> controls, thus an overall improvement on set_config()/current_setting().
>
> With access controls, GUCs could become schema variables, and settings
> from postgresql.conf could move into the database itself (which I think
> would be nice).
>

I am sorry, but I don't plan it. the behave of GUC is too different than
behave of variables. But I am planning so system GUC can be "moved" to
pg_catalog to be possibility to specify any object exactly.

Regards

Pavel

>
> Nico
> --
>

2017-11-02 16:07 GMT+01:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 26 October 2017 at 15:21, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > Hi,
> >
> > I propose a new database object - a variable.
>
> Didn't we have a pretty long discussion about this already in
>
> Yeah.
>
> https://www.postgresql.org/message-id/flat/CAMsr%2BYF0G8_
> FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw%40mail.gmail.com#CAMsr+YF0G8_
> FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw(at)mail(dot)gmail(dot)com
>
> It'd be nice if you summarised any outcomes from that and addressed
> it, rather than taking this as a new topic.
>

I am sorry. This thread follow mentioned and I started with small
recapitulation.

Regards

Pavel

> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

Nico Williams <nico(at)cryptonector(dot)com> writes:
> With access controls, GUCs could become schema variables, and settings
> from postgresql.conf could move into the database itself (which I think
> would be nice).

People re-propose some variant of that every so often, but it never works,
because it ignores the fact that some of the GUCs' values are needed
before you can access system catalogs at all, or in places where relying
on system catalog access would be a bad idea.

Sure, we could have two completely different configuration mechanisms
so that some of the variables could be "inside the database", but that
doesn't seem like a net improvement to me. The point of the Grand Unified
Configuration mechanism was to be unified, after all.

I'm on board with having a totally different mechanism for session
variables. The fact that people have been abusing GUC to store
user-defined variables doesn't make it a good way to do that.

regards, tom lane

2017-11-02 13:35 GMT+01:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > The variables can be modified by SQL command SET (this is taken from
> > standard, and it natural)
> >
> > SET varname = expression;
>
> Overloading SET to handle both variables and GUCs seems likely to
> create problems, possibly including security problems. For example,
> maybe a security-definer function could leave behind variables to
> trick the calling code into failing to set GUCs that it intended to
> set. Or maybe creating a variable at the wrong time will just break
> things randomly.
>

The syntax CREATE OR REPLACE FUNCTION xxx $$ ... $$ SET GUC=, ... is always
related only to GUC. So there should not be any security risk.

It is another reason why GUC and variables should be separated.

I know so there is risk of possibility of collision. There are two
possibilities

a) use different keyword - but it is out of SQL/PSM and out of another
databases.

b) detect possible collision and raise error when assignment is ambiguous.
I am thinking about similar solution used in plpgsql, where is a
possibility of collision between SQL identifier and plpgsql variable.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

On Thu, Nov 2, 2017 at 9:05 PM, Nico Williams <nico(at)cryptonector(dot)com> wrote:
>> Overloading SET to handle both variables and GUCs seems likely to
>> create problems, possibly including security problems. For example,
>> maybe a security-definer function could leave behind variables to
>> trick the calling code into failing to set GUCs that it intended to
>> set. Or maybe creating a variable at the wrong time will just break
>> things randomly.
>
> That's already true of GUCs, since there are no access controls on
> set_config()/current_setting().

No, it isn't. Right now, SET always refers to a GUC, never a
variable, so there's no possibility of getting confused about whether
it's intending to change a GUC or an eponymous variable. Once you
make SET able to change either one of two different kinds of objects,
then that possibility does exist.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Nov 02, 2017 at 11:48:44AM -0400, Tom Lane wrote:
> Nico Williams <nico(at)cryptonector(dot)com> writes:
> > With access controls, GUCs could become schema variables, and settings
> > from postgresql.conf could move into the database itself (which I think
> > would be nice).
>
> People re-propose some variant of that every so often, but it never works,
> because it ignores the fact that some of the GUCs' values are needed
> before you can access system catalogs at all, or in places where relying
> on system catalog access would be a bad idea.

ISTM that it should be possible to break the chicken-egg issue by having
the config variables stored in such a way that knowing only the pgdata
directory path should suffice to find them. That's effectively the case
already in that postgresql.conf is found... there.

One could do probably this as a PoC entirely as a SQL-coded VIEW that
reads and writes (via the adminpack module's pg_catalog.pg_file_write())
postgresql.conf (without preserving comments, or with some rules
regarding comments so that they are effectively attached to params).

Nico
--

Some thoughts on this.

On Thu, Nov 2, 2017 at 4:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Nico Williams <nico(at)cryptonector(dot)com> writes:
> > With access controls, GUCs could become schema variables, and settings
> > from postgresql.conf could move into the database itself (which I think
> > would be nice).
>
> People re-propose some variant of that every so often, but it never works,
> because it ignores the fact that some of the GUCs' values are needed
> before you can access system catalogs at all, or in places where relying
> on system catalog access would be a bad idea.
>

I think the basic point one should get here is that no matter the
unification, you still have some things in the db and some things out.

I would rather look at how the GUC could be improved on a functional/use
case level before we look at the question of a technical solution.

One major use case today would be restricting how high various users can
set something like work_mem or the like. As it stands, there isn't really
a way to control this with any granularity. So some of the proposals
regarding granting access to a session variable would be very handy in
granting access to a GUC variable.

>
> Sure, we could have two completely different configuration mechanisms
> so that some of the variables could be "inside the database", but that
> doesn't seem like a net improvement to me. The point of the Grand Unified
> Configuration mechanism was to be unified, after all.
>

+1

>
> I'm on board with having a totally different mechanism for session
> variables. The fact that people have been abusing GUC to store
> user-defined variables doesn't make it a good way to do that.
>

What about having a more clunky syntax as:

SET VARIABLE foo='bar';

Perhaps one can have a short form of:

SET VAR foo = 'bar';

vs

SET foo = 'bar'; -- GUC

>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Best Regards,
Chris Travers
Database Administrator

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

Hello, Pavel.

You wrote:

PS> Hi,

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

Great idea.

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

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

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

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

PS> The variable can be created by CREATE statement:

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

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

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

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

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

PS> SET varname = expression;

I propose LET keyword for this to distinguish GUC from variables, e.g.

LET varname = expression;

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

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

PS> SELECT varname;

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

PS> I hope so this proposal is good enough and simple.

PS> Comments, notes?

PS> regards

PS> Pavel

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

Hi

2017-11-13 13:15 GMT+01:00 Pavel Golub <pavel(at)microolap(dot)com>:

> Hello, Pavel.
>
> You wrote:
>
> PS> Hi,
>
> PS> I propose a new database object - a variable. The variable is
> PS> persistent object, that holds unshared session based not
> PS> transactional in memory value of any type. Like variables in any
> PS> other languages. The persistence is required for possibility to do
> PS> static checks, but can be limited to session - the variables can be
> temporal.
>
> Great idea.
>
> PS> My proposal is related to session variables from Sybase, MSSQL or
> PS> MySQL (based on prefix usage @ or @@), or package variables from
> PS> Oracle (access is controlled by scope), or schema variables from
> PS> DB2. Any design is coming from different sources, traditions and
> PS> has some advantages or disadvantages. The base of my proposal is
> PS> usage schema variables as session variables for stored procedures.
> PS> It should to help to people who try to port complex projects to
> PostgreSQL from other databases.
>
> PS> The Sybase (T-SQL) design is good for interactive work, but it
> PS> is weak for usage in stored procedures - the static check is not
> PS> possible. Is not possible to set some access rights on variables.
>
> PS> The ADA design (used on Oracle) based on scope is great, but our
> PS> environment is not nested. And we should to support other PL than
> PLpgSQL more strongly.
>
> PS> There is not too much other possibilities - the variable that
> PS> should be accessed from different PL, different procedures (in
> PS> time) should to live somewhere over PL, and there is the schema only.
>
> PS> The variable can be created by CREATE statement:
>
> PS> CREATE VARIABLE public.myvar AS integer;
> PS> CREATE VARIABLE myschema.myvar AS mytype;
>
> PS> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
> PS> [ DEFAULT expression ] [[NOT] NULL]
> PS> [ ON TRANSACTION END { RESET | DROP } ]
> PS> [ { VOLATILE | STABLE } ];
>
>
> PS> It is dropped by command DROP VARIABLE [ IF EXISTS] varname.
>
> PS> The access rights is controlled by usual access rights - by
> PS> commands GRANT/REVOKE. The possible rights are: READ, WRITE
>
> PS> The variables can be modified by SQL command SET (this is taken from
> standard, and it natural)
>
> PS> SET varname = expression;
>
> I propose LET keyword for this to distinguish GUC from variables, e.g.
>
> LET varname = expression;
>

It is one possible variant. I plan to implement more variants and then
choose one.

Regards

Pavel

>
> PS> Unfortunately we use the SET command for different purpose. But I
> PS> am thinking so we can solve it with few tricks. The first is
> PS> moving our GUC to pg_catalog schema. We can control the strictness
> PS> of SET command. In one variant, we can detect custom GUC and allow
> PS> it, in another we can disallow a custom GUC and allow only schema
> PS> variables. A new command LET can be alternative.
>
>
>
> PS> The variables should be used in queries implicitly (without JOIN)
>
>
> PS> SELECT varname;
>
>
> PS> The SEARCH_PATH is used, when varname is located. The variables
> PS> can be used everywhere where query parameters are allowed.
>
>
>
> PS> I hope so this proposal is good enough and simple.
>
>
> PS> Comments, notes?
>
>
> PS> regards
>
>
> PS> Pavel
>
>
>
>
>
>
> --
> With best wishes,
> Pavel mailto:pavel(at)gf(dot)microolap(dot)com
>
>

Hi

I wrote proof concept of schema variables. The patch is not nice, but the
functionality is almost complete (for scalars only) and can be good enough
for playing with this concept.

I recap a goals (the order is random):

1. feature like PL/SQL package variables (with similar content life cycle)
2. available from any PL used by PostgreSQL, data can be shared between
different PL
3. possibility to store short life data in fast secured storage
4. possibility to pass parameters and results to/from anonymous blocks
5. session variables with possibility to process static code check
6. multiple API available from different environments - SQL commands, SQL
functions, internal functions
7. data are stored in binary form

Example:

CREATE VARIABLE public.foo AS integer;

LET foo = 10 + 20;

DO $$
declare x int = random() * 1000;
BEGIN
RAISE NOTICE '%', foo;
LET foo = x + 100;
END;
$$;

SELECT public.foo + 10;
SELECT * FROM data WHERE col = foo;

All implemented features are described by regress tests

Interesting note - it is running without any modification of plpgsql code.

Regards

Pavel

​I've done a non-compilation documentation review, the diff from the poc
patch and the diff from master are attached.

Comments are inter-twined in the patch in xml comment format; though I
reiterate (some of?) them below.

On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> I wrote proof concept of schema variables. The patch is not nice, but the
> functionality is almost complete (for scalars only) and can be good enough
> for playing with this concept.
>
> I recap a goals (the order is random):
>
> 1. feature like PL/SQL package variables (with similar content life cycle)
> 2. available from any PL used by PostgreSQL, data can be shared between
> different PL
> 3. possibility to store short life data in fast secured storage
>

​The generic use of the word secure here bothers me. I'm taking it to be
"protected by grant/revoke"-based privileges; plus session-locality.

4. possibility to pass parameters and results to/from anonymous blocks
> 5. session variables with possibility to process static code check
>

What does "process static code check" means here?​

> 6. multiple API available from different environments - SQL commands, SQL
> functions, internal functions
>

I made the public aspect of this explicit in the CREATE VARIABLE doc
(though as noted below it probably belongs in section II)
​

> 7. data are stored in binary form
>

Thoughts during my review:

There is, for me, a cognitive dissonance between "schema variable" and
"variable value" - I'm partial to the later. Since we use "setting" for
GUCs the term variable here hopefully wouldn't cause ambiguity...

I've noticed that we don't seem to have or enforce any policy on how to
communicate "SQL standards compatibility" to the user...

We are missing the ability to alter ownership (or at least its
undocumented), and if that brings into existing ALTER VARIABLE we should
probably add ALTER TYPE TO new_type USING (cast) for completeness.

Its left for the reader to presume that because these are schema
"relations" that namespace resolution via search_path works the same as any
other relation.

I think I've answered my own question regarding DISCARD in that "variables"
discards values while if TEMP is in effect all temp variables are dropped.

Examples abound though it doesn't feel like too much: but saying "The usage
is very simple:" before giving the example in the function section seems to
be outside of our general style. A better preamble than "An example:"
would be nice but the example is so simple I could not think of anything
worth writing.

Its worth considering how both:

https://www.postgresql.org/docs/10/static/ddl.html
and
https://www.postgresql.org/docs/10/static/queries.html

could be updated to incorporate the broad picture of schema variables, with
examples, and leave the reference (SQL and functions) sections mainly
relegated to syntax and reminders.

A moderate number of lines changed are for typos and minor grammar edits.

David J.

Hi

2018-02-03 1:48 GMT+01:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> ​I've done a non-compilation documentation review, the diff from the poc
> patch and the diff from master are attached.
>
> Comments are inter-twined in the patch in xml comment format; though I
> reiterate (some of?) them below.
>
> On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> I wrote proof concept of schema variables. The patch is not nice, but the
>> functionality is almost complete (for scalars only) and can be good enough
>> for playing with this concept.
>>
>> I recap a goals (the order is random):
>>
>> 1. feature like PL/SQL package variables (with similar content life cycle)
>> 2. available from any PL used by PostgreSQL, data can be shared between
>> different PL
>> 3. possibility to store short life data in fast secured storage
>>
>
> ​The generic use of the word secure here bothers me. I'm taking it to be
> "protected by grant/revoke"-based privileges; plus session-locality.
>

I have not a problem with any other formulation.

>
> 4. possibility to pass parameters and results to/from anonymous blocks
>> 5. session variables with possibility to process static code check
>>
>
> What does "process static code check" means here?​
>

It mean the possibility to check validity of code without code execution.
You can use plpgsql_check for example.

>
>
>> 6. multiple API available from different environments - SQL commands, SQL
>> functions, internal functions
>>
>
> I made the public aspect of this explicit in the CREATE VARIABLE doc
> (though as noted below it probably belongs in section II)
> ​
>
>> 7. data are stored in binary form
>>
>
> Thoughts during my review:
>
> There is, for me, a cognitive dissonance between "schema variable" and
> "variable value" - I'm partial to the later. Since we use "setting" for
> GUCs the term variable here hopefully wouldn't cause ambiguity...
>

The "schema" is important in this case. 1) it is a analogy to "package
variable", 2) not necessary, but probably often it will be used together
with PLpgSQL. There are variables too. "Session variables" doesn't well
specify the implementation. The session variables can be GUC, psql client
variables or some custom implementation in Postgres or package variables in
Oracle.

> I've noticed that we don't seem to have or enforce any policy on how to
> communicate "SQL standards compatibility" to the user...
>
> We are missing the ability to alter ownership (or at least its
> undocumented), and if that brings into existing ALTER VARIABLE we should
> probably add ALTER TYPE TO new_type USING (cast) for completeness.
>

good note. I didn't test it. I am not sure, what variants of ALTER should
be supported. Type of variables is interface. Probably we can allow to add
new field, but change type or remove field can break other object. So it
can be prohibited like we doesn't support ALTER on views. ALTERing is
another and pretty complex topic, and I don't think it is necessary to
solve it now. This feature can be valuable without ALTER support, and
nothing block later ALTER VARIABLE implementation.

This design allows lot of interesting features (that can be implemented
step by step)

1. support for default expression
2. support for constraints and maybe triggers
3. reset on transaction end
4. initialization of session start - via default expression or triggers it
can be way how to start code on session start.

>
> Its left for the reader to presume that because these are schema
> "relations" that namespace resolution via search_path works the same as any
> other relation.
>
> I think I've answered my own question regarding DISCARD in that
> "variables" discards values while if TEMP is in effect all temp variables
> are dropped.
>

DISCARD should to remove TEMP variables and should to remove content of all
variables.

>
> Examples abound though it doesn't feel like too much: but saying "The
> usage is very simple:" before giving the example in the function section
> seems to be outside of our general style. A better preamble than "An
> example:" would be nice but the example is so simple I could not think of
> anything worth writing.
>

This doc is just design frame. I invite any enhancing because this feature
can be difficult for some people, because mix persistent object with
temporal/session content - and term "variable" can be used in relation
algebra in different semantic. It is natural for people with stored
procedures experience - mainly with Oracle, but for any other can be little
bit difficult. I believe so there should be more practical examples -
related to RLS for example.

>
> Its worth considering how both:
>
> https://www.postgresql.org/docs/10/static/ddl.html
> and
> https://www.postgresql.org/docs/10/static/queries.html
>
> could be updated to incorporate the broad picture of schema variables,
> with examples, and leave the reference (SQL and functions) sections mainly
> relegated to syntax and reminders.
>
> A moderate number of lines changed are for typos and minor grammar edits.
>

Thank you very much

Regards

Pavel

> David J.
>
>

Hi

updated patch with your changes in documentation and pg_dump (initial)
support

Main issue of this patch is storage. We can reuse local buffers used for
temp tables. But it does allocation by 8KB and it creates temp files for
every object. That is too big overhead. Storing just in session memory is
too simple - then there should be lot of new code used, when variable will
be dropped.

I have ideas how to allow work with mix of scalar and composite types - so
it will be next step of this prototype.

Regards

Pavel

>

Hi

2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> updated patch with your changes in documentation and pg_dump (initial)
> support
>
> Main issue of this patch is storage. We can reuse local buffers used for
> temp tables. But it does allocation by 8KB and it creates temp files for
> every object. That is too big overhead. Storing just in session memory is
> too simple - then there should be lot of new code used, when variable will
> be dropped.
>
> I have ideas how to allow work with mix of scalar and composite types - so
> it will be next step of this prototype.
>
> Regards
>
> Pavel
>

new update - rebased, + some initial support for composite values on right
side and custom types, arrays are supported too.

omega=# CREATE VARIABLE xx AS (a int, b numeric);
CREATE VARIABLE
omega=# LET xx = (10, 20)::xx;
LET
omega=# SELECT xx;
+---------+
| xx |
+---------+
| (10,20) |
+---------+
(1 row)

omega=# SELECT xx.a + xx.b;
+----------+
| ?column? |
+----------+
| 30 |
+----------+
(1 row)

omega=# \d xx
schema variable "public.xx"
+--------+---------+
| Column | Type |
+--------+---------+
| a | integer |
| b | numeric |
+--------+---------+

Regards

Pavel

Hi,

I plan to make usability and feature test review in several days.

Is there any chances that it will work on replicas?
Such possibility is very helpful in generating reports.
Now, LET command produces an error:

ERROR:  cannot execute LET in a read-only transaction

But if we say that variables are non-transactional ?

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 08.03.2018 21:00, Pavel Stehule wrote:
> Hi
>
> 2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>>:
>
> Hi
>
> updated patch with your changes in documentation and pg_dump
> (initial) support
>
> Main issue of this patch is storage. We can reuse local buffers
> used for temp tables. But it does allocation by 8KB and it creates
> temp files for every object. That is too big overhead. Storing
> just in session memory is too simple - then there should be lot of
> new code used, when variable will be dropped.
>
> I have ideas how to allow work with mix of scalar and composite
> types - so it will be next step of this prototype.
>
> Regards
>
> Pavel
>
>
> new update - rebased, + some initial support for composite values on
> right side and custom types, arrays are supported too.
>
> omega=# CREATE VARIABLE xx AS (a int, b numeric);
> CREATE VARIABLE
> omega=# LET xx = (10, 20)::xx;
> LET
> omega=# SELECT xx;
> +---------+
> |   xx    |
> +---------+
> | (10,20) |
> +---------+
> (1 row)
>
> omega=# SELECT xx.a + xx.b;
> +----------+
> | ?column? |
> +----------+
> |       30 |
> +----------+
> (1 row)
>
> omega=# \d xx
> schema variable "public.xx"
> +--------+---------+
> | Column |  Type   |
> +--------+---------+
> | a      | integer |
> | b      | numeric |
> +--------+---------+
>
>
> Regards
>
> Pavel
>
>

2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>:

> Hi,
>
> I plan to make usability and feature test review in several days.
>
> Is there any chances that it will work on replicas?
> Such possibility is very helpful in generating reports.
> Now, LET command produces an error:
>
> ERROR: cannot execute LET in a read-only transaction
>
>

> But if we say that variables are non-transactional ?
>

sure, it should to work. Now, I am try to solve a issues on concept level -
the LET code is based on DML code base, so probably there is check for rw
transactions. But it is useless for LET command.

Regards

Pavel

>
> -----
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
> On 08.03.2018 21:00, Pavel Stehule wrote:
>
> Hi
>
> 2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>> Hi
>>
>> updated patch with your changes in documentation and pg_dump (initial)
>> support
>>
>> Main issue of this patch is storage. We can reuse local buffers used for
>> temp tables. But it does allocation by 8KB and it creates temp files for
>> every object. That is too big overhead. Storing just in session memory is
>> too simple - then there should be lot of new code used, when variable will
>> be dropped.
>>
>> I have ideas how to allow work with mix of scalar and composite types -
>> so it will be next step of this prototype.
>>
>> Regards
>>
>> Pavel
>>
>
> new update - rebased, + some initial support for composite values on right
> side and custom types, arrays are supported too.
>
> omega=# CREATE VARIABLE xx AS (a int, b numeric);
> CREATE VARIABLE
> omega=# LET xx = (10, 20)::xx;
> LET
> omega=# SELECT xx;
> +---------+
> | xx |
> +---------+
> | (10,20) |
> +---------+
> (1 row)
>
> omega=# SELECT xx.a + xx.b;
> +----------+
> | ?column? |
> +----------+
> | 30 |
> +----------+
> (1 row)
>
> omega=# \d xx
> schema variable "public.xx"
> +--------+---------+
> | Column | Type |
> +--------+---------+
> | a | integer |
> | b | numeric |
> +--------+---------+
>
>
> Regards
>
> Pavel
>
>
>
>

On 12.03.2018 09:54, Pavel Stehule wrote:
>
> 2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru
> <mailto:p(dot)luzanov(at)postgrespro(dot)ru>>:
>
>
> Is there any chances that it will work on replicas?
>
> ...
>
> sure, it should to work. Now, I am try to solve a issues on concept
> level - the LET code is based on DML code base, so probably there is
> check for rw transactions. But it is useless for LET command.

Very, very good!

As I understand, the work on this patch now in progress and it not in
commitfest.
Please explain what features of schema variables I can review now.

From first post of this thread the syntax of the CREATE VARIABLE command:
CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

But in psql I see only:
\h create variable
Command:     CREATE VARIABLE
Description: define a new permissioned typed schema variable
Syntax:
CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]

I can include DEFAULT clause in CREATE VARIABLE command, but the value
not used:
postgres=# create variable i int default 0;
CREATE VARIABLE
postgres=# select i;
 i
---

(1 row)

postgres=# \d+ i
 schema variable "public.i"
 Column |  Type   | Storage
--------+---------+---------
 i      | integer | plain

BTW, I found an error in handling of table aliases:

postgres=# create variable x text;
CREATE VARIABLE
postgres=# select * from pg_class AS x where x.relname = 'x';
ERROR:  type text is not composite

It thinks that x.relname is an attribute of x variable instead of an
alias for pg_class table.

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

2018-03-12 16:38 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>:

>
> On 12.03.2018 09:54, Pavel Stehule wrote:
>
>
> 2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>:
>
>>
>> Is there any chances that it will work on replicas?
>>
> ...
>
> sure, it should to work. Now, I am try to solve a issues on concept level
> - the LET code is based on DML code base, so probably there is check for rw
> transactions. But it is useless for LET command.
>
>
> Very, very good!
>
> As I understand, the work on this patch now in progress and it not in
> commitfest.
> Please explain what features of schema variables I can review now.
>
> From first post of this thread the syntax of the CREATE VARIABLE command:
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
> [ DEFAULT expression ] [[NOT] NULL]
> [ ON TRANSACTION END { RESET | DROP } ]
> [ { VOLATILE | STABLE } ];
>

Now, it is too early for review - it is in development. Some features are
not implemented yet - DEFAULTs, ON TRANSACTION END .., others has not sense
(what I know now VOLATILE, STABLE). Schema variables are passed as
parameters to query, so the behave is like any other params - it is STABLE
only.

>
> But in psql I see only:
> \h create variable
> Command: CREATE VARIABLE
> Description: define a new permissioned typed schema variable
> Syntax:
> CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]
>
> I can include DEFAULT clause in CREATE VARIABLE command, but the value not
> used:
> postgres=# create variable i int default 0;
> CREATE VARIABLE
> postgres=# select i;
> i
> ---
>
> (1 row)
>
> postgres=# \d+ i
> schema variable "public.i"
> Column | Type | Storage
> --------+---------+---------
> i | integer | plain
>
>
defaults are not implemented yet

>
> BTW, I found an error in handling of table aliases:
>
> postgres=# create variable x text;
> CREATE VARIABLE
> postgres=# select * from pg_class AS x where x.relname = 'x';
> ERROR: type text is not composite
>
> It thinks that x.relname is an attribute of x variable instead of an alias
> for pg_class table.
>
>
It is not well handled collision. This should be detected and prohibited.
In this case, because x is scalar, then x.xx has not sense, and then it
should not be handled like variable. So the current design is not too
practical - it generates more collisions than it is necessary and still,
there are some errors.

Now, there is one important question - storage - Postgres stores all
objects to files - only memory storage is not designed yet. This is part,
where I need a help.

Regards

Pavel

>
> -----
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

Pavel Stehule wrote
> Now, there is one important question - storage - Postgres stores all
> objects to files - only memory storage is not designed yet. This is part,
> where I need a help.

O, I do not feel confident in such questions.
May be some ideas you can get from extension with similar functionality:
https://github.com/postgrespro/pg_variables

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

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

2018-03-13 10:54 GMT+01:00 Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru>:

> Pavel Stehule wrote
> > Now, there is one important question - storage - Postgres stores all
> > objects to files - only memory storage is not designed yet. This is part,
> > where I need a help.
>
> O, I do not feel confident in such questions.
> May be some ideas you can get from extension with similar functionality:
> https://github.com/postgrespro/pg_variables

Unfortunately not - it doesn't implement this functionality

Regards

Pavel

>
>
> -----
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-
> f1928748.html
>
>

Hi

I am sending new update. The code is less ugly, and the current
functionality is +/- final for first stage. It should be good enough for
playing and testing this concept.

What is supported:

1. scalar, composite and array variables
2. composite can be defined on place or some composite type can be used
3. variable, or any field of variable, can have defined default value
4. variable is database object - the access rights are required
5. the values are stored in binary form with defined typmod

An usage is very simple:

postgres=# create variable foo as numeric default 0;
CREATE VARIABLE
postgres=# select foo;
┌─────┐
│ foo │
╞═════╡
│ 0 │
└─────┘
(1 row)

postgres=# let foo = pi();
LET
postgres=# select foo;
┌──────────────────┐
│ foo │
╞══════════════════╡
│ 3.14159265358979 │
└──────────────────┘
(1 row)

postgres=# create variable boo as (x numeric default 0, y numeric default
0);
CREATE VARIABLE
postgres=# let boo.x = 100;
LET
postgres=# select boo;
┌─────────┐
│ boo │
╞═════════╡
│ (100,0) │
└─────────┘
(1 row)

postgres=# select boo.x;
┌─────┐
│ x │
╞═════╡
│ 100 │
└─────┘
(1 row)

Please try it.

Regards

Pavel

2018-03-20 18:38 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> I am sending new update. The code is less ugly, and the current
> functionality is +/- final for first stage. It should be good enough for
> playing and testing this concept.
>
> What is supported:
>
> 1. scalar, composite and array variables
> 2. composite can be defined on place or some composite type can be used
> 3. variable, or any field of variable, can have defined default value
> 4. variable is database object - the access rights are required
> 5. the values are stored in binary form with defined typmod
>
> An usage is very simple:
>
> postgres=# create variable foo as numeric default 0;
> CREATE VARIABLE
> postgres=# select foo;
> ┌─────┐
> │ foo │
> ╞═════╡
> │ 0 │
> └─────┘
> (1 row)
>
> postgres=# let foo = pi();
> LET
> postgres=# select foo;
> ┌──────────────────┐
> │ foo │
> ╞══════════════════╡
> │ 3.14159265358979 │
> └──────────────────┘
> (1 row)
>
> postgres=# create variable boo as (x numeric default 0, y numeric default
> 0);
> CREATE VARIABLE
> postgres=# let boo.x = 100;
> LET
> postgres=# select boo;
> ┌─────────┐
> │ boo │
> ╞═════════╡
> │ (100,0) │
> └─────────┘
> (1 row)
>
> postgres=# select boo.x;
> ┌─────┐
> │ x │
> ╞═════╡
> │ 100 │
> └─────┘
> (1 row)
>
> Please try it.
>

small fix - support for SQL functions

>
> Regards
>
> Pavel
>

2018-03-21 6:24 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2018-03-20 18:38 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>> Hi
>>
>> I am sending new update. The code is less ugly, and the current
>> functionality is +/- final for first stage. It should be good enough for
>> playing and testing this concept.
>>
>> What is supported:
>>
>> 1. scalar, composite and array variables
>> 2. composite can be defined on place or some composite type can be used
>> 3. variable, or any field of variable, can have defined default value
>> 4. variable is database object - the access rights are required
>> 5. the values are stored in binary form with defined typmod
>>
>> An usage is very simple:
>>
>> postgres=# create variable foo as numeric default 0;
>> CREATE VARIABLE
>> postgres=# select foo;
>> ┌─────┐
>> │ foo │
>> ╞═════╡
>> │ 0 │
>> └─────┘
>> (1 row)
>>
>> postgres=# let foo = pi();
>> LET
>> postgres=# select foo;
>> ┌──────────────────┐
>> │ foo │
>> ╞══════════════════╡
>> │ 3.14159265358979 │
>> └──────────────────┘
>> (1 row)
>>
>> postgres=# create variable boo as (x numeric default 0, y numeric default
>> 0);
>> CREATE VARIABLE
>> postgres=# let boo.x = 100;
>> LET
>> postgres=# select boo;
>> ┌─────────┐
>> │ boo │
>> ╞═════════╡
>> │ (100,0) │
>> └─────────┘
>> (1 row)
>>
>> postgres=# select boo.x;
>> ┌─────┐
>> │ x │
>> ╞═════╡
>> │ 100 │
>> └─────┘
>> (1 row)
>>
>> Please try it.
>>
>
> small fix - support for SQL functions
>
>

the patch is in commit fest list https://commitfest.postgresql.org/18/1608/

Regards

Pavel

>
>> Regards
>>
>> Pavel
>>
>
>

Hello Pavel,

On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> I hope so this proposal is good enough and simple.
>
> Comments, notes?

As I understood variables are stored in pg_class table. Did you consider
storing variables in a special catalog table? It can be named as
pg_variable.

pg_variable table requires more code of course, but it would fix the issues:
- pg_class has a lot attributes which are not related with variables,
and I think variables don't need many of them
- in a future variables might want to have some additional attributes
which are not needed for relations, you can easily add them to
pg_variable

What do you think?

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Hi

2018-04-17 16:14 GMT+02:00 Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>:

> Hello Pavel,
>
> On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> > I hope so this proposal is good enough and simple.
> >
> > Comments, notes?
>
> As I understood variables are stored in pg_class table. Did you consider
> storing variables in a special catalog table? It can be named as
> pg_variable.
>
> pg_variable table requires more code of course, but it would fix the
> issues:
> - pg_class has a lot attributes which are not related with variables,
> and I think variables don't need many of them
> - in a future variables might want to have some additional attributes
> which are not needed for relations, you can easily add them to
> pg_variable
>
> What do you think?
>

I though about it, and I am inclined to prefer pg_class instead separate
tables.

It true, so there are lot of "unused" attributes for this purpose, but
there is lot of shared attributes, and lot of shared code. Semantically, I
see variables in family of sequences, tables, indexes, views. Now, it
shares code, and I hope in next steps more code can be shared -
constraints, triggers.

There are two objective arguments for using pg_class:

1. unique name in schema - it reduces risk of collisions
2. sharing lot of code

So in this case I don't see well benefits of separate table.

Regards

Pavel

>
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>

On Tue, Apr 17, 2018 at 06:28:19PM +0200, Pavel Stehule wrote:
> I though about it, and I am inclined to prefer pg_class instead separate
> tables.
>
> It true, so there are lot of "unused" attributes for this purpose, but
> there is lot of shared attributes, and lot of shared code. Semantically, I
> see variables in family of sequences, tables, indexes, views. Now, it
> shares code, and I hope in next steps more code can be shared -
> constraints, triggers.
>
> There are two objective arguments for using pg_class:
>
> 1. unique name in schema - it reduces risk of collisions
> 2. sharing lot of code
>
> So in this case I don't see well benefits of separate table.

Understood. I haven't strong opinion here though. But I thought that
pg_class approach may limit extensibility of variables.

BTW:
- there is unitialized variable 'j' in pg_dump.c:15422
- in tab-complete.c:1268 initialization needs extra NULL before
&Query_for_list_of_variables

Also I think makeRangeVarForTargetOfSchemaVariable() has non friendly
argument names 'field1', 'field2', 'field2'.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Hi

I am sending rebased patch

2018-04-18 13:37 GMT+02:00 Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>:

> On Tue, Apr 17, 2018 at 06:28:19PM +0200, Pavel Stehule wrote:
> > I though about it, and I am inclined to prefer pg_class instead separate
> > tables.
> >
> > It true, so there are lot of "unused" attributes for this purpose, but
> > there is lot of shared attributes, and lot of shared code. Semantically,
> I
> > see variables in family of sequences, tables, indexes, views. Now, it
> > shares code, and I hope in next steps more code can be shared -
> > constraints, triggers.
> >
> > There are two objective arguments for using pg_class:
> >
> > 1. unique name in schema - it reduces risk of collisions
> > 2. sharing lot of code
> >
> > So in this case I don't see well benefits of separate table.
>
> Understood. I haven't strong opinion here though. But I thought that
> pg_class approach may limit extensibility of variables.
>

I didn't touch limit (I don't know if there will be some issue - still is
far to upstream). This is technology, but workable, demo. I though so some
users had a problem to imagine what is persistent variable in my view.

But almost all code and tests can be used for final version - only storage
implementation is nothing more than workaround.

>
> BTW:
> - there is unitialized variable 'j' in pg_dump.c:15422
> - in tab-complete.c:1268 initialization needs extra NULL before
> &Query_for_list_of_variables
>

I found it too today when I did rebase. But thank you for report.

>
> Also I think makeRangeVarForTargetOfSchemaVariable() has non friendly
> argument names 'field1', 'field2', 'field2'.
>

yes, I hadn't better names :(

In this routine I am doing diagnostic what semantic has sense for current
values. the field1, field2 can be schema.variable or variable.field. So
when I used semantic names: schema, varname, fieldname, then it was more
messy (for me).

Regards

Pavel

>
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>

On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> It true, so there are lot of "unused" attributes for this purpose, but there
> is lot of shared attributes, and lot of shared code. Semantically, I see
> variables in family of sequences, tables, indexes, views. Now, it shares
> code, and I hope in next steps more code can be shared - constraints,
> triggers.

I dunno, it seems awfully different to me. There's only one "column",
right? What code is really shared here? Are constraints and triggers
even desirable feature for variables? What would be the use case?

I think stuffing this into pg_class is pretty strange.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2018-04-20 17:32 GMT+02:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > It true, so there are lot of "unused" attributes for this purpose, but
> there
> > is lot of shared attributes, and lot of shared code. Semantically, I see
> > variables in family of sequences, tables, indexes, views. Now, it shares
> > code, and I hope in next steps more code can be shared - constraints,
> > triggers.
>
> I dunno, it seems awfully different to me. There's only one "column",
> right? What code is really shared here? Are constraints and triggers
> even desirable feature for variables? What would be the use case?
>

The schema variable can hold composite value. The patch allows to use any
composite type or adhoc composite values

DECLARE x AS compositetype;
DECLARE x AS (a int, b int, c int);

Constraints are clear, no.

Triggers are strange maybe, but why not - it can be used like enhanced
constraints, can be used for some value calculations, ..

> I think stuffing this into pg_class is pretty strange.
>

It will be if variable is just scalar value without any possibilities. But
then there is only low benefit

The access rights implementation is shared with other from pg_class too.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Hi

I am sending rebased patch to master

Regards

Pavel

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested

1) There are some errors applying the patch against the current master:

fabrizio(at)macanudo:/d/postgresql (master)
$ git apply /tmp/schema-variables-poc-180429-01-diff
/tmp/schema-variables-poc-180429-01-diff:2305: trailing whitespace.

/tmp/schema-variables-poc-180429-01-diff:2317: indent with spaces.
We can support UPDATE and SELECT commands on variables.
/tmp/schema-variables-poc-180429-01-diff:2319: indent with spaces.
possible syntaxes:
/tmp/schema-variables-poc-180429-01-diff:2321: indent with spaces.
-- there can be a analogy with record functions
/tmp/schema-variables-poc-180429-01-diff:2322: indent with spaces.
SELECT varname;
warning: squelched 14 whitespace errors
warning: 19 lines add whitespace errors.

2) There are some warnings when during build process

schemavar.c:383:18: warning: expression which evaluates to zero treated as a null pointer constant of type 'HeapTuple' (aka 'struct HeapTupleData *')
[-Wnon-literal-null-conversion]
HeapTuple tp = InvalidOid;
^~~~~~~~~~
../../../src/include/postgres_ext.h:36:21: note: expanded from macro 'InvalidOid'
#define InvalidOid ((Oid) 0)
^~~~~~~~~
1 warning generated.
tab-complete.c:1268:21: warning: initialization from incompatible pointer type [-Wincompatible-pointer-types]
{"VARIABLE", NULL, &Query_for_list_of_variables},
^
tab-complete.c:1268:21: note: (near initialization for ‘words_after_create[48].vquery’)
llvmjit_expr.c: In function ‘llvm_compile_expr’:
llvmjit_expr.c:253:3: warning: enumeration value ‘EEOP_PARAM_SCHEMA_VARIABLE’ not handled in switch [-Wswitch]
switch (opcode)
^

On 4/20/18 13:45, Pavel Stehule wrote:
> I dunno, it seems awfully different to me.  There's only one "column",
> right?  What code is really shared here?  Are constraints and triggers
> even desirable feature for variables?  What would be the use case?
>
>
> The schema variable can hold composite value. The patch allows to use
> any composite type or adhoc composite values
>
> DECLARE x AS compositetype;
> DECLARE x AS (a int, b int, c int);

I'm not sure that this anonymous composite type thing is such a good
idea. Such a variable will then be incompatible with anything else,
because it's of a different type.

In any case, I find that a weak argument for storing this in pg_class.
You could just as well create these pg_class entries implicitly and link
them from "pg_variable", same as composite types have a main entry in
pg_type and additional stuff in pg_class.

> I think stuffing this into pg_class is pretty strange.
>
> It will be if variable is just scalar value without any possibilities.
> But then there is only low benefit
>
> The access rights implementation is shared with other from pg_class too.

In DB2, the privileges for variables are named READ and WRITE. That
would make more sense to me than reusing the privilege names for tables.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi

2018-05-01 3:56 GMT+02:00 Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com
>:

> On 4/20/18 13:45, Pavel Stehule wrote:
> > I dunno, it seems awfully different to me. There's only one
> "column",
> > right? What code is really shared here? Are constraints and
> triggers
> > even desirable feature for variables? What would be the use case?
> >
> >
> > The schema variable can hold composite value. The patch allows to use
> > any composite type or adhoc composite values
> >
> > DECLARE x AS compositetype;
> > DECLARE x AS (a int, b int, c int);
>
> I'm not sure that this anonymous composite type thing is such a good
> idea. Such a variable will then be incompatible with anything else,
> because it's of a different type.
>

Using anonymous composite type variable is just shortcut for situations
when mentioned feature is not a problem. These variables are global, so
there can be only one variable of some specific composite type, and
incompatibility with others is not a issue.

This feature can be interesting for short live temp variables - these
variables can be used for parametrization of anonymous block.

But this feature is not significant, and can be removed from patch.

> In any case, I find that a weak argument for storing this in pg_class.
> You could just as well create these pg_class entries implicitly and link
> them from "pg_variable", same as composite types have a main entry in
> pg_type and additional stuff in pg_class.
>
> > I think stuffing this into pg_class is pretty strange.
> >
> > It will be if variable is just scalar value without any possibilities.
> > But then there is only low benefit
> >
> > The access rights implementation is shared with other from pg_class too.
>
> In DB2, the privileges for variables are named READ and WRITE. That
> would make more sense to me than reusing the privilege names for tables.
>
>
good idea

Regards

Pavel

> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Hi,

I'm reviewing the patch as it was flagged in the current commit fest.
Here are my feedback:

 - The patch need to be rebased due to changes in file
src/sgml/catalogs.sgml

 - Some compilation warning must be fixed:

analyze.c: In function ‘transformLetStmt’:
analyze.c:1568:17: warning: variable ‘rte’ set but not used
[-Wunused-but-set-variable]
  RangeTblEntry *rte;
                 ^~~
tab-complete.c:1268:21: warning: initialization from incompatible
pointer type [-Wincompatible-pointer-types]
  {"VARIABLE", NULL, &Query_for_list_of_variables},

In the last warning a NULL is missing, should be written:
{"VARIABLE", NULL, NULL, &Query_for_list_of_variables},

 - How about Peter's suggestion?:
    "In DB2, the privileges for variables are named READ and WRITE.
That would make more sense to me than reusing the privilege names for
tables.
    The patch use SELECT and UPDATE which make sense too for SELECT but
less for UPDATE.

 - The implementation of "ALTER VARIABLE varname SET SCHEMA
schema_name;" is missing

 - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and
missing in regression test

 - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and
missing in regression test

More generally I think that some comments must be rewritten, especially
those talking about a PoC. In documentation there is HTML comments that
can be removed.

Comment at end of file src/backend/commands/schemavar.c generate some
"indent with spaces" errors with git apply but perhaps the comment can
be entirely removed or undocumented details moved to the right place.

Otherwise all regression tests passed without issue and especially your
new regression tests about schema variables.

I have a patch rebased, let me known if you want me to post the new diff.

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

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:

> Hi,
>
> I'm reviewing the patch as it was flagged in the current commit fest. Here
> are my feedback:
>
> - The patch need to be rebased due to changes in file
> src/sgml/catalogs.sgml
>
> - Some compilation warning must be fixed:
>
> analyze.c: In function ‘transformLetStmt’:
> analyze.c:1568:17: warning: variable ‘rte’ set but not used
> [-Wunused-but-set-variable]
> RangeTblEntry *rte;
> ^~~
> tab-complete.c:1268:21: warning: initialization from incompatible pointer
> type [-Wincompatible-pointer-types]
> {"VARIABLE", NULL, &Query_for_list_of_variables},
>
> In the last warning a NULL is missing, should be written: {"VARIABLE",
> NULL, NULL, &Query_for_list_of_variables},
>
>
> - How about Peter's suggestion?:
> "In DB2, the privileges for variables are named READ and WRITE. That
> would make more sense to me than reusing the privilege names for tables.
>
The patch use SELECT and UPDATE which make sense too for SELECT but
> less for UPDATE.
>
> - The implementation of "ALTER VARIABLE varname SET SCHEMA schema_name;"
> is missing
>
> - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and missing
> in regression test
>
> - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and missing
> in regression test
>
> More generally I think that some comments must be rewritten, especially
> those talking about a PoC. In documentation there is HTML comments that can
> be removed.
>
> Comment at end of file src/backend/commands/schemavar.c generate some
> "indent with spaces" errors with git apply but perhaps the comment can be
> entirely removed or undocumented details moved to the right place.
>
> Otherwise all regression tests passed without issue and especially your
> new regression tests about schema variables.
>
> I have a patch rebased, let me known if you want me to post the new diff.
>

I plan significant refactoring of this patch for next commitfest. There was
anotherstrong Peter's and Robert comments

1. The schema variables should to have own system table
2. The composite schema variables should to use explicitly defined
composite type
3. The memory management is not nice - transactional drop table with
content is implemented ugly.

I hope, so I can start on these issues next month.

Thank you for review - I'll recheck ALTER commands.

Regards

Pavel

>
> --
> Gilles Darold
> Consultant PostgreSQLhttp://dalibo.com - http://dalibo.org
>
>

Le 27/06/2018 à 13:22, Pavel Stehule a écrit :
> Hi
>
> 2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com
> <mailto:gilles(dot)darold(at)dalibo(dot)com>>:
>
> Hi,
>
> I'm reviewing the patch as it was flagged in the current commit
> fest. Here are my feedback:
>
>  - The patch need to be rebased due to changes in file
> src/sgml/catalogs.sgml
>
>  - Some compilation warning must be fixed:
>
> analyze.c: In function ‘transformLetStmt’:
> analyze.c:1568:17: warning: variable ‘rte’ set but not used
> [-Wunused-but-set-variable]
>   RangeTblEntry *rte;
>                  ^~~
> tab-complete.c:1268:21: warning: initialization from
> incompatible pointer type [-Wincompatible-pointer-types]
>   {"VARIABLE", NULL, &Query_for_list_of_variables},
>
> In the last warning a NULL is missing, should be written:
> {"VARIABLE", NULL, NULL, &Query_for_list_of_variables},
>
>
>  - How about Peter's suggestion?:
>     "In DB2, the privileges for variables are named READ and
> WRITE. That would make more sense to me than reusing the privilege
> names for tables.
>
>     The patch use SELECT and UPDATE which make sense too for
> SELECT but less for UPDATE.
>
>  - The implementation of "ALTER VARIABLE varname SET SCHEMA
> schema_name;" is missing
>
>  - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and
> missing in regression test
>
>  - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and
> missing in regression test
>
> More generally I think that some comments must be rewritten,
> especially those talking about a PoC. In documentation there is
> HTML comments that can be removed.
>
> Comment at end of file src/backend/commands/schemavar.c generate
> some "indent with spaces" errors with git apply but perhaps the
> comment can be entirely removed or undocumented details moved to
> the right place.
>
> Otherwise all regression tests passed without issue and especially
> your new regression tests about schema variables.
>
> I have a patch rebased, let me known if you want me to post the
> new diff.
>
>
> I plan significant refactoring of this patch for next commitfest.
> There was anotherstrong Peter's and Robert comments
>
> 1. The schema variables should to have own system table
> 2. The composite schema variables should to use explicitly defined
> composite type
> 3. The memory management is not nice - transactional drop table with
> content is implemented ugly.
>
> I hope, so I can start on these issues next month.
>
> Thank you for review - I'll recheck ALTER commands.
>
>
> Otherwise all regression tests passed without issue and especially
> your new regression tests about schema variables.
>
> I have a patch rebased, let me known if you want me to post the
> new diff.
>
>
> I plan significant refactoring of this patch for next commitfest.
> There was anotherstrong Peter's and Robert c
> Regards

Ok Pavel, I've changed the status to "Waiting for authors" so that no
one will make an other review until you send a new patch.

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

2018-06-27 19:15 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:

> Le 27/06/2018 à 13:22, Pavel Stehule a écrit :
>
> Hi
>
> 2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:
>
>> Hi,
>>
>> I'm reviewing the patch as it was flagged in the current commit fest.
>> Here are my feedback:
>>
>> - The patch need to be rebased due to changes in file
>> src/sgml/catalogs.sgml
>>
>> - Some compilation warning must be fixed:
>>
>> analyze.c: In function ‘transformLetStmt’:
>> analyze.c:1568:17: warning: variable ‘rte’ set but not used
>> [-Wunused-but-set-variable]
>> RangeTblEntry *rte;
>> ^~~
>> tab-complete.c:1268:21: warning: initialization from incompatible pointer
>> type [-Wincompatible-pointer-types]
>> {"VARIABLE", NULL, &Query_for_list_of_variables},
>>
>> In the last warning a NULL is missing, should be written: {"VARIABLE",
>> NULL, NULL, &Query_for_list_of_variables},
>>
>>
>> - How about Peter's suggestion?:
>> "In DB2, the privileges for variables are named READ and WRITE. That
>> would make more sense to me than reusing the privilege names for tables.
>>
> The patch use SELECT and UPDATE which make sense too for SELECT but
>> less for UPDATE.
>>
>> - The implementation of "ALTER VARIABLE varname SET SCHEMA schema_name;"
>> is missing
>>
>> - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and missing
>> in regression test
>>
>> - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and missing
>> in regression test
>>
>> More generally I think that some comments must be rewritten, especially
>> those talking about a PoC. In documentation there is HTML comments that can
>> be removed.
>>
>> Comment at end of file src/backend/commands/schemavar.c generate some
>> "indent with spaces" errors with git apply but perhaps the comment can be
>> entirely removed or undocumented details moved to the right place.
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert comments
>
> 1. The schema variables should to have own system table
> 2. The composite schema variables should to use explicitly defined
> composite type
> 3. The memory management is not nice - transactional drop table with
> content is implemented ugly.
>
> I hope, so I can start on these issues next month.
>
> Thank you for review - I'll recheck ALTER commands.
>
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert c
> Regards
>
>
> Ok Pavel, I've changed the status to "Waiting for authors" so that no one
> will make an other review until you send a new patch.
>
sure

Thank you

Pavel

>
> --
> Gilles Darold
> Consultant PostgreSQLhttp://dalibo.com - http://dalibo.org
>
>

2018-06-27 19:15 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:

> Le 27/06/2018 à 13:22, Pavel Stehule a écrit :
>
> Hi
>
> 2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles(dot)darold(at)dalibo(dot)com>:
>
>> Hi,
>>
>> I'm reviewing the patch as it was flagged in the current commit fest.
>> Here are my feedback:
>>
>> - The patch need to be rebased due to changes in file
>> src/sgml/catalogs.sgml
>>
>> - Some compilation warning must be fixed:
>>
>> analyze.c: In function ‘transformLetStmt’:
>> analyze.c:1568:17: warning: variable ‘rte’ set but not used
>> [-Wunused-but-set-variable]
>> RangeTblEntry *rte;
>> ^~~
>> tab-complete.c:1268:21: warning: initialization from incompatible pointer
>> type [-Wincompatible-pointer-types]
>> {"VARIABLE", NULL, &Query_for_list_of_variables},
>>
>> In the last warning a NULL is missing, should be written: {"VARIABLE",
>> NULL, NULL, &Query_for_list_of_variables},
>>
>>
>> - How about Peter's suggestion?:
>> "In DB2, the privileges for variables are named READ and WRITE. That
>> would make more sense to me than reusing the privilege names for tables.
>>
> The patch use SELECT and UPDATE which make sense too for SELECT but
>> less for UPDATE.
>>
>> - The implementation of "ALTER VARIABLE varname SET SCHEMA schema_name;"
>> is missing
>>
>> - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and missing
>> in regression test
>>
>> - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and missing
>> in regression test
>>
>> More generally I think that some comments must be rewritten, especially
>> those talking about a PoC. In documentation there is HTML comments that can
>> be removed.
>>
>> Comment at end of file src/backend/commands/schemavar.c generate some
>> "indent with spaces" errors with git apply but perhaps the comment can be
>> entirely removed or undocumented details moved to the right place.
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert comments
>
> 1. The schema variables should to have own system table
> 2. The composite schema variables should to use explicitly defined
> composite type
> 3. The memory management is not nice - transactional drop table with
> content is implemented ugly.
>
> I hope, so I can start on these issues next month.
>
> Thank you for review - I'll recheck ALTER commands.
>
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert c
> Regards
>
>
> Ok Pavel, I've changed the status to "Waiting for authors" so that no one
> will make an other review until you send a new patch.
>
I am sending a new update of this feature. The functionality is +/- same
like previous patch, but a implementation is based on own catalog table.

I removed functions for manipulation with schema variables. These functions
can be added later simply. Now If we hold these functions, then we should
to solve often collision inside pg_proc.

Changes:

* own catalog - pg_variable
* the rights are renamed - READ|WRITE
* the code is cleaner

Regards

Pavel

>
> --
> Gilles Darold
> Consultant PostgreSQLhttp://dalibo.com - http://dalibo.org
>
>

removed forgotten file

Regards

Pavel

Hi

I am sending updated patch. It should to solve almost all Giles's and
Peter's objections.

I am not happy so executor access values of variables directly. It is most
simple implementation - and I hope so it is good enough, but now the access
to variables is too volatile. But it is works good enough for usability
testing.

I am thinking about some cache of used variables in ExprContext, so the
variable in one ExprContext will look like stable - more like PLpgSQL
variables.

Regards

Pavel

2018-08-11 7:39 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> I am sending updated patch. It should to solve almost all Giles's and
> Peter's objections.
>
> I am not happy so executor access values of variables directly. It is most
> simple implementation - and I hope so it is good enough, but now the access
> to variables is too volatile. But it is works good enough for usability
> testing.
>
> I am thinking about some cache of used variables in ExprContext, so the
> variable in one ExprContext will look like stable - more like PLpgSQL
> variables.
>

I wrote EState based schema variable values cache, so now the variables in
queries are stable (like PARAM_EXTERN) and can be used for optimization.

Regards

Pavel

>
> Regards
>
> Pavel
>

Hi

2018-08-11 20:46 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2018-08-11 7:39 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>> Hi
>>
>> I am sending updated patch. It should to solve almost all Giles's and
>> Peter's objections.
>>
>> I am not happy so executor access values of variables directly. It is
>> most simple implementation - and I hope so it is good enough, but now the
>> access to variables is too volatile. But it is works good enough for
>> usability testing.
>>
>> I am thinking about some cache of used variables in ExprContext, so the
>> variable in one ExprContext will look like stable - more like PLpgSQL
>> variables.
>>
>
> I wrote EState based schema variable values cache, so now the variables in
> queries are stable (like PARAM_EXTERN) and can be used for optimization.
>

new update - after cleaning

Regards

Pavel

> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> Pavel
>>
>
>

Hi

I wrote missing collation support

Regards

Pavel

Hello Pavel,

AFAICR, I had an objection on such new objects when you first proposed
something similar in October 2016.

Namely, if session variables are not transactional, they cannot be used to
implement security related auditing features which were advertised as the
motivating use case: an the audit check may fail on a commit because of a
differed constraint, but the variable would keep its "okay" value unduly,
which would create a latent security issue, the audit check having failed
but the variable saying the opposite.

So my point was that they should be transactional by default, although I
would be ok with an option for having a voluntary non transactional
version.

Is this issue addressed somehow with this version?

--
Fabien.

Hi Fabien

Dne út 21. 8. 2018 19:56 uživatel Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
napsal:

>
> Hello Pavel,
>
> AFAICR, I had an objection on such new objects when you first proposed
> something similar in October 2016.
>
> Namely, if session variables are not transactional, they cannot be used to
> implement security related auditing features which were advertised as the
> motivating use case: an the audit check may fail on a commit because of a
> differed constraint, but the variable would keep its "okay" value unduly,
> which would create a latent security issue, the audit check having failed
> but the variable saying the opposite.
>
> So my point was that they should be transactional by default, although I
> would be ok with an option for having a voluntary non transactional
> version.
>
> Is this issue addressed somehow with this ?

1. I respect your opinion, but I dont agree with it. Oracle, db2 has
similar or very similar feature non transactional, and I didnt find any
requests to change it.

2. the prototype implementation was based on relclass items, and some
transactional behave was possible. Peter E. had objections to this design
and proposed own catalog table. I did it. Now, the transactional behave is
harder to implement, although it is not impossible. This patch is not small
now, so I didnt implement it. I have a strong opinion so default behave
have to be non transactional.

Transactional variables significantly increases complexity of this patch,
now is simple, because we can reset variable on drop variable command.
Maybe I miss some simply implementation, but I spent on it more than few
days. Still, any cooperation are welcome.

Regards

Pavel

> --
> Fabien.
>

Hello Pavel,

>> AFAICR, I had an objection on such new objects when you first proposed
>> something similar in October 2016.
>>
>> Namely, if session variables are not transactional, they cannot be used to
>> implement security related auditing features which were advertised as the
>> motivating use case: an the audit check may fail on a commit because of a
>> differed constraint, but the variable would keep its "okay" value unduly,
>> which would create a latent security issue, the audit check having failed
>> but the variable saying the opposite.
>>
>> So my point was that they should be transactional by default, although I
>> would be ok with an option for having a voluntary non transactional
>> version.
>>
>> Is this issue addressed somehow with this ?
>
>
> 1. I respect your opinion, but I dont agree with it. Oracle, db2 has
> similar or very similar feature non transactional, and I didnt find any
> requests to change it.

The argument of authority that "X does it like that" is not a valid answer
to my technical objection about security implications of this feature.

> 2. the prototype implementation was based on relclass items, and some
> transactional behave was possible. Peter E. had objections to this design
> and proposed own catalog table. I did it. Now, the transactional behave is
> harder to implement, although it is not impossible. This patch is not small
> now, so I didnt implement it.

"It is harder to implement" does not look like a valid answer either.

> I have a strong opinion so default behave have to be non transactional.

The fact that you have a "strong opinion" does not really answer my
objection. Moreover, I said that I would be ok with a non transactional
option, provided that a default transactional is available.

> Transactional variables significantly increases complexity of this patch,
> now is simple, because we can reset variable on drop variable command.
> Maybe I miss some simply implementation, but I spent on it more than few
> days. Still, any cooperation are welcome.

"It is simpler to implement this way" is not an answer either, especially
as you said that it could have been on point 2.

As I do not see any clear answer to my objection about security
implications, I understand that it is not addressed by this patch.

At the bare minimum, if this feature ever made it as is, I think that a
clear caveat must be included in the documentation about not using it for
any security-related purpose.

Also, I'm not really sure how useful such a non-transactional object can
be for other purposes: the user should take into account that the
transaction may fail and the value of the session variable be inconsistent
as a result. Sometimes it may not matter, but if it matters there is no
easy way around the fact.

--
Fabien.

2018-08-22 9:00 GMT+02:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Pavel,
>
> AFAICR, I had an objection on such new objects when you first proposed
>>> something similar in October 2016.
>>>
>>> Namely, if session variables are not transactional, they cannot be used
>>> to
>>> implement security related auditing features which were advertised as the
>>> motivating use case: an the audit check may fail on a commit because of a
>>> differed constraint, but the variable would keep its "okay" value unduly,
>>> which would create a latent security issue, the audit check having failed
>>> but the variable saying the opposite.
>>>
>>> So my point was that they should be transactional by default, although I
>>> would be ok with an option for having a voluntary non transactional
>>> version.
>>>
>>> Is this issue addressed somehow with this ?
>>>
>>
>>
>> 1. I respect your opinion, but I dont agree with it. Oracle, db2 has
>> similar or very similar feature non transactional, and I didnt find any
>> requests to change it.
>>
>
> The argument of authority that "X does it like that" is not a valid answer
> to my technical objection about security implications of this feature.
>
> 2. the prototype implementation was based on relclass items, and some
>> transactional behave was possible. Peter E. had objections to this design
>> and proposed own catalog table. I did it. Now, the transactional behave is
>> harder to implement, although it is not impossible. This patch is not
>> small
>> now, so I didnt implement it.
>>
>
> "It is harder to implement" does not look like a valid answer either.
>
> I have a strong opinion so default behave have to be non transactional.
>>
>
> The fact that you have a "strong opinion" does not really answer my
> objection. Moreover, I said that I would be ok with a non transactional
> option, provided that a default transactional is available.
>
> Transactional variables significantly increases complexity of this patch,
>> now is simple, because we can reset variable on drop variable command.
>> Maybe I miss some simply implementation, but I spent on it more than few
>> days. Still, any cooperation are welcome.
>>
>
> "It is simpler to implement this way" is not an answer either, especially
> as you said that it could have been on point 2.
>
>
> As I do not see any clear answer to my objection about security
> implications, I understand that it is not addressed by this patch.
>
>
> At the bare minimum, if this feature ever made it as is, I think that a
> clear caveat must be included in the documentation about not using it for
> any security-related purpose.
>
> Also, I'm not really sure how useful such a non-transactional object can
> be for other purposes: the user should take into account that the
> transaction may fail and the value of the session variable be inconsistent
> as a result. Sometimes it may not matter, but if it matters there is no
> easy way around the fact.
>

I agree, so it should be well documented to be clear, what is possible,
what not, and to be correct expectations.

This feature has two (three) purposes

1. global variables for PL language
2. holding some session based informations, that can be used in security
definer functions.
3. Because it is not transactional, then it allows write operation on read
only hot stand by instances.

It is not transactional safe, but it is secure in sense a possibility to
set a access rights. I understand, so some patterns are not possible, but
when you need hold some keys per session, then this simply solution can be
good enough. The variables are clean after session end.

I think it is possible for some more complex patterns, but then developer
should be smarter, and should to enocode state result to content of
variable. There is strong benefit - read write access to variables is very
cheap and fast.

I invite any patch to doc (or everywhere) with explanation and about
possible risks.

Regards

Pavel

> --
> Fabien.
>

Hello Pavel,

> 2. holding some session based informations, that can be used in security
> definer functions.

Hmmm, I see our disagreement. My point is that this feature is *NOT* fit
for security-related uses because if the transaction fails the variable
would keep the value it had if the transaction had not failed...

> 3. Because it is not transactional, then it allows write operation on read

> It is not transactional safe, but it is secure in sense a possibility to
> set a access rights.

This is a misleading play on words. It is secure wrt to access right, but
unsecure wrt security purposes which is the only point for having such a
feature in the first place.

> I understand, so some patterns are not possible, but when you need hold
> some keys per session, then this simply solution can be good enough.

Security vs "good enough in some cases" looks bad to me.

> I think it is possible for some more complex patterns,

I'm not sure of any pattern which would be correct wrt security if it
depends on the success of a transaction.

> but then developer should be smarter, and should to enocode state result
> to content of variable.

I do not see how the developer can be smarter if they need a transactional
for security but they do not have it.

> There is strong benefit - read write access to variables is very cheap
> and fast.

I'd say that PostgreSQL is about "ACID & security" first, not "cheap &
fast" first.

> I invite any patch to doc (or everywhere) with explanation and about
> possible risks.

Hmmm... You are the one proposing the feature...

Here is something, thanks for adjusting it to the syntax you are proposing
and inserting it where appropriate. Possibly in the corresponding CREATE
doc?

"""
<caution>
<par>
Beware that session variables are not transactional.
This is a concern in a security context where the variable must be set to
some trusted value depending on the success of the transaction:
if the transaction fails, the variable keeps its trusted value unduly.
</par>

<par>
For instance, the following pattern does NOT work:

<programlisting>
CREATE USER auditer;
SET ROLE auditer;
CREATE SESSION VARIABLE is_audited BOOLEAN DEFAULT FALSE ...;
-- ensure that only "auditer" can write "is_audited":
REVOKE ... ON SESSION VARIABLE is_audited FROM ...;

-- create an audit function
CREATE FUNCTION audit_session(...) SECURITY DEFINER AS $$
-- record the session and checks in some place...
-- then tell it was done:
LET is_audited = TRUE;
$$;

-- the intention is that other security definier functions can check that
-- the session is audited by checking on "is_audited", eg:
CREATE FUNCTION only_for_audited(...) SECURITY DEFINER AS $$
IF NOT is_audited THEN RAISE "security error";
-- do protected stuff here.
$$;
</programlisting>

The above pattern can be attacked with the following approach:
<programlisting>
BEGIN;
SELECT audit_session(...);
-- success, "is_audited" is set...
ROLLBACK;
-- the audit login has been reverted, but "is_audited" retains its value.

-- any subsequent operation believes wrongly that the session is audited,
-- but its logging has really been removed by the ROLLBACK.

-- ok but should not:
SELECT only_for_audited(...);
</programlisting>
</par>
</caution>
"""

For the record, I'm "-1" on this feature as proposed, for what it's worth,
because of the misleading security implications. This feature would just
help people have their security wrong.

--
Fabien.

2018-08-23 10:17 GMT+02:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Pavel,
>
> 2. holding some session based informations, that can be used in security
>> definer functions.
>>
>
> Hmmm, I see our disagreement. My point is that this feature is *NOT* fit
> for security-related uses because if the transaction fails the variable
> would keep the value it had if the transaction had not failed...
>
> 3. Because it is not transactional, then it allows write operation on read
>>
>
> It is not transactional safe, but it is secure in sense a possibility to
>> set a access rights.
>>
>
> This is a misleading play on words. It is secure wrt to access right, but
> unsecure wrt security purposes which is the only point for having such a
> feature in the first place.
>
> I understand, so some patterns are not possible, but when you need hold
>> some keys per session, then this simply solution can be good enough.
>>
>
> Security vs "good enough in some cases" looks bad to me.
>

We don't find a agreement, because you are concentrated on transation, me
on session. And we have different expectations.

> I think it is possible for some more complex patterns,
>>
>
> I'm not sure of any pattern which would be correct wrt security if it
> depends on the success of a transaction.
>
> but then developer should be smarter, and should to enocode state result
>> to content of variable.
>>
>
> I do not see how the developer can be smarter if they need a transactional
> for security but they do not have it.
>
> There is strong benefit - read write access to variables is very cheap and
>> fast.
>>
>
> I'd say that PostgreSQL is about "ACID & security" first, not "cheap &
> fast" first.
>
> I invite any patch to doc (or everywhere) with explanation and about
>> possible risks.
>>
>
> Hmmm... You are the one proposing the feature...
>
> Here is something, thanks for adjusting it to the syntax you are proposing
> and inserting it where appropriate. Possibly in the corresponding CREATE
> doc?
>
> """
> <caution>
> <par>
> Beware that session variables are not transactional.
> This is a concern in a security context where the variable must be set to
> some trusted value depending on the success of the transaction:
> if the transaction fails, the variable keeps its trusted value unduly.
> </par>
>
> <par>
> For instance, the following pattern does NOT work:
>
> <programlisting>
> CREATE USER auditer;
> SET ROLE auditer;
> CREATE SESSION VARIABLE is_audited BOOLEAN DEFAULT FALSE ...;
> -- ensure that only "auditer" can write "is_audited":
> REVOKE ... ON SESSION VARIABLE is_audited FROM ...;
>
> -- create an audit function
> CREATE FUNCTION audit_session(...) SECURITY DEFINER AS $$
> -- record the session and checks in some place...
> -- then tell it was done:
> LET is_audited = TRUE;
> $$;
>
> -- the intention is that other security definier functions can check that
> -- the session is audited by checking on "is_audited", eg:
> CREATE FUNCTION only_for_audited(...) SECURITY DEFINER AS $$
> IF NOT is_audited THEN RAISE "security error";
> -- do protected stuff here.
> $$;
> </programlisting>
>
> The above pattern can be attacked with the following approach:
> <programlisting>
> BEGIN;
> SELECT audit_session(...);
> -- success, "is_audited" is set...
> ROLLBACK;
> -- the audit login has been reverted, but "is_audited" retains its value.
>
> -- any subsequent operation believes wrongly that the session is audited,
> -- but its logging has really been removed by the ROLLBACK.
>
> -- ok but should not:
> SELECT only_for_audited(...);
> </programlisting>
> </par>
> </caution>
> """
>
>
It is good example of not supported pattern. It is not designed for this.
I'll merge this doc.

Note: I am not sure, if I have all relations to described issue, but if I
understand well, then solution can be reset on transaction end, maybe reset
on rollback. This is solvable, I'll look how it is complex.

>
> For the record, I'm "-1" on this feature as proposed, for what it's worth,
> because of the misleading security implications. This feature would just
> help people have their security wrong.
>

I respect your opinion - and I hope so integration of your proposed doc is
good warning for users that would to use not transactional variable like
transactional source.

Regards

Pavel

>
> --
> Fabien.
>
>

>> Security vs "good enough in some cases" looks bad to me.
>
> We don't find a agreement, because you are concentrated on transation,
> me on session. And we have different expectations.

I do not understand your point, as usual. I raise a factual issue about
security, and you do not answer how this can be solved with your proposal,
but appeal to argument of authority and declare your "strong opinion".

I do not see any intrinsic opposition between having session objects and
transactions. Nothing prevents a session object to be transactional beyond
your willingness that it should not be.

Now, I do expect all PostgreSQL features to be security-wise, whatever
their scope.

I do not think that security should be traded for "cheap & fast", esp as
the sole use case for a feature is a security pattern that cannot be
implemented securely with it. This appears to me as a huge contradiction,
hence my opposition against this feature as proposed.

The good news is that I'm a nobody: if a committer is happy with your
patch, it will get committed, you do not need my approval.

--
Fabien.

On 23.08.2018 12:46, Fabien COELHO wrote:
> I do not understand your point, as usual. I raise a factual issue
> about security, and you do not answer how this can be solved with your
> proposal, but appeal to argument of authority and declare your "strong
> opinion".
>
> I do not see any intrinsic opposition between having session objects
> and transactions. Nothing prevents a session object to be
> transactional beyond your willingness that it should not be.
>
> Now, I do expect all PostgreSQL features to be security-wise, whatever
> their scope.
>
> I do not think that security should be traded for "cheap & fast", esp
> as the sole use case for a feature is a security pattern that cannot
> be implemented securely with it. This appears to me as a huge
> contradiction, hence my opposition against this feature as proposed.

I can't to agree with your position.

Consider this example.
I want to record some inappropriate user actions to audit table and
rollback transaction.
But aborting transaction will also abort record to audit table.
So, do not use tables, becouse they have security implications.

This is very similar to your approach.

Schema variables is a very needed and important feature, but for others
purposes.

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Hello Pavel L.

>> I do not understand your point, as usual. I raise a factual issue about
>> security, and you do not answer how this can be solved with your proposal,
>> but appeal to argument of authority and declare your "strong opinion".
>>
>> I do not see any intrinsic opposition between having session objects and
>> transactions. Nothing prevents a session object to be transactional beyond
>> your willingness that it should not be.
>>
>> Now, I do expect all PostgreSQL features to be security-wise, whatever
>> their scope.
>>
>> I do not think that security should be traded for "cheap & fast", esp as
>> the sole use case for a feature is a security pattern that cannot be
>> implemented securely with it. This appears to me as a huge contradiction,
>> hence my opposition against this feature as proposed.
>
> I can't to agree with your position.
>
> Consider this example. I want to record some inappropriate user actions
> to audit table and rollback transaction. But aborting transaction will
> also abort record to audit table. So, do not use tables, becouse they
> have security implications.

Indeed, you cannot record a transaction failure from a transaction.

> This is very similar to your approach.

I understand that your point is that some use case could require a non
transactional session variable. I'm not sure of how the use case would go
on though, because once the "attacker" disconnects, the session variable
disappears, so it does not record that there was a problem.

Anyway, I'm not against having session variables per se. I'm argumenting
that there is a good case to have them transactional by default, and
possibly an option to have them non transactional if this is really needed
by some use case to provide.

The only use case put forward by Pavel S. is the security audit one
where a session variable stores that audit checks have been performed,
which AFAICS cannot be implemented securely with the proposed non
transactional session variables.

--
Fabien.

AFAICS this patch does nothing to consider parallel safety -- that is,
as things stand, a variable is allowed in a query that may be
parallelised, but its value is not copied to workers, leading to
incorrect results. For example:

create table foo(a int);
insert into foo select * from generate_series(1,1000000);
create variable zero int;
let zero = 0;

explain (costs off) select count(*) from foo where a%10 = zero;

QUERY PLAN
-----------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on foo
Filter: ((a % 10) = zero)
(6 rows)

select count(*) from foo where a%10 = zero;

count
-------
38037 -- Different random result each time, should be 100,000
(1 row)

Thoughts?

Regards,
Dean

Hi

2018-09-04 9:21 GMT+02:00 Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>:

> AFAICS this patch does nothing to consider parallel safety -- that is,
> as things stand, a variable is allowed in a query that may be
> parallelised, but its value is not copied to workers, leading to
> incorrect results. For example:
>
> create table foo(a int);
> insert into foo select * from generate_series(1,1000000);
> create variable zero int;
> let zero = 0;
>
> explain (costs off) select count(*) from foo where a%10 = zero;
>
> QUERY PLAN
> -----------------------------------------------
> Finalize Aggregate
> -> Gather
> Workers Planned: 2
> -> Partial Aggregate
> -> Parallel Seq Scan on foo
> Filter: ((a % 10) = zero)
> (6 rows)
>
> select count(*) from foo where a%10 = zero;
>
> count
> -------
> 38037 -- Different random result each time, should be 100,000
> (1 row)
>
> Thoughts?
>

The query use copy of values of variables now - but unfortunately, these
values are not passed to workers. Should be fixed.

Thank you for test case.

Pavel

> Regards,
> Dean
>

Hi

here is updated patch - I wrote some transactional support

I am not sure how these new features are understandable and if these
features does it better or not.

There are possibility to reset to default value when

a) any transaction is finished - the scope of value is limited by
transaction

CREATE VARIABLE foo int ON TRANSACTION END RESET;

b) when transaction finished by rollback

CREATE VARIABLE foo int ON ROLLBACK RESET

Now, when I am thinking about it, the @b is simple, but not too practical -
when some fails, then we lost a value (any transaction inside session can
fails). The @a has sense - the behave is global value (what is not possible
in Postgres now), but this value is destroyed by any unhandled exceptions,
and it cleaned on transaction end. The @b is just for information and for
discussion, but I'll remove it - because it is obscure.

The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
little bit unclean, because it has semantic "on transaction end", but if I
didn't implement @b, then ON COMMIT syntax can be used.

Regards

Pavel

Hello Pavel,

> here is updated patch - I wrote some transactional support
>
> I am not sure how these new features are understandable and if these
> features does it better or not.

> There are possibility to reset to default value when
>
> a) any transaction is finished - the scope of value is limited by
> transaction
>
> CREATE VARIABLE foo int ON TRANSACTION END RESET;

With this option I understand that it is a "within a transactionnal"
variable, i.e. when the transaction ends, whether commit or rollback, the
variable is reset to a default variable. It is not really a "session"
variable anymore, each transaction has its own value.

-- begin session
-- foo has default value, eg NULL
BEGIN;
LET foo = 1;
COMMIT/ROLLBACK;
-- foo has default value again, NULL

> b) when transaction finished by rollback
>
> CREATE VARIABLE foo int ON ROLLBACK RESET

That is a little bit safer and you are back to a SESSION-scope variable,
which is reset to the default value if the (any) transaction fails?

-- begin session
-- foo has default value, eg NULL
BEGIN;
LET foo = 1;
COMMIT;
-- foo has value 1
BEGIN;
-- foo has value 1...
ROLLBACK;
-- foo has value NULL

c) A more logical (from a transactional point of view - but not necessary
simple to implement, I do not know) feature/variant would be to reset the
value to the one it had at the beginning of the transaction, which is not
necessarily the default.

-- begin session
-- foo has default value, eg NULL
BEGIN;
LET foo = 1;
COMMIT;
-- foo has value 1
BEGIN;
LET foo = 2; (*)
-- foo has value 2
ROLLBACK;
-- foo has value 1 back, change (*) has been reverted

> Now, when I am thinking about it, the @b is simple, but not too practical -
> when some fails, then we lost a value (any transaction inside session can
> fails).

Indeed.

> The @a has sense - the behave is global value (what is not possible
> in Postgres now), but this value is destroyed by any unhandled exceptions,
> and it cleaned on transaction end. The @b is just for information and for
> discussion, but I'll remove it - because it is obscure.

Indeed.

> The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
> little bit unclean, because it has semantic "on transaction end", but if I
> didn't implement @b, then ON COMMIT syntax can be used.

I was more arguing on the third (c) option, i.e. on rollback the value is
reverted to its value at the beginning of the rollbacked transaction.

At the minimum, ISTM that option (b) is enough to implement the audit
pattern, but it would mean that any session which has a rollback, for any
reason (deadlock, serialization...), would have to be reinitialized, which
would be a drawback.

The to options could be non-transactional session variables "ON ROLLBACK
DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON
ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c).

--
Fabien.

2018-09-07 14:34 GMT+02:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Pavel,
>
> here is updated patch - I wrote some transactional support
>>
>> I am not sure how these new features are understandable and if these
>> features does it better or not.
>>
>
> There are possibility to reset to default value when
>>
>> a) any transaction is finished - the scope of value is limited by
>> transaction
>>
>> CREATE VARIABLE foo int ON TRANSACTION END RESET;
>>
>
> With this option I understand that it is a "within a transactionnal"
> variable, i.e. when the transaction ends, whether commit or rollback, the
> variable is reset to a default variable. It is not really a "session"
> variable anymore, each transaction has its own value.
>

yes, the correct name should be "schema variable with transaction scope". I
think it can be useful like short life global variable. These variables can
works like transaction caches.

> -- begin session
> -- foo has default value, eg NULL
> BEGIN;
> LET foo = 1;
> COMMIT/ROLLBACK;
> -- foo has default value again, NULL
>
> b) when transaction finished by rollback
>>
>> CREATE VARIABLE foo int ON ROLLBACK RESET
>>
>
> That is a little bit safer and you are back to a SESSION-scope variable,
> which is reset to the default value if the (any) transaction fails?
>
> -- begin session
> -- foo has default value, eg NULL
> BEGIN;
> LET foo = 1;
> COMMIT;
> -- foo has value 1
> BEGIN;
> -- foo has value 1...
> ROLLBACK;
> -- foo has value NULL
>
> c) A more logical (from a transactional point of view - but not necessary
> simple to implement, I do not know) feature/variant would be to reset the
> value to the one it had at the beginning of the transaction, which is not
> necessarily the default.
>
> -- begin session
> -- foo has default value, eg NULL
> BEGIN;
> LET foo = 1;
> COMMIT;
> -- foo has value 1
> BEGIN;
> LET foo = 2; (*)
> -- foo has value 2
> ROLLBACK;
> -- foo has value 1 back, change (*) has been reverted
>
> Now, when I am thinking about it, the @b is simple, but not too practical -
>> when some fails, then we lost a value (any transaction inside session can
>> fails).
>>
>
> Indeed.
>
> The @a has sense - the behave is global value (what is not possible
>> in Postgres now), but this value is destroyed by any unhandled exceptions,
>> and it cleaned on transaction end. The @b is just for information and for
>> discussion, but I'll remove it - because it is obscure.
>>
>
> Indeed.
>
> The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
>> little bit unclean, because it has semantic "on transaction end", but if I
>> didn't implement @b, then ON COMMIT syntax can be used.
>>
>
> I was more arguing on the third (c) option, i.e. on rollback the value is
> reverted to its value at the beginning of the rollbacked transaction.
>

> At the minimum, ISTM that option (b) is enough to implement the audit
> pattern, but it would mean that any session which has a rollback, for any
> reason (deadlock, serialization...), would have to be reinitialized, which
> would be a drawback.
>
> The to options could be non-transactional session variables "ON ROLLBACK
> DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON
> ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c).
>

@b is hardly understandable for not trained people, because any rollback in
session does reset. But people expecting @c, or some near @c.

I understand so you talked about @c. Now I think so it is possible to
implement, but it is not trivial. The transactional behave have to
calculate not only with transactions, but with SAVEPOINTS and ROLLBACK TO
savepoints. On second hand, the implementation will be relatively compact.

I'll hold it in my memory, but there are harder issues (support for
parallelism).

Regards

Pavel

> --
> Fabien.
>
>

út 4. 9. 2018 v 9:21 odesílatel Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
napsal:

> AFAICS this patch does nothing to consider parallel safety -- that is,
> as things stand, a variable is allowed in a query that may be
> parallelised, but its value is not copied to workers, leading to
> incorrect results. For example:
>
> create table foo(a int);
> insert into foo select * from generate_series(1,1000000);
> create variable zero int;
> let zero = 0;
>
> explain (costs off) select count(*) from foo where a%10 = zero;
>
> QUERY PLAN
> -----------------------------------------------
> Finalize Aggregate
> -> Gather
> Workers Planned: 2
> -> Partial Aggregate
> -> Parallel Seq Scan on foo
> Filter: ((a % 10) = zero)
> (6 rows)
>
> select count(*) from foo where a%10 = zero;
>
> count
> -------
> 38037 -- Different random result each time, should be 100,000
> (1 row)
>
> Thoughts?
>

This issue should be fixed in attached patch (and more others).

The code is more cleaner now, there are more tests, and documentation is
mostly complete. I am sorry - my English is not good.
New features:

o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on
commit, reset variable on transaction end (commit, rollback)
o LET var = DEFAULT -- reset specified variable

Regards

Pavel

> Regards,
> Dean
>

> The code is more cleaner now, there are more tests, and documentation is
> mostly complete. I am sorry - my English is not good.
> New features:
>
> o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on
> commit, reset variable on transaction end (commit, rollback)
> o LET var = DEFAULT -- reset specified variable
>
>
fix some forgotten warnings and dependency issue
few more tests

Regards

Pavel

> Regards
>
> Pavel
>
>
>> Regards,
>> Dean
>>
>

so 15. 9. 2018 v 18:06 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
>
>
>> The code is more cleaner now, there are more tests, and documentation is
>> mostly complete. I am sorry - my English is not good.
>> New features:
>>
>> o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on
>> commit, reset variable on transaction end (commit, rollback)
>> o LET var = DEFAULT -- reset specified variable
>>
>>
> fix some forgotten warnings and dependency issue
> few more tests
>
>
new update:

o support NOT NULL check
o implementation limited transaction variables - these variables doesn't
respects subtransactions(this is much more complex), drop variable drops
content although the drop can be reverted (maybe this limit will be
removed).

CREATE TRANSACTION VARIABLE fx AS int;

LET fx = 10;
BEGIN
LET fx = 20;
ROLLBACK;

SELECT fx;

Regards

Pavel

> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>>
>>> Regards,
>>> Dean
>>>
>>

Hi

new update:

I fixed pg_restore, and I cleaned a code related to transaction processing

There should be a full functionality now.

Regards

Pavel