proposal: schema variables

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

Comments, notes?

I like it.

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

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

It might be nice to be able to generalize this.

Questions that then arise:

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

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

Nico
--

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

Hi

2017-10-27 0:07 GMT+02:00 Nico Williams <nico@cryptonector.com>:

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

Comments, notes?

I like it.

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

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

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

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

It might be nice to be able to generalize this.

Questions that then arise:

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

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

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

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

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

Regards

Pavel

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

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

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

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Pavel Stehule
I propose a new database object - a variable. The variable is persistent
object, that holds unshared session based not transactional in memory value
of any type. Like variables in any other languages. The persistence is
required for possibility to do static checks, but can be limited to session
- the variables can be temporal.

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

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

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

Regards
Takayuki Tsunakawa

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

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

From: pgsql-hackers-owner@postgresql.org

[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Pavel Stehule
I propose a new database object - a variable. The variable is persistent
object, that holds unshared session based not transactional in memory

value

of any type. Like variables in any other languages. The persistence is
required for possibility to do static checks, but can be limited to

session

- the variables can be temporal.

My proposal is related to session variables from Sybase, MSSQL or MySQL
(based on prefix usage @ or @@), or package variables from Oracle (access
is controlled by scope), or schema variables from DB2. Any design is

coming

from different sources, traditions and has some advantages or

disadvantages.

The base of my proposal is usage schema variables as session variables

for

stored procedures. It should to help to people who try to port complex
projects to PostgreSQL from other databases.

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

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

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

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

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

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

Regards

Pavel

Regards

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

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

regards

Pavel

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

  varname     CONSTANT INTEGER    := 500;

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

Regards

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

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

2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.darold@dalibo.com>:

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

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

regards

Pavel

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

varname CONSTANT INTEGER := 500;

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

Plpgsql declaration supports CONSTANT

I forgot it. Thank you

Pavel

On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

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

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

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

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

If done this way then:

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

regards

Pavel

--
Best Regards,
Chris Travers
Database Administrator

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

Hi

2017-10-28 16:24 GMT+02:00 Chris Travers <chris.travers@adjust.com>:

On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

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

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

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

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

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

The variable can be created by CREATE statement:

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

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

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

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

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

SET varname = expression;

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

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

SELECT varname;

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

I hope so this proposal is good enough and simple.

Comments, notes?

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

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

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

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

If done this way then:

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

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

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

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

Regards

Pavel

On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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

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

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

I was mostly thinking that users can probably set things like work_mem and
possibly this might be a problem.

@2 With variables typed custom GUC are not necessary

I don't know about that. For example with the geoip2lookup extension it is
nice that you could set the preferred language for translation on a per
user basis or the mmdb path on a per-db basis.

@3 Why you need it? It is possible with set_config function now.

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

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

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

Regards

Pavel

regards

Pavel

--
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin
<https://maps.google.com/?q=Saarbr%C3%BCcker+Stra%C3%9Fe+37a,+10405+Berlin&amp;entry=gmail&amp;source=g&gt;

--
Best Regards,
Chris Travers
Database Administrator

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

but you can always do

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

if you are worried about the evaluation order

On 29 October 2017 at 09:51, Chris Travers <chris.travers@adjust.com> wrote:

Pavel,

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

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

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

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

Cheers
Serge Rielau
Salesforce.com

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

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

Hi

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

Pavel,

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

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

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

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

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

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

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

Regards

Pavel

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

2017-10-31 22:08 GMT+01:00 Serge Rielau <serge@rielau.com>:

Pavel,

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

Language is important because language stays.
You choice of syntax will outlive your code and possibly yourself.

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

Regards

Pavel

Pavel,

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

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

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

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

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

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

Pavel,

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

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

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

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

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

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

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

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

Pavel,

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

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

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

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

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

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

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

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

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

Pavel,

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

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

CREATE TEMP TABLE has a different meaning from what I understand you

envision for variables.

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

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

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