proposal: schema variables

On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:

Comments, notes?

I like it.

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

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

It might be nice to be able to generalize this.

Questions that then arise:

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

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

Nico
--

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

Hi

2017-10-27 0:07 GMT+02:00 Nico Williams <nico@cryptonector.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

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

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

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

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.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

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

2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki <
tsunakawa.takay@jp.fujitsu.com>:

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.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

Le 26/10/2017 à 09:21, Pavel Stehule a écrit :

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

regards

Pavel

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

  varname     CONSTANT INTEGER    := 500;

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

Regards

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

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

2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.darold@dalibo.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

On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel.stehule@gmail.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.travers@adjust.com>:

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

On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <pavel.stehule@gmail.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&amp;entry=gmail&amp;source=g&gt;

--
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.travers@adjust.com> wrote:

Pavel,

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

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

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

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

Cheers
Serge Rielau
Salesforce.com

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

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

Hi

2017-10-30 22:42 GMT+01:00 srielau <serge@rielau.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

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@rielau.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

Pavel,

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

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

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

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

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

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

Pavel,

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

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

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

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

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

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

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

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

Pavel,

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

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

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

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

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

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

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

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

2017-10-31 22:28 GMT+01:00 srielau <serge@rielau.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.

" 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@rielau.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

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

2017-11-01 19:03 GMT+01:00 Mark Dilger <hornschnorter@gmail.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

Le 01/11/2017 à 05:15, Pavel Stehule a écrit :

2017-10-31 22:28 GMT+01:00 srielau <serge@rielau.com
<mailto:serge@rielau.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.stehule@gmail.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

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

On 26 October 2017 at 15:21, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi,

I propose a new database object - a variable.

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

Yeah.

/messages/by-id/CAMsr+YF0G8_FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw@mail.gmail.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

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

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.stehule@gmail.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
--

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

2017-11-02 16:35 GMT+01:00 Nico Williams <nico@cryptonector.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.stehule@gmail.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

2017-11-02 16:07 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 26 October 2017 at 15:21, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

I propose a new database object - a variable.

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

Yeah.

/messages/by-id/CAMsr+YF0G8_
FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw%40mail.gmail.com#CAMsr+YF0G8_
FehQyFS8gSfnEer9OPsMOvpfniDJOVGQzJzHzsw@mail.gmail.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

Nico Williams <nico@cryptonector.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

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

2017-11-02 13:35 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:

On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule <pavel.stehule@gmail.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

On Thu, Nov 2, 2017 at 9:05 PM, Nico Williams <nico@cryptonector.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

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

On Thu, Nov 02, 2017 at 11:48:44AM -0400, Tom Lane wrote:

Nico Williams <nico@cryptonector.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
--

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

Some thoughts on this.

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

Nico Williams <nico@cryptonector.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@postgresql.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@gf.microolap.com

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

Hi

2017-11-13 13:15 GMT+01:00 Pavel Golub <pavel@microolap.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

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.stehule@gmail.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.g.johnston@gmail.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.stehule@gmail.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

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.stehule@gmail.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

2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.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

On 12.03.2018 09:54, Pavel Stehule wrote:

2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru
<mailto:p.luzanov@postgrespro.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.luzanov@postgrespro.ru>:

On 12.03.2018 09:54, Pavel Stehule wrote:

2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.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 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.luzanov@postgrespro.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

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.stehule@gmail.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

2018-03-21 6:24 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-20 18:38 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.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

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.zakirov@postgrespro.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

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.zakirov@postgrespro.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

On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule <pavel.stehule@gmail.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@gmail.com>:

On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule <pavel.stehule@gmail.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

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@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.eisentraut@2ndquadrant.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

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.darold@dalibo.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

Le 27/06/2018 à 13:22, Pavel Stehule a écrit :

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles.darold@dalibo.com
<mailto:gilles.darold@dalibo.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.darold@dalibo.com>:

Le 27/06/2018 à 13:22, Pavel Stehule a écrit :

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles.darold@dalibo.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

2018-06-27 19:15 GMT+02:00 Gilles Darold <gilles.darold@dalibo.com>:

Le 27/06/2018 à 13:22, Pavel Stehule a écrit :

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold <gilles.darold@dalibo.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

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.stehule@gmail.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

Hi

2018-08-11 20:46 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-08-11 7:39 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.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

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@cri.ensmp.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

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@cri.ensmp.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

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@cri.ensmp.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

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.a.rasheed@gmail.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

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@cri.ensmp.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

út 4. 9. 2018 v 9:21 odesílatel Dean Rasheed <dean.a.rasheed@gmail.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

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

so 15. 9. 2018 v 18:06 odesílatel Pavel Stehule <pavel.stehule@gmail.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

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

Hello,

On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:

Hi

new update:

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

There should be a full functionality now.

I reviewed a little bit the patch. I have a few comments.

<title><structname>pg_views</structname> Columns</title>

I think there is a typo here. It should be "pg_variable".

GRANT { READ | WRITE | ALL [ PRIVILEGES ] }

Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the
constistency. Same for REVOKE. I'm not experienced syntax developer
though. But we use SELECT and LET commands when working with variables.
So we should GRANT and REVOKE priveleges for this commands.

[ { ON COMMIT DROP | ON TRANSACTION END RESET } ]

I think we may join them and have the syntax { ON COMMIT DROP | RESET }
to get more simpler syntax. If we create a variable with ON COMMIT
DROP, PostgreSQL will drop it regardless of whether transaction was
committed or rollbacked:

