Обсуждение: [HACKERS] 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
On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> Comments, notes?
I like it.
I would further like to move all of postgresql.conf into the database,
as much as possible, as well as pg_ident.conf and pg_hba.conf.
Variables like current_user have a sort of nesting context
functionality: calling a SECURITY DEFINER function "pushes" a new value
onto current_user, then when the function returns the new value of
current_user is "popped" and the previous value restored.
It might be nice to be able to generalize this.
Questions that then arise:
- can one see up the stack?
- are there permissions issues with seeing up the stack?
I recently posted proposing a feature such that SECURITY DEFINER
functions could observe the _caller_'s current_user.
Nico
--
> Hi, > > 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 andthis feature promote auto-translation of PL/SQL procedures? I'm curious what will be the major road blocks after addingthe 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
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
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
Great feature that will help for migration. How will you handle CONSTANTLe 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
>
>
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
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.SET varname = expression;The variables can be modified by SQL command SET (this is taken from standard, and it natural)The access rights is controlled by usual access rights - by commands GRANT/REVOKE. The possible rights are: READ, WRITE[ DEFAULT expression ] [[NOT] NULL]CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS typeCREATE VARIABLE myschema.myvar AS mytype;CREATE VARIABLE public.myvar AS integer;The variable can be created by CREATE statement: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 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.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.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.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.[ ON TRANSACTION END { RESET | DROP } ][ { VOLATILE | STABLE } ];It is dropped by command DROP VARIABLE [ IF EXISTS] varname.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?
regardsPavel
On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: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.SET varname = expression;The variables can be modified by SQL command SET (this is taken from standard, and it natural)The access rights is controlled by usual access rights - by commands GRANT/REVOKE. The possible rights are: READ, WRITE[ DEFAULT expression ] [[NOT] NULL]CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS typeCREATE VARIABLE myschema.myvar AS mytype;CREATE VARIABLE public.myvar AS integer;The variable can be created by CREATE statement: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 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.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.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.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.[ ON TRANSACTION END { RESET | DROP } ][ { VOLATILE | STABLE } ];It is dropped by command DROP VARIABLE [ IF EXISTS] varname.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 queries2. 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.
regardsPavel--Best Regards,Chris TraversDatabase Administrator
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.
RegardsPavelregardsPavel--Best Regards,Chris TraversDatabase Administrator
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 againstI 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 necessaryI 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.RegardsPavelregardsPavel--Best Regards,Chris TraversDatabase Administrator--Best Regards,Chris TraversDatabase Administrator
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
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
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.
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 centsSerge
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
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
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.”"Although the syntax ofCREATE 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 ownCREATE 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.
> 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
> Comments, notes?
How would variables behave on transaction rollback?
CREATE TEMP VARIABLE myvar;
SET myvar := 1;
BEGIN;
SET myvar := 2;
COMMIT;
BEGIN;
SET myvar := 3;
ROLLBACK;
SELECT myvar;
How would variables behave when modified in a procedure
that aborts rather than returning cleanly?
mark
2017-10-31 22:28 GMT+01:00 srielau <serge@rielau.com>:Pavel,
There is no
DECLARE TEMP CURSOR
or
DECLARE TEMP variable in PLpgSQL
andsure .. DECLARE TEMP has no sense, I talked about similarity DECLARE and CREATE TEMPCREATE 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. https://www.postgresql.org/message-id/flat/CAMsr%2BYF0G8_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. -- 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
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
--
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.
https://www.postgresql.org/message-id/flat/CAMsr%2BYF0G8_ FehQyFS8gSfnEer9OPsMOvpfniDJOV GQzJzHzsw%40mail.gmail.com# CAMsr+YF0G8_ FehQyFS8gSfnEer9OPsMOvpfniDJOV GQzJzHzsw@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
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
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
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
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
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
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
DO $$
$$;
Вложения
3. possibility to store short life data in fast secured storage2. available from any PL used by PostgreSQL, data can be shared between different PL1. feature like PL/SQL package variables (with similar content life cycle)I recap a goals (the order is random):HiI 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.
4. possibility to pass parameters and results to/from anonymous blocks5. 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
Вложения
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.3. possibility to store short life data in fast secured storage2. available from any PL used by PostgreSQL, data can be shared between different PL1. feature like PL/SQL package variables (with similar content life cycle)I recap a goals (the order is random):HiI 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.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 blocks5. session variables with possibility to process static code checkWhat does "process static code check" means here?
6. multiple API available from different environments - SQL commands, SQL functions, internal functionsI 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 formThoughts 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:andcould 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.
Вложения
Hiupdated patch with your changes in documentation and pg_dump (initial) supportMain 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.RegardsPavel
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 |
+--------+---------+
Вложения
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
Hi2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:Hiupdated patch with your changes in documentation and pg_dump (initial) supportMain 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.RegardsPavelnew 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 |
+--------+---------+RegardsPavel
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 CompanyOn 08.03.2018 21:00, Pavel Stehule wrote:Hi2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:Hiupdated patch with your changes in documentation and pg_dump (initial) supportMain 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.RegardsPavelnew 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 |
+--------+---------+RegardsPavel
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]
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:
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
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 } ];
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
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
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
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)
Вложения
4. variable is database object - the access rights are required3. variable, or any field of variable, can have defined default value2. composite can be defined on place or some composite type can be used1. scalar, composite and array variablesWhat is supported:HiI 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.5. the values are stored in binary form with defined typmodAn 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.
RegardsPavel
Вложения
2018-03-20 18:38 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:4. variable is database object - the access rights are required3. variable, or any field of variable, can have defined default value2. composite can be defined on place or some composite type can be used1. scalar, composite and array variablesWhat is supported:HiI 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.5. the values are stored in binary form with defined typmodAn 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
RegardsPavel
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
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
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
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'.
Вложения
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
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.
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
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
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,
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
Hi2018-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 comments1. The schema variables should to have own system table2. The composite schema variables should to use explicitly defined composite type3. 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 cRegards
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
Le 27/06/2018 à 13:22, Pavel Stehule a écrit :Hi2018-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 comments1. The schema variables should to have own system table2. The composite schema variables should to use explicitly defined composite type3. 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 cRegards
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
Le 27/06/2018 à 13:22, Pavel Stehule a écrit :Hi2018-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 comments1. The schema variables should to have own system table2. The composite schema variables should to use explicitly defined composite type3. 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 cRegards
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
Вложения
Вложения
HiI 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.
RegardsPavel
Вложения
2018-08-11 7:39 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:HiI 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.
RegardsPavelRegardsPavel
Вложения
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.
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 ?
--
Fabien.
Hello Pavel, >> AFAICR, I had an objection on such new objects when you first proposed >> something similar in October 2016. >> >> Namely, if session variables are not transactional, they cannot be used to >> implement security related auditing features which were advertised as the >> motivating use case: an the audit check may fail on a commit because of a >> differed constraint, but the variable would keep its "okay" value unduly, >> which would create a latent security issue, the audit check having failed >> but the variable saying the opposite. >> >> So my point was that they should be transactional by default, although I >> would be ok with an option for having a voluntary non transactional >> version. >> >> Is this issue addressed somehow with this ? > > > 1. I respect your opinion, but I dont agree with it. Oracle, db2 has > similar or very similar feature non transactional, and I didnt find any > requests to change it. The argument of authority that "X does it like that" is not a valid answer to my technical objection about security implications of this feature. > 2. the prototype implementation was based on relclass items, and some > transactional behave was possible. Peter E. had objections to this design > and proposed own catalog table. I did it. Now, the transactional behave is > harder to implement, although it is not impossible. This patch is not small > now, so I didnt implement it. "It is harder to implement" does not look like a valid answer either. > I have a strong opinion so default behave have to be non transactional. The fact that you have a "strong opinion" does not really answer my objection. Moreover, I said that I would be ok with a non transactional option, provided that a default transactional is available. > Transactional variables significantly increases complexity of this patch, > now is simple, because we can reset variable on drop variable command. > Maybe I miss some simply implementation, but I spent on it more than few > days. Still, any cooperation are welcome. "It is simpler to implement this way" is not an answer either, especially as you said that it could have been on point 2. As I do not see any clear answer to my objection about security implications, I understand that it is not addressed by this patch. At the bare minimum, if this feature ever made it as is, I think that a clear caveat must be included in the documentation about not using it for any security-related purpose. Also, I'm not really sure how useful such a non-transactional object can be for other purposes: the user should take into account that the transaction may fail and the value of the session variable be inconsistent as a result. Sometimes it may not matter, but if it matters there is no easy way around the fact. -- Fabien.
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.
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.
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 readIt 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.
>> 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
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
Вложения
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.
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, NULLb) 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 revertedNow, 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.
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
Вложения
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
RegardsPavel
Regards,
Dean
Вложения
The code is more cleaner now, there are more tests, and documentation is mostly complete. I am sorry - my English is not good.New features:o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on commit, reset variable on transaction end (commit, rollback)o LET var = DEFAULT -- reset specified variablefix some forgotten warnings and dependency issuefew more tests
RegardsPavelRegardsPavel
Regards,
Dean
Вложения
Вложения
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
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
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
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.
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".
> - 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
Вложения
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
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
Вложения
Hirebased against yesterday changes in tab-complete.c
RegardsPavel
Вложения
so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hirebased against yesterday changes in tab-complete.crebased against last changes in master
RegardsPavelRegardsPavel
Вложения
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
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
Вложения
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:Hirebased against yesterday changes in tab-complete.crebased against last changes in master+ using content of schema variable for estimation+ subtransaction supportI hope so now, there are almost complete functionality. Please, check it.
RegardsPavelRegardsPavelRegardsPavel
Вложения
> [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
> [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
Вложения
> 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.
> 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.
Вложения
Hijust rebase
RegardsPavel
Вложения
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
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
Вложения
Hijust rebaseregardsPavel
Вложения
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]. -- -David david@pgmasters.net [1] https://www.postgresql.org/message-id/20190216054526.zss2cufdxfeudr4i%40alap3.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.
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.
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.
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
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
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
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
Hirebase against current master
RegardsPavel
Вложения
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
Hine 24. 3. 2019 v 6:57 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hirebase against current masterfixed issue IF NOT EXISTS & related regress tests
RegardsPavelRegardsPavel
Вложения
Hirebased patch
RegardsPavel
Вложения
Hičt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hirebased patchrebase after pgindent
RegardsPavelRegardsPavel
Вложения
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:Hirebased patchrebase after pgindentfresh rebase
RegardsPavelRegardsPavelRegardsPavel
Вложения
Hijust rebase
RegardsPavel
Вложения
Hiso 10. 8. 2019 v 9:10 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hijust rebasefresh rebaseRegardsPavelRegardsPavel
Вложения
Himinor change - replace heap_tuple_fetch_attr by detoast_external_attr.
Вложения
čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Himinor change - replace heap_tuple_fetch_attr by detoast_external_attr.similar update - heap_open, heap_close was replaced by table_open, table_close
RegardsPavel
Вложения
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:Himinor change - replace heap_tuple_fetch_attr by detoast_external_attr.similar update - heap_open, heap_close was replaced by table_open, table_closefresh rebase
RegardsPavelRegardsPavel
Вложения
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 variablesis sometimes a blocking issue for Oracle to Postgres migrations, because the usual emulation with GUC is sometimesnot 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 amnot 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 havenot 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 packagevariables 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 wouldbe a great work. We would have at least to find another keyword in the CREATE VARIABLE statement. Something like "NON-TRANSACTIONALVARIABLE" ? It is possible to create a NOT NULL variable without DEFAULT. When trying to read the variable before a LET statement, onegets an error massage saying that the NULL value is not allowed (and the documentation is clear about this case). Justfor the records, I wondered whether it wouldn't be better to forbid a NOT NULL variable creation that wouldn't have aDEFAULT 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 usedin 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 reusednow 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 variableis 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 EXTENSIONstatement 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 CHECKclauses. 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_variableis 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 presentthe 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 orderto be able to create memory spaces that could be shared by all connections on the database and accessible in SQL andPL, under the protection of ACL. But that's another story ;-) Best regards. Philippe. The new status of this patch is: Waiting on Author
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.
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;
^
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
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
Вложения
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation: tested, failed Hi Pavel, I have tested the latest version of your patch. Both issues I reported are now fixed. And you largely applied my proposals. That's great ! I have also spent some time to review more closely the documentation. I will send you a direct mail with an attached filefor some minor comments on this topic. Except these documentation remarks to come, I haven't any other issue or suggestion to report. Note that I have not closely looked at the C code itself. But may be some other reviewers have already done that job. If yes, my feeling is that the patch could soon be set as "Ready for commiter". Best regards. Philippe. The new status of this patch is: Waiting on Author
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: tested, failed
Hi Pavel,
I have tested the latest version of your patch.
Both issues I reported are now fixed. And you largely applied my proposals. That's great !
I have also spent some time to review more closely the documentation. I will send you a direct mail with an attached file for some minor comments on this topic.
Except these documentation remarks to come, I haven't any other issue or suggestion to report.
Note that I have not closely looked at the C code itself. But may be some other reviewers have already done that job.
If yes, my feeling is that the patch could soon be set as "Ready for commiter".
Best regards. Philippe.
The new status of this patch is: Waiting on Author
Вложения
Hipo 30. 12. 2019 v 17:27 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, passed
Spec compliant: not tested
Documentation: tested, failed
Hi Pavel,
I have tested the latest version of your patch.
Both issues I reported are now fixed. And you largely applied my proposals. That's great !
I have also spent some time to review more closely the documentation. I will send you a direct mail with an attached file for some minor comments on this topic.
Except these documentation remarks to come, I haven't any other issue or suggestion to report.
Note that I have not closely looked at the C code itself. But may be some other reviewers have already done that job.
If yes, my feeling is that the patch could soon be set as "Ready for commiter".
Best regards. Philippe.
The new status of this patch is: Waiting on AuthorThank you very much for your comments, and notes. Updated patch attached.
RegardsPavel
Вложения
Hi, I did a quick review of this patch today, so let me share a couple of comments. Firstly, the patch is pretty large - it has ~270kB. Not the largest patch ever, but large. I think breaking the patch into smaller pieces would significantly improve the chnce of it getting committed. Is it possible to break the patch into smaller pieces that could be applied incrementally? For example, we could move the "transactional" behavior into a separate patch, but it's not clear to me how much code would that actually move to that second patch. Any other parts that could be moved to a separate patch? I see one of the main contention points was a rather long discussion about transactional vs. non-transactional behavior. I agree with Pavel's position that the non-transactional behavior should be the default, simply because it's better aligned with what the other databases are doing (and supporting migrations seems like one of the main use cases for this feature). I do understand it may not be suitable for some other use cases, mentioned by Fabien, but IMHO it's fine to require explicit specification of transactional behavior. Well, we can't have both as default, and I don't think there's an obvious reason why it should be the other way around. Now, a bunch of comments about the code (some of that nitpicking): 1) This hunk in doc/src/sgml/catalogs.sgml still talks about "table creation" instead of schema creation: <row> <entry><structfield>vartypmod</structfield></entry> <entry><type>int4</type></entry> <entry></entry> <entry> <structfield>vartypmod</structfield> records type-specific data supplied at table creation time (for example, the maximum length of a <type>varchar</type> column). It is passed to type-specific input functions and length coercion functions. The value will generally be -1 for types that do not need <structfield>vartypmod</structfield>. </entry> </row> 2) This hunk in doc/src/sgml/ref/alter_default_privileges.sgml uses "role_name" instead of "variable_name" GRANT { READ | WRITE | ALL [ PRIVILEGES ] } ON VARIABLES TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] 3) I find the syntax in create_variable.sgml a bit too over-complicated: <synopsis> CREATE [ { TEMPORARY | TEMP } ] [ { TRANSACTIONAL | TRANSACTION } ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>[ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceableclass="parameter">collation</replaceable> ] [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON { TRANSACTIONAL| TRANSACTION } END RESET } ] </synopsis> Do we really need both TRANSACTION and TRANSACTIONAL? Why not just one that we already have in the grammar (i.e. TRANSACTION)? 4) I think we should rename schemavariable.h to schema_variable.h. 5) objectaddress.c has extra line after 'break;' in one switch. 6) The comment is wrong: /* * Find the ObjectAddress for a type or domain */ static ObjectAddress get_object_address_variable(List *object, bool missing_ok) 7) I think the code/comments are really inconsistent in talking about "variables" and "schema variables". For example in objectaddress.c we do these two things: case OCLASS_VARIABLE: appendStringInfoString(&buffer, "schema variable"); break; vs. case DEFACLOBJ_VARIABLE: appendStringInfoString(&buffer, " on variables"); break; That's going to be confusing for people. 8) I'm rather confused by CMD_PLAN_UTILITY, which seems to be defined merely to support LET. I'm not sure why that's necessary (Why wouldn't CMD_UTILITY be sufficient?). Having to add conditions checking for CMD_PLAN_UTILITY to various places in planner.c is rather annoying, and I wonder how likely it's this will unnecessarily break external code in extensions etc. 9) This comment in planner.c seems obsolete (not updated to reflect addition of the CMD_PLAN_UTILITY check): /* * If this is an INSERT/UPDATE/DELETE, and we're not being called from * inheritance_planner, add the ModifyTable node. */ if (parse->commandType != CMD_SELECT && parse->commandType != CMD_PLAN_UTILITY && !inheritance_update) 10) I kinda wonder what happens when a function is used in a WHERE condition, but it depends on a variable and alsu mutates it on each call ... 11) I think Query->hasSchemaVariable (in analyze.c) should be renamed to hasSchemaVariables (which reflects the other fields referring to things like window functions etc.) 12) I find it rather suspicious that we make decisions in utility.c solely based on commandType (whether it's CMD_UTILITY or not). IMO it's pretty strange/ugly that T_LetStmt can be both CMD_UTILITY and CMD_PLAN_UTILITY: case T_LetStmt: { if (pstmt->commandType == CMD_UTILITY) doLetStmtReset(pstmt); else { Assert(pstmt->commandType == CMD_PLAN_UTILITY); doLetStmtEval(pstmt, params, queryEnv, queryString); } if (completionTag) strcpy(completionTag, "LET"); } break; 13) Not sure why we moved DO_TABLE in addBoundaryDependencies (pg_dump.c), seems unnecessary: case DO_CONVERSION: - case DO_TABLE: + case DO_VARIABLE: case DO_ATTRDEF: + case DO_TABLE: case DO_PROCLANG: 14) namespace.c defines VariableIsVisible twice: extern bool VariableIsVisible(Oid relid); ... extern bool VariableIsVisible(Oid varid); 15) I'd say lookup_variable and identify_variable should use camelcase just like the other functions in the same file. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi,
I did a quick review of this patch today, so let me share a couple of
comments.
Firstly, the patch is pretty large - it has ~270kB. Not the largest
patch ever, but large. I think breaking the patch into smaller pieces
would significantly improve the chnce of it getting committed.
Is it possible to break the patch into smaller pieces that could be
applied incrementally? For example, we could move the "transactional"
behavior into a separate patch, but it's not clear to me how much code
would that actually move to that second patch. Any other parts that
could be moved to a separate patch?
I see one of the main contention points was a rather long discussion
about transactional vs. non-transactional behavior. I agree with Pavel's
position that the non-transactional behavior should be the default,
simply because it's better aligned with what the other databases are
doing (and supporting migrations seems like one of the main use cases
for this feature).
I do understand it may not be suitable for some other use cases,
mentioned by Fabien, but IMHO it's fine to require explicit
specification of transactional behavior. Well, we can't have both as
default, and I don't think there's an obvious reason why it should be
the other way around.
Now, a bunch of comments about the code (some of that nitpicking):
1) This hunk in doc/src/sgml/catalogs.sgml still talks about "table
creation" instead of schema creation:
<row>
<entry><structfield>vartypmod</structfield></entry>
<entry><type>int4</type></entry>
<entry></entry>
<entry>
<structfield>vartypmod</structfield> records type-specific data
supplied at table creation time (for example, the maximum
length of a <type>varchar</type> column). It is passed to
type-specific input functions and length coercion functions.
The value will generally be -1 for types that do not need <structfield>vartypmod</structfield>.
</entry>
</row>
2) This hunk in doc/src/sgml/ref/alter_default_privileges.sgml uses
"role_name" instead of "variable_name"
GRANT { READ | WRITE | ALL [ PRIVILEGES ] }
ON VARIABLES
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
3) I find the syntax in create_variable.sgml a bit too over-complicated:
<synopsis>
CREATE [ { TEMPORARY | TEMP } ] [ { TRANSACTIONAL | TRANSACTION } ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
[ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON { TRANSACTIONAL | TRANSACTION } END RESET } ]
</synopsis>
Do we really need both TRANSACTION and TRANSACTIONAL? Why not just one
that we already have in the grammar (i.e. TRANSACTION)?
4) I think we should rename schemavariable.h to schema_variable.h.
5) objectaddress.c has extra line after 'break;' in one switch.
6) The comment is wrong:
/*
* Find the ObjectAddress for a type or domain
*/
static ObjectAddress
get_object_address_variable(List *object, bool missing_ok)
7) I think the code/comments are really inconsistent in talking about
"variables" and "schema variables". For example in objectaddress.c we do
these two things:
case OCLASS_VARIABLE:
appendStringInfoString(&buffer, "schema variable");
break;
vs.
case DEFACLOBJ_VARIABLE:
appendStringInfoString(&buffer,
" on variables");
break;
That's going to be confusing for people.
8) I'm rather confused by CMD_PLAN_UTILITY, which seems to be defined
merely to support LET. I'm not sure why that's necessary (Why wouldn't
CMD_UTILITY be sufficient?).
Having to add conditions checking for CMD_PLAN_UTILITY to various places
in planner.c is rather annoying, and I wonder how likely it's this will
unnecessarily break external code in extensions etc.
9) This comment in planner.c seems obsolete (not updated to reflect
addition of the CMD_PLAN_UTILITY check):
/*
* If this is an INSERT/UPDATE/DELETE, and we're not being called from
* inheritance_planner, add the ModifyTable node.
*/
if (parse->commandType != CMD_SELECT && parse->commandType != CMD_PLAN_UTILITY && !inheritance_update)
10) I kinda wonder what happens when a function is used in a WHERE
condition, but it depends on a variable and alsu mutates it on each
call ...
11) I think Query->hasSchemaVariable (in analyze.c) should be renamed to
hasSchemaVariables (which reflects the other fields referring to things
like window functions etc.)
12) I find it rather suspicious that we make decisions in utility.c
solely based on commandType (whether it's CMD_UTILITY or not). IMO
it's pretty strange/ugly that T_LetStmt can be both CMD_UTILITY and
CMD_PLAN_UTILITY:
case T_LetStmt:
{
if (pstmt->commandType == CMD_UTILITY)
doLetStmtReset(pstmt);
else
{
Assert(pstmt->commandType == CMD_PLAN_UTILITY);
doLetStmtEval(pstmt, params, queryEnv, queryString);
}
if (completionTag)
strcpy(completionTag, "LET");
}
break;
13) Not sure why we moved DO_TABLE in addBoundaryDependencies
(pg_dump.c), seems unnecessary:
case DO_CONVERSION:
- case DO_TABLE:
+ case DO_VARIABLE:
case DO_ATTRDEF:
+ case DO_TABLE:
case DO_PROCLANG:
14) namespace.c defines VariableIsVisible twice:
extern bool VariableIsVisible(Oid relid);
...
extern bool VariableIsVisible(Oid varid);
15) I'd say lookup_variable and identify_variable should use camelcase
just like the other functions in the same file.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Вложения
12) I find it rather suspicious that we make decisions in utility.c
solely based on commandType (whether it's CMD_UTILITY or not). IMO
it's pretty strange/ugly that T_LetStmt can be both CMD_UTILITY and
CMD_PLAN_UTILITY:
case T_LetStmt:
{
if (pstmt->commandType == CMD_UTILITY)
doLetStmtReset(pstmt);
else
{
Assert(pstmt->commandType == CMD_PLAN_UTILITY);
doLetStmtEval(pstmt, params, queryEnv, queryString);
}
if (completionTag)
strcpy(completionTag, "LET");
}
break;
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Вложения
Вложения
HirebaseRegardsPavel
Вложения
pá 7. 2. 2020 v 17:09 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:HirebaseRegardsPavelHianother rebase, fix \dV statement (for 0001 patch)
RegardsPavel
Вложения
The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: tested, passed Spec compliant: tested, failed Documentation: tested, failed Hello Pavel First thanks for working on this patch cause it might be really helpful for those of us trying to migrate PL code betweenRDBMs. I tried your patch for migrating an Oracle package body to PL/pgSQL after also testing a solution using set_config and current_setting(which works but I'm not really satisfied by this workaround solution). So I compiled latest postgres sources from github on Linux (redhat 7.7) using only your patch number 1 (I did not try thesecond part of the patch). For my use-case it's working great, performances are excellent (compared to other solution for porting "package variables"). I did not test all the features involved by the patch (especially ALTER variable). I have some feedback however : 1) Failure when using pg_dump 13 on a 12.1 database When exporting a 12.1 database using pg_dump from the latest development sources I have an error regarding variables export [pg12@TST-LINUX-PG-03 ~]$ /opt/postgres12/pg12/bin/pg_dump -h localhost -p 5432 -U postgres -f dump_pg12.sql database1 pg_dump: error: query failed: ERROR: relation "pg_variable" does not exist LINE 1: ...og.pg_get_expr(v.vardefexpr,0) as vardefexpr FROM pg_variabl... ^ pg_dump: error: query was: SELECT v.tableoid, v.oid, v.varname, v.vareoxaction, v.varnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = varowner) AS rolname , (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(v.varacl,pg_catalog.acldefault('V',v.varowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('V',v.varowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as varacl, ...: I think that it should have worked anyway cause the documentation states : https://www.postgresql.org/docs/current/upgrading.html "It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantageof enhancements that might have been made in these programs." (that's what I did here) I think there should be a way to avoid dumping the variable if they don't exist, should'nt it ? 2) Displaying the variables + completion I created 2 variables using : CREATE VARIABLE my_pkg.g_dat_deb varchar(11); CREATE VARIABLE my_pkg.g_dat_fin varchar(11); When I try to display them, I can only see them when prefixing by the schema : bdd13=> \dV "Did not find any schema variables." bdd13=> \dV my_pkg.* List of variables Schema | Name | Type | Is nullable | Default | Owner | Transactional end action ------------+----------------+-----------------------+-------------+---------+-------+-------------------------- my_pkg| g_dat_deb | character varying(11) | t | | myowner | my_pkg| g_dat_fin | character varying(11) | t | | myowner | (3 rows) bdd13=> \dV my_pkg Did not find any schema variable named "my_pck". NB : Using this template, functions are returned, maybe variables should also be listed ? (here by querying on "my_pkg%") cts_get13=> \dV my_p [TAB] => completion using [TAB] key is not working Is this normal that I cannot see all the variables when not specifying any schema ? Also the completion works for functions, but not for variable. That's just some bonus but it might be good to have it. I think the way variables are listed using \dV should match with \df for querying functions 3) Any way to define CONSTANTs ? We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like toinsist on it. I think it would be nice to have a way to say that a variable should not be changed once defined. Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open. Otherwise the documentation looks good to me. Regards Rémi
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: tested, passed
Spec compliant: tested, failed
Documentation: tested, failed
Hello Pavel
First thanks for working on this patch cause it might be really helpful for those of us trying to migrate PL code between RDBMs.
I tried your patch for migrating an Oracle package body to PL/pgSQL after also testing a solution using set_config and current_setting (which works but I'm not really satisfied by this workaround solution).
So I compiled latest postgres sources from github on Linux (redhat 7.7) using only your patch number 1 (I did not try the second part of the patch).
For my use-case it's working great, performances are excellent (compared to other solution for porting "package variables").
I did not test all the features involved by the patch (especially ALTER variable).
I have some feedback however :
1) Failure when using pg_dump 13 on a 12.1 database
When exporting a 12.1 database using pg_dump from the latest development sources I have an error regarding variables export
[pg12@TST-LINUX-PG-03 ~]$ /opt/postgres12/pg12/bin/pg_dump -h localhost -p 5432 -U postgres -f dump_pg12.sql database1
pg_dump: error: query failed: ERROR: relation "pg_variable" does not exist
LINE 1: ...og.pg_get_expr(v.vardefexpr,0) as vardefexpr FROM pg_variabl...
^
pg_dump: error: query was: SELECT v.tableoid, v.oid, v.varname, v.vareoxaction, v.varnamespace,
(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = varowner) AS rolname
, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n
FROM pg_catalog.unnest(coalesce(v.varacl,pg_catalog.acldefault('V',v.varowner)))
WITH ORDINALITY AS perm(acl,row_n)
WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('V',v.varowner))) AS init(init_acl)
WHERE acl = init_acl)) as foo) as varacl, ...:
I think that it should have worked anyway cause the documentation states :
https://www.postgresql.org/docs/current/upgrading.html
"It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of enhancements that might have been made in these programs." (that's what I did here)
I think there should be a way to avoid dumping the variable if they don't exist, should'nt it ?
2) Displaying the variables + completion
I created 2 variables using :
CREATE VARIABLE my_pkg.g_dat_deb varchar(11);
CREATE VARIABLE my_pkg.g_dat_fin varchar(11);
When I try to display them, I can only see them when prefixing by the schema :
bdd13=> \dV
"Did not find any schema variables."
bdd13=> \dV my_pkg.*
List of variables
Schema | Name | Type | Is nullable | Default | Owner | Transactional end action
------------+----------------+-----------------------+-------------+---------+-------+--------------------------
my_pkg| g_dat_deb | character varying(11) | t | | myowner |
my_pkg| g_dat_fin | character varying(11) | t | | myowner |
(3 rows)
bdd13=> \dV my_pkg
Did not find any schema variable named "my_pck".
NB : Using this template, functions are returned, maybe variables should also be listed ? (here by querying on "my_pkg%")
cts_get13=> \dV my_p [TAB]
=> completion using [TAB] key is not working
Is this normal that I cannot see all the variables when not specifying any schema ?
Also the completion works for functions, but not for variable.
That's just some bonus but it might be good to have it.
I think the way variables are listed using \dV should match with \df for querying functions
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
Otherwise the documentation looks good to me.
Regards
Rémi
Вложения
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
Hello Pavel.
That looks pretty good to me !
I’m adding Philippe Beaudoin who was also interested in this topic.
Recap : We were looking for a way to separate variable from constants in the “Schema Variables” proposition from Pavel.
Pavel was saying that there are some limitations regarding the keywords we can use, as the community don’t want to introduce too much new keywords in Postgres SQL (PL/pgSQL is a different list of keywords).
“CONSTANT” is not a keyword in SQL for Now (though it is one in PL/pgSQL).
Pavel’s syntax allow to use it as a keyword in the “WITH OPTIONS” clause that is already supported.
… I think it’s a good idea.
The list of keywords is defined in : postgresql\src\include\parser\kwlist.h
Pavel, I saw that in DB2, those variables are called “Global Variables”, is it something we can consider changing, or do you prefer to keep using the “Schema Variable” name ?
De : Pavel Stehule [mailto:pavel.stehule@gmail.com]
Envoyé : jeudi 27 février 2020 15:38
À : DUVAL REMI <REMI.DUVAL@CHEOPS.FR>
Cc : PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Objet : Re: proposal: schema variables
Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.
last variant, but maybe best is using keyword WITH
So the syntax can looks like
CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]
What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.
CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);
?
Regards
Pavel
Hello Pavel.
That looks pretty good to me !
I’m adding Philippe Beaudoin who was also interested in this topic.
Recap : We were looking for a way to separate variable from constants in the “Schema Variables” proposition from Pavel.
Pavel was saying that there are some limitations regarding the keywords we can use, as the community don’t want to introduce too much new keywords in Postgres SQL (PL/pgSQL is a different list of keywords).
“CONSTANT” is not a keyword in SQL for Now (though it is one in PL/pgSQL).
Pavel’s syntax allow to use it as a keyword in the “WITH OPTIONS” clause that is already supported.
… I think it’s a good idea.
The list of keywords is defined in : postgresql\src\include\parser\kwlist.h
Pavel, I saw that in DB2, those variables are called “Global Variables”, is it something we can consider changing, or do you prefer to keep using the “Schema Variable” name ?
De : Pavel Stehule [mailto:pavel.stehule@gmail.com]
Envoyé : jeudi 27 février 2020 15:38
À : DUVAL REMI <REMI.DUVAL@CHEOPS.FR>
Cc : PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Objet : Re: proposal: schema variables
Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.
last variant, but maybe best is using keyword WITH
So the syntax can looks like
CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]
What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.
CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);
?
Regards
Pavel
Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.last variant, but maybe best is using keyword WITHSo the syntax can looks likeCREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);
?RegardsPavel
Вложения
čt 27. 2. 2020 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.last variant, but maybe best is using keyword WITHSo the syntax can looks likeCREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);After some more thinking and because in other patch I support syntax CREATE TRANSACTION VARIABLE ... I change my opinion and implemented support forsyntax CREATE IMMUTABLE VARIABLE for define constants.
See attached patchRegardsPavel?RegardsPavel
Вложения
pá 28. 2. 2020 v 16:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:čt 27. 2. 2020 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.last variant, but maybe best is using keyword WITHSo the syntax can looks likeCREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);After some more thinking and because in other patch I support syntax CREATE TRANSACTION VARIABLE ... I change my opinion and implemented support forsyntax CREATE IMMUTABLE VARIABLE for define constants.second try to fix pg_dumpRegardsPavelSee attached patchRegardsPavel?RegardsPavel
llvmjit_expr.c: In function ‘llvm_compile_expr’:llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]build_EvalXFunc(b, mod, "ExecEvalParamVariable",^llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: passing argument 5 of ‘build_EvalXFuncInt’ from incompatible pointer type [enabled by default]llvmjit_expr.c:60:21: note: expected ‘struct ExprEvalStep *’ but argument is of type ‘LLVMValueRef’static LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod,^llvmjit_expr.c:1092:29: error: ‘i’ undeclared (first use in this function)LLVMBuildBr(b, opblocks[i + 1]);^llvmjit_expr.c:1092:29: note: each undeclared identifier is reported only once for each function it appears inmake[2]: *** [llvmjit_expr.o] Error 1
postgres=# create or replace function foofunc() returns timestamp as $$ begin return now(); end; $$ language plpgsql;CREATE FUNCTIONpostgres=# create schema test;CREATE SCHEMApostgres=# create variable test.v1 as timestamp default foofunc();CREATE VARIABLEpostgres=# drop function foofunc();DROP FUNCTIONpostgres=# select test.v1;ERROR: cache lookup failed for function 16437
postgres=# create variable test.v2 as timestamp default now();CREATE VARIABLEpostgres=# select now();now-------------------------------2020-03-05 12:13:29.775373+00(1 row)postgres=# select test.v2;v2----------------------------2020-03-05 12:13:37.192317 -- I was expecting this to be earlier than the above timestamp.(1 row)postgres=# select test.v2;v2----------------------------2020-03-05 12:13:37.192317(1 row)postgres=# let test.v2 = default;LETpostgres=# select test.v2;v2----------------------------2020-03-05 12:14:07.538615(1 row)
Вложения
On Sat, Feb 29, 2020 at 2:10 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:pá 28. 2. 2020 v 16:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:čt 27. 2. 2020 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.last variant, but maybe best is using keyword WITHSo the syntax can looks likeCREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);After some more thinking and because in other patch I support syntax CREATE TRANSACTION VARIABLE ... I change my opinion and implemented support forsyntax CREATE IMMUTABLE VARIABLE for define constants.second try to fix pg_dumpRegardsPavelSee attached patchRegardsPavel?RegardsPavelHi Pavel,I have been reviewing the latest patch (schema-variables-20200229.patch.gz)and here are few comments:1- There is a compilation error, when compiled with --with-llvm enabled onCentOS 7.llvmjit_expr.c: In function ‘llvm_compile_expr’:llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]build_EvalXFunc(b, mod, "ExecEvalParamVariable",^llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]llvmjit_expr.c:1090:5: warning: passing argument 5 of ‘build_EvalXFuncInt’ from incompatible pointer type [enabled by default]llvmjit_expr.c:60:21: note: expected ‘struct ExprEvalStep *’ but argument is of type ‘LLVMValueRef’static LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod,^llvmjit_expr.c:1092:29: error: ‘i’ undeclared (first use in this function)LLVMBuildBr(b, opblocks[i + 1]);^llvmjit_expr.c:1092:29: note: each undeclared identifier is reported only once for each function it appears inmake[2]: *** [llvmjit_expr.o] Error 1After looking into it, it turns out that:- parameter order was incorrect in build_EvalXFunc()- LLVMBuildBr() is using the undeclared variable 'i' whereas it should beusing 'opno'.2- Similarly, If the default expression is referencing a function or object,dependency should be marked, so if the function is not dropped silently.otherwise, a cache lookup error will come.postgres=# create or replace function foofunc() returns timestamp as $$ begin return now(); end; $$ language plpgsql;CREATE FUNCTIONpostgres=# create schema test;CREATE SCHEMApostgres=# create variable test.v1 as timestamp default foofunc();CREATE VARIABLEpostgres=# drop function foofunc();DROP FUNCTIONpostgres=# select test.v1;ERROR: cache lookup failed for function 16437
3- Variable DEFAULT expression is apparently being evaluated at the time offirst access. whereas I think that It should be at the time of variablecreation. consider the following example:postgres=# create variable test.v2 as timestamp default now();CREATE VARIABLEpostgres=# select now();now-------------------------------2020-03-05 12:13:29.775373+00(1 row)postgres=# select test.v2;v2----------------------------2020-03-05 12:13:37.192317 -- I was expecting this to be earlier than the above timestamp.(1 row)postgres=# select test.v2;v2----------------------------2020-03-05 12:13:37.192317(1 row)postgres=# let test.v2 = default;LETpostgres=# select test.v2;v2----------------------------2020-03-05 12:14:07.538615(1 row)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare x timestamp default current_timestamp;
begin
raise notice '%', x;
end;
$function$
NOTICE: 2020-03-05 18:49:12.465054
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
postgres=# select foo();
NOTICE: 2020-03-05 18:49:13.255197
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
To continue my testing of the patch I made few fixes for the above-mentionedcomments. The patch for those changes is attached if it could be of any use.--Asif Rehman
Вложения
Hello Pavel
I tested your patch again and I think things are better now, close to perfect for me.
1) Patch review
- We can define CONSTANTs with CREATE IMMUTABLE VARIABLE … I’m really pleased with this
- The previous bug I mentioned to you by private mail (see detail below) has been fixed and a non-regression test case has been added for it.
- I’m now able to export a 12.1 database using pg_dump from the latest git HEAD (internal version 130000).
NB: the condition is “if internal_version < 130000 => don’t try to export any schema variable”.
Also I was able to test a use case for a complex Oracle package I migrated to PostgreSQL (it has a total of 194 variables and constants in it !).
The Oracle package has been migrated to a PostgreSQL schema with one routine per Oracle subprogram.
I’m able to run my business test case using schema variables on those routines and it’s giving me the expected result.
NB: Previous bug was
database1=> CREATE VARIABLE T0_var AS char(14);
CREATE VARIABLE
database1=> CREATE IMMUTABLE VARIABLE Taille_var AS numeric DEFAULT 14;
CREATE VARIABLE
database1=> LET T0_var = LPAD('999', trunc(Taille_var::NUMERIC)::INTEGER, '0');
ERROR: schema variable "taille_var" is declared IMMUTABLE
database1=> LET T0_var = LPAD('999', trunc(Taille_var::NUMERIC)::INTEGER, '0');
ERROR: variable "taille_var" has not valid content
ð It’s now fixed !
2) Regarding documentation
It’s pretty good except some small details :
- sql-createvariable.html => IMMUTABLE parameter : The value of the variable cannot be changed. => I think an article is needed here (the)
- sql-createvariable.html => ON COMMIT DROP :
The ON COMMIT DROP
clause specifies the bahaviour of temporary => behaviour
Also there are “tabs” between words (here between “of” and “temporary”), making the paragraph look strange.
- sql-createvariable.html => Maybe we should mention that the IMMUTABLE parameter (CREATE IMMUTABLE VARIABLE …) is the way to define global CONSTANTs, because people will look for a way to create global constants in the documentation and it would be good if they can find the CONSTANT word in it.
Also maybe it’s worth mentioning that “schema variables” relates to “global variables” (for the same purpose of people searching in the documentation).
- sql-altervariable.html => sentence “These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the variable.“ => not sure I understand this one. Isn’t it “does not do anything you could do” instead of “you couln’t do” .. but maybe it’s me
Otherwise, this is a really nice feature and I’m looking forward to have it in PostgreSQL.
Thanks a lot
Duval Rémi
De : Pavel Stehule [mailto:pavel.stehule@gmail.com]
Envoyé : jeudi 5 mars 2020 18:54
À : Asif Rehman <asifr.rehman@gmail.com>
Cc : DUVAL REMI <REMI.DUVAL@CHEOPS.FR>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Objet : Re: proposal: schema variables
čt 5. 3. 2020 v 15:11 odesílatel Asif Rehman <asifr.rehman@gmail.com> napsal:
On Sat, Feb 29, 2020 at 2:10 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
pá 28. 2. 2020 v 16:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
čt 27. 2. 2020 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.
last variant, but maybe best is using keyword WITH
So the syntax can looks like
CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]
What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.
CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);
After some more thinking and because in other patch I support syntax CREATE TRANSACTION VARIABLE ... I change my opinion and implemented support for
syntax CREATE IMMUTABLE VARIABLE for define constants.
second try to fix pg_dump
Regards
Pavel
See attached patch
Regards
Pavel
?
Regards
Pavel
Hi Pavel,
I have been reviewing the latest patch (schema-variables-20200229.patch.gz)
and here are few comments:
1- There is a compilation error, when compiled with --with-llvm enabled on
CentOS 7.
llvmjit_expr.c: In function ‘llvm_compile_expr’:
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
build_EvalXFunc(b, mod, "ExecEvalParamVariable",
^
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: passing argument 5 of ‘build_EvalXFuncInt’ from incompatible pointer type [enabled by default]
llvmjit_expr.c:60:21: note: expected ‘struct ExprEvalStep *’ but argument is of type ‘LLVMValueRef’
static LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod,
^
llvmjit_expr.c:1092:29: error: ‘i’ undeclared (first use in this function)
LLVMBuildBr(b, opblocks[i + 1]);
^
llvmjit_expr.c:1092:29: note: each undeclared identifier is reported only once for each function it appears in
make[2]: *** [llvmjit_expr.o] Error 1
After looking into it, it turns out that:
- parameter order was incorrect in build_EvalXFunc()
- LLVMBuildBr() is using the undeclared variable 'i' whereas it should be
using 'opno'.
2- Similarly, If the default expression is referencing a function or object,
dependency should be marked, so if the function is not dropped silently.
otherwise, a cache lookup error will come.
postgres=# create or replace function foofunc() returns timestamp as $$ begin return now(); end; $$ language plpgsql;
CREATE FUNCTION
postgres=# create schema test;
CREATE SCHEMA
postgres=# create variable test.v1 as timestamp default foofunc();
CREATE VARIABLE
postgres=# drop function foofunc();
DROP FUNCTION
postgres=# select test.v1;
ERROR: cache lookup failed for function 16437
Thank you for this analyze and patches. I merged them to attached patch
3- Variable DEFAULT expression is apparently being evaluated at the time of
first access. whereas I think that It should be at the time of variable
creation. consider the following example:
postgres=# create variable test.v2 as timestamp default now();
CREATE VARIABLE
postgres=# select now();
now
-------------------------------
2020-03-05 12:13:29.775373+00
(1 row)
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:13:37.192317 -- I was expecting this to be earlier than the above timestamp.
(1 row)
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:13:37.192317
(1 row)
postgres=# let test.v2 = default;
LET
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:14:07.538615
(1 row)
This is expected and wanted - same behave has plpgsql variables.
CREATE OR REPLACE FUNCTION public.foo()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare x timestamp default current_timestamp;
begin
raise notice '%', x;
end;
$function$
postgres=# select foo();
NOTICE: 2020-03-05 18:49:12.465054
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
postgres=# select foo();
NOTICE: 2020-03-05 18:49:13.255197
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
You can use
CREATE VARIABLE cuser AS text DEFAULT session_user;
Has not any sense to use a value from creating time
And a analogy with CREATE TABLE
CREATE TABLE fooo(a timestamp DEFAULT current_timestamp) -- there is not a create time timestamp
I fixed buggy behave of IMMUTABLE variables
Regards
Pavel
To continue my testing of the patch I made few fixes for the above-mentioned
comments. The patch for those changes is attached if it could be of any use.
--
Asif Rehman
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
Hello Pavel
I tested your patch again and I think things are better now, close to perfect for me.
1) Patch review
- We can define CONSTANTs with CREATE IMMUTABLE VARIABLE … I’m really pleased with this
- The previous bug I mentioned to you by private mail (see detail below) has been fixed and a non-regression test case has been added for it.
- I’m now able to export a 12.1 database using pg_dump from the latest git HEAD (internal version 130000).
NB: the condition is “if internal_version < 130000 => don’t try to export any schema variable”.
Also I was able to test a use case for a complex Oracle package I migrated to PostgreSQL (it has a total of 194 variables and constants in it !).
The Oracle package has been migrated to a PostgreSQL schema with one routine per Oracle subprogram.
I’m able to run my business test case using schema variables on those routines and it’s giving me the expected result.
NB: Previous bug was
database1=> CREATE VARIABLE T0_var AS char(14);
CREATE VARIABLE
database1=> CREATE IMMUTABLE VARIABLE Taille_var AS numeric DEFAULT 14;
CREATE VARIABLE
database1=> LET T0_var = LPAD('999', trunc(Taille_var::NUMERIC)::INTEGER, '0');
ERROR: schema variable "taille_var" is declared IMMUTABLE
database1=> LET T0_var = LPAD('999', trunc(Taille_var::NUMERIC)::INTEGER, '0');
ERROR: variable "taille_var" has not valid contentð It’s now fixed !
2) Regarding documentation
It’s pretty good except some small details :
- sql-createvariable.html => IMMUTABLE parameter : The value of the variable cannot be changed. => I think an article is needed here (the)
- sql-createvariable.html => ON COMMIT DROP :
The
ON COMMIT DROP
clause specifies the bahaviour of temporary => behaviour
Also there are “tabs” between words (here between “of” and “temporary”), making the paragraph look strange.
- sql-createvariable.html => Maybe we should mention that the IMMUTABLE parameter (CREATE IMMUTABLE VARIABLE …) is the way to define global CONSTANTs, because people will look for a way to create global constants in the documentation and it would be good if they can find the CONSTANT word in it.
Also maybe it’s worth mentioning that “schema variables” relates to “global variables” (for the same purpose of people searching in the documentation).
- sql-altervariable.html => sentence “These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the variable.“ => not sure I understand this one. Isn’t it “does not do anything you could do” instead of “you couln’t do” .. but maybe it’s me
owning role, and that role must have <literal>CREATE</literal> privilege on
the view's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the view.
However, a superuser can alter ownership of any view anyway.)
Otherwise, this is a really nice feature and I’m looking forward to have it in PostgreSQL.
Thanks a lot
Duval Rémi
De : Pavel Stehule [mailto:pavel.stehule@gmail.com]
Envoyé : jeudi 5 mars 2020 18:54
À : Asif Rehman <asifr.rehman@gmail.com>
Cc : DUVAL REMI <REMI.DUVAL@CHEOPS.FR>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Objet : Re: proposal: schema variables
čt 5. 3. 2020 v 15:11 odesílatel Asif Rehman <asifr.rehman@gmail.com> napsal:
On Sat, Feb 29, 2020 at 2:10 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
pá 28. 2. 2020 v 16:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
čt 27. 2. 2020 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi
3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduces it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be changed once defined.
Maybe it's hard to implement and can be implemented later, but I just want to know if this concern is open.
I played little bit with it and I didn't find any nice solution, but maybe I found the solution. I had ideas about some variants, but almost all time I had a problem with parser's shifts because all potential keywords are not reserved.
last variant, but maybe best is using keyword WITH
So the syntax can looks like
CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ]
What do you think about this syntax? It doesn't need any new keyword, and it easy to enhance it.
CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);
After some more thinking and because in other patch I support syntax CREATE TRANSACTION VARIABLE ... I change my opinion and implemented support for
syntax CREATE IMMUTABLE VARIABLE for define constants.
second try to fix pg_dump
Regards
Pavel
See attached patch
Regards
Pavel
?
Regards
Pavel
Hi Pavel,
I have been reviewing the latest patch (schema-variables-20200229.patch.gz)
and here are few comments:
1- There is a compilation error, when compiled with --with-llvm enabled on
CentOS 7.
llvmjit_expr.c: In function ‘llvm_compile_expr’:
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
build_EvalXFunc(b, mod, "ExecEvalParamVariable",
^
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default]
llvmjit_expr.c:1090:5: warning: (near initialization for ‘(anonymous)[0]’) [enabled by default]
llvmjit_expr.c:1090:5: warning: passing argument 5 of ‘build_EvalXFuncInt’ from incompatible pointer type [enabled by default]
llvmjit_expr.c:60:21: note: expected ‘struct ExprEvalStep *’ but argument is of type ‘LLVMValueRef’
static LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod,
^
llvmjit_expr.c:1092:29: error: ‘i’ undeclared (first use in this function)
LLVMBuildBr(b, opblocks[i + 1]);
^
llvmjit_expr.c:1092:29: note: each undeclared identifier is reported only once for each function it appears in
make[2]: *** [llvmjit_expr.o] Error 1
After looking into it, it turns out that:
- parameter order was incorrect in build_EvalXFunc()
- LLVMBuildBr() is using the undeclared variable 'i' whereas it should be
using 'opno'.
2- Similarly, If the default expression is referencing a function or object,
dependency should be marked, so if the function is not dropped silently.
otherwise, a cache lookup error will come.
postgres=# create or replace function foofunc() returns timestamp as $$ begin return now(); end; $$ language plpgsql;
CREATE FUNCTION
postgres=# create schema test;
CREATE SCHEMA
postgres=# create variable test.v1 as timestamp default foofunc();
CREATE VARIABLE
postgres=# drop function foofunc();
DROP FUNCTION
postgres=# select test.v1;
ERROR: cache lookup failed for function 16437
Thank you for this analyze and patches. I merged them to attached patch
3- Variable DEFAULT expression is apparently being evaluated at the time of
first access. whereas I think that It should be at the time of variable
creation. consider the following example:
postgres=# create variable test.v2 as timestamp default now();
CREATE VARIABLE
postgres=# select now();
now
-------------------------------
2020-03-05 12:13:29.775373+00
(1 row)
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:13:37.192317 -- I was expecting this to be earlier than the above timestamp.
(1 row)
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:13:37.192317
(1 row)
postgres=# let test.v2 = default;
LET
postgres=# select test.v2;
v2
----------------------------
2020-03-05 12:14:07.538615
(1 row)
This is expected and wanted - same behave has plpgsql variables.
CREATE OR REPLACE FUNCTION public.foo()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare x timestamp default current_timestamp;
begin
raise notice '%', x;
end;
$function$
postgres=# select foo();
NOTICE: 2020-03-05 18:49:12.465054
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
postgres=# select foo();
NOTICE: 2020-03-05 18:49:13.255197
┌─────┐
│ foo │
╞═════╡
│ │
└─────┘
(1 row)
You can use
CREATE VARIABLE cuser AS text DEFAULT session_user;
Has not any sense to use a value from creating time
And a analogy with CREATE TABLE
CREATE TABLE fooo(a timestamp DEFAULT current_timestamp) -- there is not a create time timestamp
I fixed buggy behave of IMMUTABLE variables
Regards
Pavel
To continue my testing of the patch I made few fixes for the above-mentioned
comments. The patch for those changes is attached if it could be of any use.
--
Asif Rehman
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
Вложения
Вложения
HiI fixed the some ugly parts of this patch - now the LET x = DEFAULT, LET x = NULL is processed by more usual way.Statement LET is doesn't switch between STMT_UTILITY and STMT_PLAN_UTILITY like before. It is only STMT_PLAN_UTILITY statement.
RegardsPavel
Вложения
so 7. 3. 2020 v 22:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:HiI fixed the some ugly parts of this patch - now the LET x = DEFAULT, LET x = NULL is processed by more usual way.Statement LET is doesn't switch between STMT_UTILITY and STMT_PLAN_UTILITY like before. It is only STMT_PLAN_UTILITY statement.I did some cleaning - I mainly replaced CMD_PLAN_UTILITY by CMD_LET because there is not another similar statement in queue.
RegardsPavelRegardsPavel
Вложения
Вложения
Hello
I played around with the ALTER VARIABLE statement to make sure it’s OK and it seems fine to me.
Another last thing before commiting.
I agree with Thomas Vondra, that this part might/should be simplified :
[ { ON COMMIT DROP | ON { TRANSACTIONAL | TRANSACTION } END RESET } ]
I would only allow “ON TRANSACTION END RESET”.
I think we don’t need both here.
Philippe Beaudoin was indeed talking about the TRANSACTIONAL keyword, but that would have make sense (and I think that’s what he meant) , if you could do something like “CREATE [NON] TRANSACTIONAL VARIABLE …”.
But here I don’t think that the ON TRANSACTIONAL END RESET has any sense in English, and it only complicates the syntax.
Maybe Thomas Vondra (if it’s him) would be more inclined to commit the patch if it has this more simple syntax has he requested.
What do you think ?
I hope it would be the last touch, making it fully ready for a commiter.
Вложения
čt 19. 3. 2020 v 10:43 odesílatel DUVAL REMI <REMI.DUVAL@cheops.fr> napsal:Hello
I played around with the ALTER VARIABLE statement to make sure it’s OK and it seems fine to me.
Another last thing before commiting.
I agree with Thomas Vondra, that this part might/should be simplified :
[ { ON COMMIT DROP | ON { TRANSACTIONAL | TRANSACTION } END RESET } ]
I would only allow “ON TRANSACTION END RESET”.
I think we don’t need both here.
Philippe Beaudoin was indeed talking about the TRANSACTIONAL keyword, but that would have make sense (and I think that’s what he meant) , if you could do something like “CREATE [NON] TRANSACTIONAL VARIABLE …”.
But here I don’t think that the ON TRANSACTIONAL END RESET has any sense in English, and it only complicates the syntax.
Maybe Thomas Vondra (if it’s him) would be more inclined to commit the patch if it has this more simple syntax has he requested.
What do you think ?
I removed TRANSACTIONAL from this clause, see attachement change.diffUpdated patch attached.I hope it would be the last touch, making it fully ready for a commiter.
Thank you very much for review and testing
Pavel
Вложения
HirebaseRegardsPavel
Вложения
On Thu, May 21, 2020 at 3:41 PM Pavel Stehule <pavel.stehule@gmail.com> wrote: > > Hi > > just rebase without any other changes > You seem to forget attaching the rebased patch. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On Thu, May 21, 2020 at 3:41 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> just rebase without any other changes
>
You seem to forget attaching the rebased patch.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Вложения
čt 21. 5. 2020 v 13:34 odesílatel Amit Kapila <amit.kapila16@gmail.com> napsal:On Thu, May 21, 2020 at 3:41 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> just rebase without any other changes
>
You seem to forget attaching the rebased patch.I am sorryattached.
Вложения
čt 21. 5. 2020 v 14:49 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:čt 21. 5. 2020 v 13:34 odesílatel Amit Kapila <amit.kapila16@gmail.com> napsal:On Thu, May 21, 2020 at 3:41 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> just rebase without any other changes
>
You seem to forget attaching the rebased patch.I am sorryattached.fresh rebase
Вложения
ne 5. 7. 2020 v 15:33 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:čt 21. 5. 2020 v 14:49 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:čt 21. 5. 2020 v 13:34 odesílatel Amit Kapila <amit.kapila16@gmail.com> napsal:On Thu, May 21, 2020 at 3:41 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> just rebase without any other changes
>
You seem to forget attaching the rebased patch.I am sorryattached.fresh rebasefix test build
RegardsPavel
Вложения
On Sat, Jul 11, 2020 at 06:44:24AM +0200, Pavel Stehule wrote: > rebase Per the CF bot, this needs an extra rebase as it does not apply anymore. This has not attracted much the attention of committers as well. -- Michael
Вложения
On Sat, Jul 11, 2020 at 06:44:24AM +0200, Pavel Stehule wrote:
> rebase
Per the CF bot, this needs an extra rebase as it does not apply
anymore. This has not attracted much the attention of committers as
well.
--
Michael
čt 24. 9. 2020 v 5:56 odesílatel Michael Paquier <michael@paquier.xyz> napsal:On Sat, Jul 11, 2020 at 06:44:24AM +0200, Pavel Stehule wrote:
> rebase
Per the CF bot, this needs an extra rebase as it does not apply
anymore. This has not attracted much the attention of committers as
well.I'll fix it today
--
Michael
Вложения
On Thu, Sep 24, 2020 at 08:49:50PM +0200, Pavel Stehule wrote: > fixed patch attached It looks like there are again conflicts within setrefs.c. -- Michael
Вложения
On Thu, Sep 24, 2020 at 08:49:50PM +0200, Pavel Stehule wrote:
> fixed patch attached
It looks like there are again conflicts within setrefs.c.
--
Michael
Вложения
čt 1. 10. 2020 v 5:38 odesílatel Michael Paquier <michael@paquier.xyz> napsal:On Thu, Sep 24, 2020 at 08:49:50PM +0200, Pavel Stehule wrote:
> fixed patch attached
It looks like there are again conflicts within setrefs.c.fresh patch
RegardsPavel--
Michael
Вложения
Hionly rebase
RegardsPavel
Вложения
On 2020-12-26 05:52, Pavel Stehule wrote: > so 19. 12. 2020 v 7:57 odesílatel Pavel Stehule > <pavel.stehule@gmail.com> > napsal: > [schema-variables-20201222.patch.gz (~] > >> Hi >> >> only rebase >> > > rebase and comments fixes > Hi Pavel, This file is the exact same as the file you sent Tuesday. Is it a mistake?
On 2020-12-26 05:52, Pavel Stehule wrote:
> so 19. 12. 2020 v 7:57 odesílatel Pavel Stehule
> <pavel.stehule@gmail.com>
> napsal:
> [schema-variables-20201222.patch.gz (~]
>
>> Hi
>>
>> only rebase
>>
>
> rebase and comments fixes
>
Hi Pavel,
This file is the exact same as the file you sent Tuesday. Is it a
mistake?
On 2021-01-08 07:20, Pavel Stehule wrote: > Hi > > just rebase > > [schema-variables-20200108.patch] Hey Pavel, My gcc 8.3.0 compile says: (on debian 10/Buster) utility.c: In function ‘CreateCommandTag’: utility.c:2332:8: warning: this statement may fall through [-Wimplicit-fallthrough=] tag = CMDTAG_SELECT; ~~~~^~~~~~~~~~~~~~~ utility.c:2334:3: note: here case T_LetStmt: ^~~~ compile, check, check-world, runs without further problem. I also changed a few typos/improvements in the documentation, see attached. One thing I wasn't sure of: I have assumed that ON TRANSACTIONAL END RESET should be ON TRANSACTION END RESET and changed it accordingly, please double-check. Erik Rijkers
Вложения
On 2021-01-08 07:20, Pavel Stehule wrote:
> Hi
>
> just rebase
>
> [schema-variables-20200108.patch]
Hey Pavel,
My gcc 8.3.0 compile says:
(on debian 10/Buster)
utility.c: In function ‘CreateCommandTag’:
utility.c:2332:8: warning: this statement may fall through
[-Wimplicit-fallthrough=]
tag = CMDTAG_SELECT;
~~~~^~~~~~~~~~~~~~~
utility.c:2334:3: note: here
case T_LetStmt:
^~~~
compile, check, check-world, runs without further problem.
I also changed a few typos/improvements in the documentation, see
attached.
One thing I wasn't sure of: I have assumed that
ON TRANSACTIONAL END RESET
should be
ON TRANSACTION END RESET
and changed it accordingly, please double-check.
Erik Rijkers
Вложения
On 2021-01-14 07:35, Pavel Stehule wrote: > [schema-variables-20210114.patch.gz] Build is fine. My (small) list of tests run OK. I did notice a few more documentation peculiarities: 'The PostgreSQL has schema variables' should be 'PostgreSQL has schema variables' A link to the LET command should be added to the 'See Also' of the CREATE VARIABLE, ALTER VARIABLE, and DROP VARIABLE pages. (After all, the LET command is the most interesting) Similarly, an ALTER VARIABLE link should be added to the 'See Also' section of LET. Somehow, the sgml in the doc files causes too large spacing in the html, example: I copy from the LET html: 'if that is defined. If no explicit' (6 spaces between 'defined.' and 'If') Can you have a look? Sorry - I can't find the cause. It occurs on a few more places in the newly added sgml/html. The unwanted spaces are visible also in the pdf. (firefox 78.6.1, debian) Thanks, Erik Rijkers
I did some testing locally. All runs smoothly, compiled without warning. Later on (once merged) it would be nice to write down a documentation page for the whole feature as a set next to documented individual commands. It took me a few moments to understand how this works. I was looking how to create variable with non default value in one command, but if I understand it correctly, that's not the purpose. Variable lives in a schema with default value, but you can set it per session via LET. Thus "CREATE VARIABLE" statement should not be usually part of "classic" queries, but it should be threatened more like TABLE or other DDL statements. On the other side LET is there to use the variable in "classic" queries. Does that make sense? Feel free to ping me if any help with documentation would be needed. I can try to prepare an initial variables guide once I'll ensure I do understand this feature well. PS: Now it is clear to me why it is called "schema variables", not "session variables". čt 14. 1. 2021 v 7:36 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal: > > Hi > > rebase > > Regards > > Pavel >
On 2021-01-14 07:35, Pavel Stehule wrote:
> [schema-variables-20210114.patch.gz]
Build is fine. My (small) list of tests run OK.
I did notice a few more documentation peculiarities:
'The PostgreSQL has schema variables' should be
'PostgreSQL has schema variables'
A link to the LET command should be added to the 'See Also' of the
CREATE VARIABLE, ALTER VARIABLE, and DROP VARIABLE pages. (After all,
the LET command is the most interesting)
Similarly, an ALTER VARIABLE link should be added to the 'See Also'
section of LET.
Somehow, the sgml in the doc files causes too large spacing in the html,
example:
I copy from the LET html:
'if that is defined. If no explicit'
(6 spaces between 'defined.' and 'If')
Can you have a look? Sorry - I can't find the cause. It occurs on a
few more places in the newly added sgml/html. The unwanted spaces are
visible also in the pdf.
(firefox 78.6.1, debian)
Thanks,
Erik Rijkers
I did some testing locally. All runs smoothly, compiled without warning.
Later on (once merged) it would be nice to write down a documentation
page for the whole feature as a set next to documented individual
commands.
It took me a few moments to understand how this works.
I was looking how to create variable with non default value in one
command, but if I understand it correctly, that's not the purpose.
Variable lives in a schema with default value, but you can set it per
session via LET.
Thus "CREATE VARIABLE" statement should not be usually part of
"classic" queries, but it should be threatened more like TABLE or
other DDL statements.
On the other side LET is there to use the variable in "classic" queries.
Does that make sense? Feel free to ping me if any help with
documentation would be needed. I can try to prepare an initial
variables guide once I'll ensure I do understand this feature well.
PS: Now it is clear to me why it is called "schema variables", not
"session variables".
čt 14. 1. 2021 v 7:36 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
>
> Hi
>
> rebase
>
> Regards
>
> Pavel
>
čt 14. 1. 2021 v 10:24 odesílatel Erik Rijkers <er@xs4all.nl> napsal:On 2021-01-14 07:35, Pavel Stehule wrote:
> [schema-variables-20210114.patch.gz]
Build is fine. My (small) list of tests run OK.
I did notice a few more documentation peculiarities:
'The PostgreSQL has schema variables' should be
'PostgreSQL has schema variables'fixed
A link to the LET command should be added to the 'See Also' of the
CREATE VARIABLE, ALTER VARIABLE, and DROP VARIABLE pages. (After all,
the LET command is the most interesting)
Similarly, an ALTER VARIABLE link should be added to the 'See Also'
section of LET.fixed
Somehow, the sgml in the doc files causes too large spacing in the html,
example:
I copy from the LET html:
'if that is defined. If no explicit'
(6 spaces between 'defined.' and 'If')
Can you have a look? Sorry - I can't find the cause. It occurs on a
few more places in the newly added sgml/html. The unwanted spaces are
visible also in the pdf.Should be fixed in the last version. Probably there were some problems with invisible white chars.Thank you for checkPavel(firefox 78.6.1, debian)
Thanks,
Erik Rijkers
Вложения
On 2021-01-18 10:59, Pavel Stehule wrote: >> > and here is the patch > [schema-variables-20200118.patch.gz ] One small thing: The drop variable synopsis is: DROP VARIABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] In that text following it, 'RESTRICT' is not documented. When used it does not give an error but I don't see how it 'works'. Erik
On 2021-01-18 10:59, Pavel Stehule wrote:
>>
> and here is the patch
> [schema-variables-20200118.patch.gz ]
One small thing:
The drop variable synopsis is:
DROP VARIABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
In that text following it, 'RESTRICT' is not documented. When used it
does not give an error but I don't see how it 'works'.
Erik
Вложения
Hirebase and set PK for pg_variable table
RegardsPavel
Вложения
> On 2021.03.13. 07:01 Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hi
> fresh rebase
> [schema-variables-20210313.patch.gz]
Hi Pavel,
I notice that the phrase 'schema variable' is not in the index at the end ('bookindex.html'). Not good.
It is also not in the index at the front of the manual - also not good.
Maybe these two (front and back index) can be added?
- schema variable
- altering, ALTER VARIABLE
- changing, LET
- defining, CREATE VARIABLE
- description, Description
- removing, DROP VARIABLE
- changing, LET
- altering, ALTER VARIABLE
If a user searches the pdf, the first occurrence he finds is at:
43.13.2.4. Global variables and constants
(in itself that occurrence/mention is all right, but is should not be the first find, I think)
(I think there was in earlier versions of the patch an entry in the 'contents', i.e., at the front of the manual). I think it would be good to have it in the front-index, pointing to either LET or CREATE VARIABLE, or maybe even to a small introductory paragraph somewhere else (again, I seem to remember that there was one in an earlier patch version).
Of the new commands that this patch brings, 'LET' is the most immediately illuminating for a user (even when a CREATE VARIABLE has to be done first. There is an entry 'LET' in the index (good), but it would be better if that with LET-entry too the phrase 'schema variable' occurred. (I don't know if that's possible)
Then, in the CREATE VARIABLE paragraphs it says
'Changing a schema variable is non-transactional by default.'
I think that, unless there exists a mode where schema vars can be made transactional, 'by default' should be deleted (and there is no such 'transactional mode' for schema variables, is there?). The 'Description' also has such a 'By default' which is better removed for the same reason.
In the CREATE VARIABLE page the example is:
CREATE VARIABLE var1 AS integer;
SELECT var1;
I suggest to make that
CREATE VARIABLE var1 AS date;
LET var1 = (select current_date);
SELECT var1;
So that the example immediately shows an application of functionality.
Thanks,
Erik Rijkers
>
> Pavel
Вложения
Hist 17. 3. 2021 v 13:05 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
> On 2021.03.13. 07:01 Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hi
> fresh rebase
> [schema-variables-20210313.patch.gz]
Hi Pavel,
I notice that the phrase 'schema variable' is not in the index at the end ('bookindex.html'). Not good.
It is also not in the index at the front of the manual - also not good.
Maybe these two (front and back index) can be added?I inserted new indexterm "schema variable", and now this part of bookindex.html looks like:
- schema variable
- altering, ALTER VARIABLE
- changing, LET
- defining, CREATE VARIABLE
- description, Description
- removing, DROP VARIABLE
If a user searches the pdf, the first occurrence he finds is at:
43.13.2.4. Global variables and constants
(in itself that occurrence/mention is all right, but is should not be the first find, I think)
(I think there was in earlier versions of the patch an entry in the 'contents', i.e., at the front of the manual). I think it would be good to have it in the front-index, pointing to either LET or CREATE VARIABLE, or maybe even to a small introductory paragraph somewhere else (again, I seem to remember that there was one in an earlier patch version).I wrote new section to "advanced features" about schema variables
Of the new commands that this patch brings, 'LET' is the most immediately illuminating for a user (even when a CREATE VARIABLE has to be done first. There is an entry 'LET' in the index (good), but it would be better if that with LET-entry too the phrase 'schema variable' occurred. (I don't know if that's possible)
Then, in the CREATE VARIABLE paragraphs it says
'Changing a schema variable is non-transactional by default.'
I think that, unless there exists a mode where schema vars can be made transactional, 'by default' should be deleted (and there is no such 'transactional mode' for schema variables, is there?). The 'Description' also has such a 'By default' which is better removed for the same reason.fixed
In the CREATE VARIABLE page the example is:
CREATE VARIABLE var1 AS integer;
SELECT var1;
I suggest to make that
CREATE VARIABLE var1 AS date;
LET var1 = (select current_date);
SELECT var1;
So that the example immediately shows an application of functionality.doneThank you for the documentation review.Updated patch attachedRegardsPavel
Thanks,
Erik Rijkers
>
> Pavel
Вложения
Hifresh rebase
Pavel