Thread: [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

> 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

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

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,

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

> Comments, notes?

How would variables behave on transaction rollback?

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

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


mark


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

2017-10-31 22:28 GMT+01:00 srielau <serge@rielau.com>:

Pavel,

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

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

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

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

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

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

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

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

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

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

Hi

2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

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

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

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

Regards

Pavel

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

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

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

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

Regards

Pavel

 

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.

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

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

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

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

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

Hi

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

Hi,

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

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

 - Some compilation warning must be fixed:

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

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

 - How about Peter's suggestion?:
    "In DB2, the privileges for variables are named READ and WRITE. That would make more sense to me than reusing the privilege names for tables.

    The patch use SELECT and UPDATE which make sense too for SELECT but less for UPDATE.

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

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

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

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

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

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

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

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

1. The schema variables should to have own system table

2. The composite schema variables should to use explicitly defined composite type

3. The memory management is not nice - transactional drop table with content is implemented ugly.

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

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

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

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

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

Regards

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

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

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

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

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,

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

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