=# ...
=# begin;
=# create variable int1 int on commit drop;
=# rollback;
=# -- There is no variable int1

CREATE TABLE syntax has similar options [1]. ON COMMIT controls
the behaviour of temporary tables at the end a transaction block,
whether it was committed or rollbacked. But I'm not sure is this a good
example of precedence.

-	ONCOMMIT_DROP				/* ON COMMIT DROP */
+	ONCOMMIT_DROP,				/* ON COMMIT DROP */
} OnCommitAction;

There is the extra comma here after ONCOMMIT_DROP.

1 - https://www.postgresql.org/docs/current/static/sql-createtable.html

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

Hi
st 19. 9. 2018 v 13:23 odesílatel Arthur Zakirov <a.zakirov@postgrespro.ru>
napsal:

Hello,

On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:

Hi

new update:

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

processing

There should be a full functionality now.

I reviewed a little bit the patch. I have a few comments.

<title><structname>pg_views</structname> Columns</title>

I think there is a typo here. It should be "pg_variable".

I'll fix it.

GRANT { READ | WRITE | ALL [ PRIVILEGES ] }

Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the
constistency. Same for REVOKE. I'm not experienced syntax developer
though. But we use SELECT and LET commands when working with variables.
So we should GRANT and REVOKE priveleges for this commands.

I understand to your proposal, - and I have not strong opinion. Originally
I proposed {SELECT|UPDATE), but some people prefer {READ|WRITE}. Now I
prefer Peter's proposal (what is implemented now) - READ|WRITE, because it
is very illustrative - and the mentioned difference is good because the
variables are not tables (by default), are not persistent, so different
rights are good for me. I see "GRANT LET" like very low clear, because
nobody knows what LET command does. Unfortunately we cannot to use standard
"SET" command, because it is used in Postgres for different purpose.
READ|WRITE are totally clear, and for user it is another signal so
variables are different than tables (so it is not one row table).

I prefer current state, but if common opinion will be different, I have not
problem to change it.

[ { ON COMMIT DROP | ON TRANSACTION END RESET } ]

I think we may join them and have the syntax { ON COMMIT DROP | RESET }
to get more simpler syntax. If we create a variable with ON COMMIT
DROP, PostgreSQL will drop it regardless of whether transaction was
committed or rollbacked:

I though about it too. I'll try to explain my idea. Originally I was
surprised so postgres uses "ON COMMIT" syntax, but in documentation is used
term "at transaction end". But it has some sense. ON COMMIT DROP is allowed
only for temporary tables and ON COMMIT DELETE ROWS is allowed for tables.
With these clauses the PostgreSQL is more aggressive in cleaning. It
doesn't need to calculate with rollback, because the rollback does cleaning
by self. So syntax "ON COMMIT" is fully correct it is related only for
commit event. It has not sense on rollback event (and doesn't change a
behave on rollback event).

The content of variables is not transactional (by default). It is not
destroyed by rollback. So I have to calculate with rollback too. So the
most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is little
bit messy and I used "ON TRANSACTION END". It should be signal, so this
event is effective on rollback event and it is valid for not transaction
variable. This logic is not valid to transactional variables, where ON
COMMIT RESET has sense. But this behave is not default and then I prefer
more generic syntax.

Again I have not a problem to change it, but I am thinking so current
design is logically correct.

=# ...
=# begin;
=# create variable int1 int on commit drop;
=# rollback;
=# -- There is no variable int1

PostgreSQL catalog is transactional (where the metadata is stored), so when
I am working with metadata, then I use ON COMMIT syntax, because the behave
of ON ROLLBACK cannot be changed.

So I see two different cases - work with catalog (what is transactional)
and work with variable value, what is (like other variables in programming
languages) not transactional. "ON TRANSACTION END RESET" means - does reset
on any transaction end.

I hope so I explained it cleanly - if not, please, ask.

CREATE TABLE syntax has similar options [1]. ON COMMIT controls

the behaviour of temporary tables at the end a transaction block,
whether it was committed or rollbacked. But I'm not sure is this a good
example of precedence.

-     ONCOMMIT_DROP                           /* ON COMMIT DROP */
+     ONCOMMIT_DROP,                          /* ON COMMIT DROP */
} OnCommitAction;

There is the extra comma here after ONCOMMIT_DROP.

I'll fix it.

Regards

Pavel

On Wed, Sep 19, 2018 at 02:08:04PM +0200, Pavel Stehule wrote:

Unfortunately we cannot to use standard
"SET" command, because it is used in Postgres for different purpose.
READ|WRITE are totally clear, and for user it is another signal so
variables are different than tables (so it is not one row table).

I prefer current state, but if common opinion will be different, I have not
problem to change it.

I see. I grepped the thread before writhing this but somehow missed the
discussion.

The content of variables is not transactional (by default). It is not
destroyed by rollback. So I have to calculate with rollback too. So the
most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is little
bit messy and I used "ON TRANSACTION END". It should be signal, so this
event is effective on rollback event and it is valid for not transaction
variable. This logic is not valid to transactional variables, where ON
COMMIT RESET has sense. But this behave is not default and then I prefer
more generic syntax.
...
So I see two different cases - work with catalog (what is transactional)
and work with variable value, what is (like other variables in programming
languages) not transactional. "ON TRANSACTION END RESET" means - does reset
on any transaction end.

I hope so I explained it cleanly - if not, please, ask.

I understood what you mean, thank you. I thought that
{ ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only
for transactional variables in the first place. But is there any sense
in using this parameters with non-transactional variables? That is when
we create non-transactional variable we don't want that the variable
will rollback or reset its value after the end of a transaction.

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

st 19. 9. 2018 v 14:53 odesílatel Arthur Zakirov <a.zakirov@postgrespro.ru>
napsal:

On Wed, Sep 19, 2018 at 02:08:04PM +0200, Pavel Stehule wrote:

Unfortunately we cannot to use standard
"SET" command, because it is used in Postgres for different purpose.
READ|WRITE are totally clear, and for user it is another signal so
variables are different than tables (so it is not one row table).

I prefer current state, but if common opinion will be different, I have

not

problem to change it.

I see. I grepped the thread before writhing this but somehow missed the
discussion.

The content of variables is not transactional (by default). It is not
destroyed by rollback. So I have to calculate with rollback too. So the
most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is

little

bit messy and I used "ON TRANSACTION END". It should be signal, so this
event is effective on rollback event and it is valid for not transaction
variable. This logic is not valid to transactional variables, where ON
COMMIT RESET has sense. But this behave is not default and then I prefer
more generic syntax.
...
So I see two different cases - work with catalog (what is transactional)
and work with variable value, what is (like other variables in

programming

languages) not transactional. "ON TRANSACTION END RESET" means - does

reset

on any transaction end.

I hope so I explained it cleanly - if not, please, ask.

I understood what you mean, thank you. I thought that
{ ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only
for transactional variables in the first place. But is there any sense
in using this parameters with non-transactional variables? That is when
we create non-transactional variable we don't want that the variable
will rollback or reset its value after the end of a transaction.

ON COMMIT DROP is used only for temp variables (transaction or not
transaction). The purpose is same like for tables. Sometimes you can to
have object with shorter life than is session.

ON TRANSACTION END RESET has sense mainly for not transaction variables. I
see two use cases.

1. protect some sensitive data - on transaction end guaranteed reset and
cleaning on end transaction. So you can be sure, so variable is not
initialized (has default value), or you are inside transaction.

2. automatic initialization - ON TRANSACTION END RESET ensure so variable
is in init state for any transaction.

Both cases has sense for transaction or not transaction variables.

I am thinking so transaction life time for content has sense. Is cheaper to
reset variable than drop it (what ON COMMIT DROP does)

What do you think?

Pavel

Hi

st 19. 9. 2018 v 13:23 odesílatel Arthur Zakirov <a.zakirov@postgrespro.ru>
napsal:

Hello,

On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:

Hi

new update:

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

processing

There should be a full functionality now.

I reviewed a little bit the patch. I have a few comments.

<title><structname>pg_views</structname> Columns</title>

I think there is a typo here. It should be "pg_variable".

fixed

-     ONCOMMIT_DROP                           /* ON COMMIT DROP */
+     ONCOMMIT_DROP,                          /* ON COMMIT DROP */
} OnCommitAction;

There is the extra comma here after ONCOMMIT_DROP.

fixed

Thank you for comments

attached updated patch

On Wed, Sep 19, 2018 at 04:36:40PM +0200, Pavel Stehule wrote:

ON COMMIT DROP is used only for temp variables (transaction or not
transaction). The purpose is same like for tables. Sometimes you can to
have object with shorter life than is session.

ON TRANSACTION END RESET has sense mainly for not transaction variables. I
see two use cases.

1. protect some sensitive data - on transaction end guaranteed reset and
cleaning on end transaction. So you can be sure, so variable is not
initialized (has default value), or you are inside transaction.

2. automatic initialization - ON TRANSACTION END RESET ensure so variable
is in init state for any transaction.

Both cases has sense for transaction or not transaction variables.

I am thinking so transaction life time for content has sense. Is cheaper to
reset variable than drop it (what ON COMMIT DROP does)

What do you think?

Thanks, I understood the cases.

But I think there is more sense to use these options only with transactional
variables. It is more consistent and simple for me.

As a summary, it is 1 voice vs 1 voice :) So it is better to leave the
syntax as is without changes for now.

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

pá 21. 9. 2018 v 21:46 odesílatel Arthur Zakirov <a.zakirov@postgrespro.ru>
napsal:

On Wed, Sep 19, 2018 at 04:36:40PM +0200, Pavel Stehule wrote:

ON COMMIT DROP is used only for temp variables (transaction or not
transaction). The purpose is same like for tables. Sometimes you can to
have object with shorter life than is session.

ON TRANSACTION END RESET has sense mainly for not transaction variables.

I

see two use cases.

1. protect some sensitive data - on transaction end guaranteed reset and
cleaning on end transaction. So you can be sure, so variable is not
initialized (has default value), or you are inside transaction.

2. automatic initialization - ON TRANSACTION END RESET ensure so variable
is in init state for any transaction.

Both cases has sense for transaction or not transaction variables.

I am thinking so transaction life time for content has sense. Is cheaper

to

reset variable than drop it (what ON COMMIT DROP does)

What do you think?

Thanks, I understood the cases.

But I think there is more sense to use these options only with
transactional
variables. It is more consistent and simple for me.

I agree so it can be hard to imagine - and if I return back to start
discussion about schema variables - it can be hard because it joins some
concepts - variables has persistent transactional metadata, but the content
is not transactional.

I don't think so the variability is a issue in this case. There is a lot of
examples, so lot of combinations are possible - global temp tables and
package variables (Oracle), local temp tables and local variables
(Postgres), session variables and memory tables (MSSQL). Any combination of
feature has cases where can be very practical and useful.

ON TRANSACTION END RESET can be useful, because we have not a session event
triggers (and in this moment I am not sure if it is necessary and practical
- their usage can be very fragile). But some work can do ON xxx clauses,
that should not to have negative impact on performance or fragility.

ON TRANSACTION END RESET ensure cleaned and initialized to default value
for any transaction. Other possibility is ON COMMAND END RESET (but I would
not to implement it now), ...

As a summary, it is 1 voice vs 1 voice :) So it is better to leave the
syntax as is without changes for now.

:) now is enough time to think about syntax. Some features can be removed
and returned back later, where this concept will be more absorbed.

Regards

Pavel

Hi

rebased against yesterday changes in tab-complete.c

Regards

Pavel

so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased against yesterday changes in tab-complete.c

rebased against last changes in master

Regards

Pavel

so 29. 9. 2018 v 10:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased against yesterday changes in tab-complete.c

rebased against last changes in master

+ using content of schema variable for estimation
+ subtransaction support

I hope so now, there are almost complete functionality. Please, check it.

Regards

Pavel

On Sun, Sep 30, 2018 at 11:20 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

I hope so now, there are almost complete functionality. Please, check it.

Hi Pavel,

FYI there is a regression test failure on Windows:

plpgsql ... FAILED

*** 4071,4077 ****
end;
$$ language plpgsql;
select stacked_diagnostics_test();
- NOTICE: sqlstate: 22012, message: division by zero, context:
[PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement
"SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test()
line 6 at PERFORM]
+ NOTICE: sqlstate: 42702, message: column reference "v" is ambiguous,
context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL
statement "SELECT zero_divide()" <- PL/pgSQL function
stacked_diagnostics_test() line 6 at PERFORM]

https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.15234

--
Thomas Munro
http://www.enterprisedb.com

st 3. 10. 2018 v 1:01 odesílatel Thomas Munro <thomas.munro@enterprisedb.com>
napsal:

On Sun, Sep 30, 2018 at 11:20 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

I hope so now, there are almost complete functionality. Please, check it.

Hi Pavel,

FYI there is a regression test failure on Windows:

plpgsql ... FAILED

*** 4071,4077 ****
end;
$$ language plpgsql;
select stacked_diagnostics_test();
- NOTICE: sqlstate: 22012, message: division by zero, context:
[PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement
"SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test()
line 6 at PERFORM]
+ NOTICE: sqlstate: 42702, message: column reference "v" is ambiguous,
context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL
statement "SELECT zero_divide()" <- PL/pgSQL function
stacked_diagnostics_test() line 6 at PERFORM]

https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.15234

please, check attached patch

Thank you for report

Pavel

Hi

ne 30. 9. 2018 v 0:19 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

so 29. 9. 2018 v 10:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased against yesterday changes in tab-complete.c

rebased against last changes in master

+ using content of schema variable for estimation
+ subtransaction support

I hope so now, there are almost complete functionality. Please, check it.

new update

minor white space issue
one more regress test and 2 pg_dump tests

Regards

Pavel

[schema-variables-20181007-01.patch.gz]

Hi,

I tried to test your schema-variables patch but got stuck here instead
(after applying succesfully on top of e6f5d1acc):

make[2]: *** No rule to make target
'../../../src/include/catalog/pg_variable.h', needed by 'bki-stamp'.
Stop.
make[1]: *** [submake-catalog-headers] Error 2
make[1]: *** Waiting for unfinished jobs....
make: *** [submake-generated-headers] Error 2
Makefile:141: recipe for target 'submake-catalog-headers' failed
src/Makefile.global:370: recipe for target 'submake-generated-headers'
failed

thanks,

Erik Rijkers

Hi

út 23. 10. 2018 v 14:50 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

[schema-variables-20181007-01.patch.gz]

Hi,

I tried to test your schema-variables patch but got stuck here instead
(after applying succesfully on top of e6f5d1acc):

make[2]: *** No rule to make target
'../../../src/include/catalog/pg_variable.h', needed by 'bki-stamp'.
Stop.
make[1]: *** [submake-catalog-headers] Error 2
make[1]: *** Waiting for unfinished jobs....
make: *** [submake-generated-headers] Error 2
Makefile:141: recipe for target 'submake-catalog-headers' failed
src/Makefile.global:370: recipe for target 'submake-generated-headers'
failed

Unfortunately previous patch was completely broken. I am sorry

Please, check this patch.

Regards

Pavel

Hi

just rebase

Regards

Pavel

On Wed, Nov 21, 2018 at 8:25 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

just rebase

Thanks for working on this patch.

I'm a bit confused, but cfbot again says that there are some conflicts.
Probably they are the minor one, from src/bin/psql/help.c

For now I'm moving it to the next CF.

so 1. 12. 2018 v 0:16 odesílatel Dmitry Dolgov <9erthalion6@gmail.com>
napsal:

On Wed, Nov 21, 2018 at 8:25 AM Pavel Stehule <pavel.stehule@gmail.com>

wrote:

just rebase

Thanks for working on this patch.

I'm a bit confused, but cfbot again says that there are some conflicts.
Probably they are the minor one, from src/bin/psql/help.c

rebased again

Regards

Pavel

st 21. 11. 2018 v 8:24 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

just rebase

rebase

Regards

Pavel

On 2018-12-31 14:23, Pavel Stehule wrote:

st 21. 11. 2018 v 8:24 odesílatel Pavel Stehule
<pavel.stehule@gmail.com>

[schema-variables-20181231-01.patch.gz]

Hi Pavel,

I gave this a quick try-out with the script I had from previous
versions,
and found these two errors:

------------
drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1; --> error 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2; --> error 4287
select schema1.myvar2;
------------

The above, ran with psql -qXa gives the following output:

drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1; --> error 49
ERROR: unrecognized object type: 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
myvar1
--------

(1 row)

let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
myvar1
-------------------
variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
myvar2
-------------------
variable value ""
(1 row)

create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
myvar1
-------------------
variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2; --> error 4287
ERROR: unsupported object class 4287
select schema1.myvar2;
myvar2
-------------------
variable value ""
(1 row)

thanks,

Erik Rijkers

Hi

po 31. 12. 2018 v 16:40 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2018-12-31 14:23, Pavel Stehule wrote:

st 21. 11. 2018 v 8:24 odesílatel Pavel Stehule
<pavel.stehule@gmail.com>

[schema-variables-20181231-01.patch.gz]

Hi Pavel,

I gave this a quick try-out with the script I had from previous
versions,
and found these two errors:

------------
drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1; --> error 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2; --> error 4287
select schema1.myvar2;
------------

The above, ran with psql -qXa gives the following output:

drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1; --> error 49
ERROR: unrecognized object type: 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
myvar1
--------

(1 row)

let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
myvar1
-------------------
variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
myvar2
-------------------
variable value ""
(1 row)

create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
myvar1
-------------------
variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2; --> error 4287
ERROR: unsupported object class 4287
select schema1.myvar2;
myvar2
-------------------
variable value ""
(1 row)

Should be fixed now.

Thank you for report

Pavel

Hi

fresh rebased patch, no other changes

Pavel

Hi

just rebase

Regards

Pavel

Hi

just rebase

regards

Pavel

Hi

čt 31. 1. 2019 v 12:49 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

just rebase

regards

Pavel

rebase and fix compilation due changes related pg_dump

Regards

Pavel

On 3/3/19 10:27 PM, Pavel Stehule wrote:

rebase and fix compilation due changes related pg_dump

This patch hasn't receive any review in a while and I'm not sure if
that's because nobody is interested or the reviewers think it does not
need any more review.

It seems to me that this patch as implemented does not quite satisfy any
one.

I think we need to hear something from the reviewers soon or I'll push
this patch to PG13 as Andres recommends [1]/messages/by-id/20190216054526.zss2cufdxfeudr4i@alap3.anarazel.de.

--
-David
david@pgmasters.net

[1]: /messages/by-id/20190216054526.zss2cufdxfeudr4i@alap3.anarazel.de
/messages/by-id/20190216054526.zss2cufdxfeudr4i@alap3.anarazel.de

Hello David,

This patch hasn't receive any review in a while and I'm not sure if that's
because nobody is interested or the reviewers think it does not need any more
review.

It seems to me that this patch as implemented does not quite satisfy any one.

I think we need to hear something from the reviewers soon or I'll push this
patch to PG13 as Andres recommends [1].

I have discussed the feature extensively with Pavel on the initial thread.

My strong opinion based on the underlying use case is that it that such
session variables should be transactional by default, and Pavel strong
opinion is that they should not, to be closer to Oracle comparable
feature.

According to the documentation, the current implementation does provide a
transactional feature. However, it is not the default behavior, so I'm in
disagreement on a key feature, although I do really appreciate that Pavel
implemented the transactional behavior.

Otherwise, ISTM that they could be named "SESSION VARIABLE" because the
variable only exists in memory, in a session, and we could thing of adding
other kind of variables later on.

I do intend to review it in depth when it is transactional by default.

Anyway, the patch is non trivial and very large, so targetting v12 now is
indeed out of reach.

--
Fabien.

čt 7. 3. 2019 v 9:10 odesílatel Fabien COELHO <coelho@cri.ensmp.fr> napsal:

Hello David,

This patch hasn't receive any review in a while and I'm not sure if

that's

because nobody is interested or the reviewers think it does not need any

more

review.

It seems to me that this patch as implemented does not quite satisfy any

one.

I think we need to hear something from the reviewers soon or I'll push

this

patch to PG13 as Andres recommends [1].

I have discussed the feature extensively with Pavel on the initial thread.

My strong opinion based on the underlying use case is that it that such
session variables should be transactional by default, and Pavel strong
opinion is that they should not, to be closer to Oracle comparable
feature.

According to the documentation, the current implementation does provide a
transactional feature. However, it is not the default behavior, so I'm in
disagreement on a key feature, although I do really appreciate that Pavel
implemented the transactional behavior.

Otherwise, ISTM that they could be named "SESSION VARIABLE" because the
variable only exists in memory, in a session, and we could thing of adding
other kind of variables later on.

I do intend to review it in depth when it is transactional by default.

I am sorry. I cannot to support this request. Variables are not
transactional. My opinion is strong in this part.

I would not to repeat this discussion from start. I am sorry.

Regards

Pavel

Hi

My strong opinion based on the underlying use case is that it that such
session variables should be transactional by default, and Pavel strong
opinion is that they should not, to be closer to Oracle comparable
feature.

It is closer to any known database Oracle, DB2, Firebird, MSSQL, MySQL,

Regards

Pavel

On 3/7/19 10:10 AM, Fabien COELHO wrote:

Anyway, the patch is non trivial and very large, so targetting v12 now
is indeed out of reach.

Agreed. I have set the target version to PG13.

Regards,
--
-David
david@pgmasters.net

Hi

rebase against current master

Regards

Pavel

On 2019-03-24 06:57, Pavel Stehule wrote:

Hi

rebase against current master

I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR: schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

(both statements are needed to force the crash)

thanks,

Erik Rijkers

ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2019-03-24 06:57, Pavel Stehule wrote:

Hi

rebase against current master

I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR: schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

(both statements are needed to force the crash)

I cannot to reproduce it.

please, try compilation with "make distclean"; configure ..

or if the problem persists, please send test case, or backtrace

Regards

Pavel

On 2019-03-24 10:32, Pavel Stehule wrote:

ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2019-03-24 06:57, Pavel Stehule wrote:

Hi

rebase against current master

I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR: schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

(both statements are needed to force the crash)

I cannot to reproduce it.
[backtrace and stuff]

Sorry, I don't have the wherewithal to get more info but I have repeated
this now on 4 different machines (debian jessie/stretch; centos).

I did notice that sometimes those two offending lines
"
drop variable varschema2.testv cascade;
create variable if not exists testv as text;
"
have to be repeated a few times (never more than 4 or 5 times) before
the crash occurs (signal 11: Segmentation fault).

Erik Rijkers

Hi

ne 24. 3. 2019 v 6:57 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebase against current master

fixed issue IF NOT EXISTS & related regress tests

Regards

Pavel

po 25. 3. 2019 v 20:40 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2019-03-24 10:32, Pavel Stehule wrote:

ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

On 2019-03-24 06:57, Pavel Stehule wrote:

Hi

rebase against current master

I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR: schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost

(both statements are needed to force the crash)

I cannot to reproduce it.
[backtrace and stuff]

Sorry, I don't have the wherewithal to get more info but I have repeated
this now on 4 different machines (debian jessie/stretch; centos).

I did notice that sometimes those two offending lines
"
drop variable varschema2.testv cascade;
create variable if not exists testv as text;
"
have to be repeated a few times (never more than 4 or 5 times) before
the crash occurs (signal 11: Segmentation fault).

Should be fixed now.

Thank you for report

Pavel

út 26. 3. 2019 v 6:40 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

ne 24. 3. 2019 v 6:57 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebase against current master

fixed issue IF NOT EXISTS & related regress tests

another rebase

Regards

Pavel

fresh rebase

Regards

Pavel

Hi

rebased patch

Regards

Pavel

Hi

čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased patch

rebase after pgindent

Regards

Pavel

pá 24. 5. 2019 v 19:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased patch

rebase after pgindent

fresh rebase

Regards

Pavel

Hi

ne 30. 6. 2019 v 5:10 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

pá 24. 5. 2019 v 19:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

rebased patch

rebase after pgindent

fresh rebase

just rebase again

Regards

Pavel

Hi

just rebase

Regards

Pavel

Hi

so 10. 8. 2019 v 9:10 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

just rebase

fresh rebase

Regards

Pavel

Hi

minor change - replace heap_tuple_fetch_attr by detoast_external_attr.

Regards

Pavel

pá 4. 10. 2019 v 6:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

minor change - replace heap_tuple_fetch_attr by detoast_external_attr.

similar update - heap_open, heap_close was replaced by table_open,
table_close

Regards

Pavel

ne 3. 11. 2019 v 17:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

minor change - replace heap_tuple_fetch_attr by detoast_external_attr.

similar update - heap_open, heap_close was replaced by table_open,
table_close

fresh rebase

Regards

Pavel

po 18. 11. 2019 v 19:47 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

ne 3. 11. 2019 v 17:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

minor change - replace heap_tuple_fetch_attr by detoast_external_attr.

similar update - heap_open, heap_close was replaced by table_open,
table_close

fresh rebase

only rebase

Regards

Pavel

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

Hi Pavel,

First of all, I would like to congratulate you for this great work. This patch is really cool. The lack of package variables is sometimes a blocking issue for Oracle to Postgres migrations, because the usual emulation with GUC is sometimes not enough, in particular when there are security concerns or when the database is used in a public cloud.

As I look forward to having this patch commited, I decided to spend some time to participate to the review, although I am not a C specialist and I have not a good knowledge of the Postgres internals. Here is my report.

A) Installation

The patch applies correctly and the compilation is fine. The "make check" doesn't report any issue.

B) Basic usage

I tried some simple schema variables use cases. No problem.

C) The interface

The SQL changes look good to me.

However, in the CREATE VARIABLE command, I would replace the "TRANSACTION" word by "TRANSACTIONAL".

I have also tried to replace this word by a ON ROLLBACK clause at the end of the statement, like for ON COMMIT, but I have not found a satisfying wording to propose.

D) Behaviour

I am ok with variables not being transactional by default. That's the most simple, the most efficient, it emulates the package variables of other RDBMS and it will probably fit the most common use cases.

Note that I am not strongly opposed to having by default transactional variables. But I don't know whether this change would be a great work. We would have at least to find another keyword in the CREATE VARIABLE statement. Something like "NON-TRANSACTIONAL VARIABLE" ?

It is possible to create a NOT NULL variable without DEFAULT. When trying to read the variable before a LET statement, one gets an error massage saying that the NULL value is not allowed (and the documentation is clear about this case). Just for the records, I wondered whether it wouldn't be better to forbid a NOT NULL variable creation that wouldn't have a DEFAULT value. But finally, I think this behaviour provides a good way to force the variable initialisation before its use. So let's keep it as is.

E) ACL and Rights

I played a little bit with the GRANT and REVOKE statements.

I have got an error (Issue 1). The following statement chain:
create variable public.sv1 int;
grant read on variable sv1 to other_user;
drop owned by other_user;
reports : ERROR: unexpected object class 4287

I then tried to use DEFAULT PRIVILEGES. Despite this is not documented, I successfuly performed:
alter default privileges in schema public grant read on variables to simple_user;
alter default privileges in schema public grant write on variables to simple_user;

When variables are then created, the grants are properly given.
And the psql \ddp command perfectly returns:
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+------+-------------------------
postgres | public | | simple_user=SW/postgres
(1 row)

So the ALTER DEFAULT PRIVILEGES documentation chapter has to reflect this new syntax (Issue 2).

BTW, in the ACL, the READ privilege is represented by a S letter. A comment in the source reports that the R letter was used in the past for rule privilege. Looking at the postgres sources, I see that this privilege on rules has been suppressed in 8.2, so 13 years ago. As this R letter would be a so much better choice, I wonder whether it couldn't be reused now for this new purpose. Is it important to keep this letter frozen ?

F) Extension

I then created an extension, whose installation script creates a schema variable and functions that use it. The schema variable is correctly linked to the extension, so that dropping the extension drops the variable.

But there is an issue when dumping the database (Issue 3). The script generated by pg_dump includes the CREATE EXTENSION statement as expected but also a redundant CREATE VARIABLE statement for the variable that belongs to the extension. As a result, one of course gets an error at restore time.

G) Row Level Security

I did a test activating RLS on a table and creating a POLICY that references a schema variable in its USING and WITH CHECK clauses. Everything worked fine.

H) psql

A \dV meta-command displays all the created variables.
I would change a little bit the provided view. More precisely I would:
- rename "Constraint" into "Is nullable" and report it as a boolean
- rename "Special behave" into "Is transactional" and report it as a boolean
- change the order of columns so to have:
Schema | Name | Type | Is nullable | Default | Owner | Is transactional | Transaction end action
"Is nullable" being aside "Default"

I) Performance

I just quickly looked at the performance, and didn't notice any issue.

About variables read performance, I have noticed that:
select sum(1) from generate_series(1,10000000);
and
select sum(sv1) from generate_series(1,10000000);
have similar response times.

About planning, a condition with a variable used as a constant is indexable, as if it were a literal.

J) Documentation

There are some wordings to improve in the documentation. But I am not the best person to give advice about english language ;-).

However, aside the already mentionned lack of changes in the ALTER DEFAULT PRIVILEGES chapter, I also noticed :
- line 50 of the patch, the sentence "(hidden attribute; must be explicitly selected)" looks false as the oid column of pg_variable is displayed, as for other tables of the catalog;
- at several places, the word "behave" should be replaced by "behaviour"
- line 433, a get_schema_variable() function is mentionned; is it a function that can really be called by users ?

May be it would be interesting to also add a chapter in the Section V of the documentation, in order to more globally present the schema variables concept, aside the new or the modified statements.

K) Coding

I am not able to appreciate the way the feature has been coded. So I let this for other reviewers ;-)

To conclude, again, thanks a lot for this feature !
And if I may add this. I dream of an additional feature: adding a SHARED clause to the CREATE VARIABLE statement in order to be able to create memory spaces that could be shared by all connections on the database and accessible in SQL and PL, under the protection of ACL. But that's another story ;-)

Best regards. Philippe.

The new status of this patch is: Waiting on Author

Hi

ne 22. 12. 2019 v 13:04 odesílatel Philippe BEAUDOIN <phb07@apra.asso.fr>
napsal:

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

Hi Pavel,

First of all, I would like to congratulate you for this great work. This
patch is really cool. The lack of package variables is sometimes a blocking
issue for Oracle to Postgres migrations, because the usual emulation with
GUC is sometimes not enough, in particular when there are security concerns
or when the database is used in a public cloud.

As I look forward to having this patch commited, I decided to spend some
time to participate to the review, although I am not a C specialist and I
have not a good knowledge of the Postgres internals. Here is my report.

A) Installation

The patch applies correctly and the compilation is fine. The "make check"
doesn't report any issue.

B) Basic usage

I tried some simple schema variables use cases. No problem.

C) The interface

The SQL changes look good to me.

However, in the CREATE VARIABLE command, I would replace the "TRANSACTION"
word by "TRANSACTIONAL".

There is not technical problem - the problem is in introduction new keyword
"transactional" that is near to "transaction". I am not sure if it is
practical to have two "similar" keyword and how much the CREATE statement
has to use correct English grammar.

I am not native speaker, so I am not able to see how bad is using
"TRANSACTION" instead "TRANSACTIONAL" in this context. So I see a risk to
have two important (it is not syntactic sugar) similar keywords.

Just I afraid so using TRANSACTIONAL instead just TRANSACTION is not too
user friendly. I have not strong opinion about this - and the
implementation is easy, but I am not feel comfortable with introduction
this keyword.

I have also tried to replace this word by a ON ROLLBACK clause at the end
of the statement, like for ON COMMIT, but I have not found a satisfying
wording to propose.

D) Behaviour

I am ok with variables not being transactional by default. That's the most
simple, the most efficient, it emulates the package variables of other
RDBMS and it will probably fit the most common use cases.

Note that I am not strongly opposed to having by default transactional
variables. But I don't know whether this change would be a great work. We
would have at least to find another keyword in the CREATE VARIABLE
statement. Something like "NON-TRANSACTIONAL VARIABLE" ?

Variables almost everywhere (global user settings - GUC is only one planet
exception) are non transactional by default. I don't see any reason
introduce new different design than is wide used.

It is possible to create a NOT NULL variable without DEFAULT. When trying
to read the variable before a LET statement, one gets an error massage
saying that the NULL value is not allowed (and the documentation is clear
about this case). Just for the records, I wondered whether it wouldn't be
better to forbid a NOT NULL variable creation that wouldn't have a DEFAULT
value. But finally, I think this behaviour provides a good way to force the
variable initialisation before its use. So let's keep it as is.

This is a question - and there are two possibilities

postgres=# do $$
declare x int not null;
begin
raise notice '%', x;
end;
$$ ;
ERROR: variable "x" must have a default value, since it's declared NOT NULL
LINE 2: declare x int not null;
^

PLpgSQL requires it. But there is not a possibility to enforce future
setting.

So I know so behave of schema variables is little bit different, but I
think so this difference has interesting use case. You can check if the
variable was modified somewhere or not.

E) ACL and Rights

I played a little bit with the GRANT and REVOKE statements.

I have got an error (Issue 1). The following statement chain:
create variable public.sv1 int;
grant read on variable sv1 to other_user;
drop owned by other_user;
reports : ERROR: unexpected object class 4287

this is bug and should be fixed

I then tried to use DEFAULT PRIVILEGES. Despite this is not documented, I
successfuly performed:
alter default privileges in schema public grant read on variables to
simple_user;
alter default privileges in schema public grant write on variables to
simple_user;

When variables are then created, the grants are properly given.
And the psql \ddp command perfectly returns:
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+------+-------------------------
postgres | public | | simple_user=SW/postgres
(1 row)

So the ALTER DEFAULT PRIVILEGES documentation chapter has to reflect this
new syntax (Issue 2).

BTW, in the ACL, the READ privilege is represented by a S letter. A
comment in the source reports that the R letter was used in the past for
rule privilege. Looking at the postgres sources, I see that this privilege
on rules has been suppressed in 8.2, so 13 years ago. As this R letter
would be a so much better choice, I wonder whether it couldn't be reused
now for this new purpose. Is it important to keep this letter frozen ?

I have not a idea why it is. I'll recheck it - but in this moment I prefer
a consistency with existing ACL - it can be in future as one block if it
will be necessary for somebody.

F) Extension

I then created an extension, whose installation script creates a schema
variable and functions that use it. The schema variable is correctly linked
to the extension, so that dropping the extension drops the variable.

But there is an issue when dumping the database (Issue 3). The script
generated by pg_dump includes the CREATE EXTENSION statement as expected
but also a redundant CREATE VARIABLE statement for the variable that
belongs to the extension. As a result, one of course gets an error at
restore time.

It is bug and should be fixed

G) Row Level Security

I did a test activating RLS on a table and creating a POLICY that
references a schema variable in its USING and WITH CHECK clauses.
Everything worked fine.

H) psql

A \dV meta-command displays all the created variables.
I would change a little bit the provided view. More precisely I would:
- rename "Constraint" into "Is nullable" and report it as a boolean
- rename "Special behave" into "Is transactional" and report it as a
boolean
- change the order of columns so to have:
Schema | Name | Type | Is nullable | Default | Owner | Is transactional |
Transaction end action
"Is nullable" being aside "Default"

ok

I) Performance

I just quickly looked at the performance, and didn't notice any issue.

About variables read performance, I have noticed that:
select sum(1) from generate_series(1,10000000);
and
select sum(sv1) from generate_series(1,10000000);
have similar response times.

About planning, a condition with a variable used as a constant is
indexable, as if it were a literal.

J) Documentation

There are some wordings to improve in the documentation. But I am not the
best person to give advice about english language ;-).

However, aside the already mentionned lack of changes in the ALTER DEFAULT
PRIVILEGES chapter, I also noticed :
- line 50 of the patch, the sentence "(hidden attribute; must be
explicitly selected)" looks false as the oid column of pg_variable is
displayed, as for other tables of the catalog;
- at several places, the word "behave" should be replaced by "behaviour"
- line 433, a get_schema_variable() function is mentionned; is it a
function that can really be called by users ?

May be it would be interesting to also add a chapter in the Section V of
the documentation, in order to more globally present the schema variables
concept, aside the new or the modified statements.

K) Coding

I am not able to appreciate the way the feature has been coded. So I let
this for other reviewers ;-)

To conclude, again, thanks a lot for this feature !
And if I may add this. I dream of an additional feature: adding a SHARED
clause to the CREATE VARIABLE statement in order to be able to create
memory spaces that could be shared by all connections on the database and
accessible in SQL and PL, under the protection of ACL. But that's another
story ;-)

sure, it is another story :-).

Thank you for review - I'll try to fix bugs this week.

Pavel