Re: [HACKERS] proposal: schema variables

2017-11-13 Thread Pavel Stehule 
Hi

2017-11-13 13:15 GMT+01:00 Pavel Golub :

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

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

Regards

Pavel

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

Re: [HACKERS] proposal: schema variables

2017-11-13 Thread Pavel Golub 
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:pa...@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

Re: [HACKERS] proposal: schema variables

2017-11-03 Thread Chris Travers 
Some thoughts on this.

On Thu, Nov 2, 2017 at 4:48 PM, Tom Lane  wrote:

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

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

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

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

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

+1

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

What about having a more clunky syntax as:

SET VARIABLE foo='bar';

Perhaps one can have a short form of:

SET VAR foo = 'bar';

vs

SET foo = 'bar'; -- GUC



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



-- 
Best Regards,
Chris Travers
Database Administrator

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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Nico Williams 
On Thu, Nov 02, 2017 at 11:48:44AM -0400, Tom Lane wrote:
> Nico Williams  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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Robert Haas 
On Thu, Nov 2, 2017 at 9:05 PM, Nico Williams  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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Pavel Stehule 
2017-11-02 13:35 GMT+01:00 Robert Haas :

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

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

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

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

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

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

Regards

Pavel




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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Tom Lane 
Nico Williams  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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Pavel Stehule 
2017-11-02 16:07 GMT+01:00 Craig Ringer :

> On 26 October 2017 at 15:21, Pavel Stehule 
> 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_
> fehqyfs8gsfneer9opsmovpfnidjovgqzjzh...@mail.gmail.com
>
> It'd be nice if you summarised any outcomes from that and addressed
> it, rather than taking this as a new topic.
>

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

Regards

Pavel


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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Pavel Stehule 
2017-11-02 16:35 GMT+01:00 Nico Williams :

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

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

Regards

Pavel

>
> Nico
> --
>

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Nico Williams 
On Thu, Nov 02, 2017 at 06:05:54PM +0530, Robert Haas wrote:
> On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule  
> 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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Craig Ringer 
On 26 October 2017 at 15:21, Pavel Stehule  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_fehqyfs8gsfneer9opsmovpfnidjovgqzjzh...@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

Re: [HACKERS] proposal: schema variables

2017-11-02 Thread Robert Haas 
On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule  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

Re: [HACKERS] proposal: schema variables

2017-11-01 Thread Gilles Darold 
Le 01/11/2017 à 05:15, Pavel Stehule a écrit :
>
>
> 2017-10-31 22:28 GMT+01:00 srielau  >:
>
> Pavel,
>
> There is no
> DECLARE TEMP CURSOR
> or
> DECLARE TEMP variable in PLpgSQL
> and
>
>
> sure .. DECLARE TEMP has no sense, I talked about similarity DECLARE
> and CREATE TEMP
>
>
> CREATE TEMP TABLE has a different meaning from what I understand you
> envision for variables.
>
> But maybe I'm mistaken. Your original post did not describe the entire
> syntax:
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> Especially the TEMP is not spelled out and how its presence affects or
> doesn't ON TRANSACTION END.
> So may be if you elaborate I understand where you are coming from.
>
>
> TEMP has same functionality (and implementation) like our temp tables
> - so at session end the temp variables are destroyed, but it can be
> assigned to transaction.

Oh ok, I understand thanks for the precision.

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

Re: [HACKERS] proposal: schema variables

2017-11-01 Thread Pavel Stehule 
2017-11-01 19:03 GMT+01:00 Mark Dilger :

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

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

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

Regards

Pavel


> mark
>

Re: [HACKERS] proposal: schema variables

2017-11-01 Thread Mark Dilger 

> 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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Pavel Stehule 
2017-11-01 6:07 GMT+01:00 Serge Rielau :

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

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

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

Regards

Pavel

Serge

>

Re: [HACKERS] proposal: schema variables

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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Pavel Stehule 
2017-10-31 22:28 GMT+01:00 srielau :

> Pavel,
>
> There is no
> DECLARE TEMP CURSOR
> or
> DECLARE TEMP variable in PLpgSQL
> and
>

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


CREATE TEMP TABLE has a different meaning from what I understand you
> envision for variables.
>
> But maybe I'm mistaken. Your original post did not describe the entire
> syntax:
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>
> Especially the TEMP is not spelled out and how its presence affects or
> doesn't ON TRANSACTION END.
> So may be if you elaborate I understand where you are coming from.
>

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



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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Gilles Darold 
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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Gilles Darold 
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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread srielau 
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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Pavel Stehule 
2017-10-31 22:08 GMT+01:00 Serge Rielau :

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

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

Regards

Pavel


> My 2 cents
> Serge
>

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Serge Rielau 
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

Re: [HACKERS] proposal: schema variables

2017-10-31 Thread Pavel Stehule 
Hi

2017-10-30 22:42 GMT+01:00 srielau :

> 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  is DROP IF
EXISTS, but it should not be used.

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

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

Regards

Pavel


> Cheers
> Serge Rielau
> Salesforce.com
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-
> f1928748.html
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] proposal: schema variables

2017-10-30 Thread srielau 
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

Re: [HACKERS] proposal: schema variables

2017-10-30 Thread Hannu Krosing 
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  wrote:

>
>
> On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule 
> 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
>>> 
>>>
>>>
>>
>
>
> --
> 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
> 
>
>

Re: [HACKERS] proposal: schema variables

2017-10-29 Thread Chris Travers 
On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule 
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
>> 
>>
>>
>


-- 
Best Regards,
Chris Travers
Database Administrator

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

Re: [HACKERS] proposal: schema variables

2017-10-28 Thread Pavel Stehule 
Hi

2017-10-28 16:24 GMT+02:00 Chris Travers :

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

Re: [HACKERS] proposal: schema variables

2017-10-28 Thread Chris Travers 
On Thu, Oct 26, 2017 at 9:21 AM, Pavel Stehule 
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

Re: [HACKERS] proposal: schema variables

2017-10-27 Thread Pavel Stehule 
2017-10-27 15:38 GMT+02:00 Gilles Darold :

> Le 26/10/2017 à 09:21, Pavel Stehule a écrit :
> > Hi,
> >
> > I propose a  new database object - a variable. The variable is
> > persistent object, that holds unshared session based not transactional
> > in memory value of any type. Like variables in any other languages.
> > The persistence is required for possibility to do static checks, but
> > can be limited to session - the variables can be temporal.
> >
> > My proposal is related to session variables from Sybase, MSSQL or
> > MySQL (based on prefix usage @ or @@), or package variables from
> > Oracle (access is controlled by scope), or schema variables from DB2.
> > Any design is coming from different sources, traditions and has some
> > advantages or disadvantages. The base of my proposal is usage schema
> > variables as session variables for stored procedures. It should to
> > help to people who try to port complex projects to PostgreSQL from
> > other databases.
> >
> > The Sybase  (T-SQL) design is good for interactive work, but it is
> > weak for usage in stored procedures - the static check is not
> > possible. Is not possible to set some access rights on variables.
> >
> > The ADA design (used on Oracle) based on scope is great, but our
> > environment is not nested. And we should to support other PL than
> > PLpgSQL more strongly.
> >
> > There is not too much other possibilities - the variable that should
> > be accessed from different PL, different procedures (in time) should
> > to live somewhere over PL, and there is the schema only.
> >
> > The variable can be created by CREATE statement:
> >
> > CREATE VARIABLE public.myvar AS integer;
> > CREATE VARIABLE myschema.myvar AS mytype;
> >
> > CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
> >   [ DEFAULT expression ] [[NOT] NULL]
> >   [ ON TRANSACTION END { RESET | DROP } ]
> >   [ { VOLATILE | STABLE } ];
> >
> > It is dropped by command DROP VARIABLE  [ IF EXISTS] varname.
> >
> > The access rights is controlled by usual access rights - by commands
> > GRANT/REVOKE. The possible rights are: READ, WRITE
> >
> > The variables can be modified by SQL command SET (this is taken from
> > standard, and it natural)
> >
> > SET varname = expression;
> >
> > Unfortunately we use the SET command for different purpose. But I am
> > thinking so we can solve it with few tricks. The first is moving our
> > GUC to pg_catalog schema. We can control the strictness of SET
> > command. In one variant, we can detect custom GUC and allow it, in
> > another we can disallow a custom GUC and allow only schema variables.
> > A new command LET can be alternative.
> >
> > The variables should be used in queries implicitly (without JOIN)
> >
> > SELECT varname;
> >
> > The SEARCH_PATH is used, when varname is located. The variables can be
> > used everywhere where query parameters are allowed.
> >
> > I hope so this proposal is good enough and simple.
> >
> > Comments, notes?
> >
> > regards
> >
> > Pavel
> >
> >
>
> Great feature that will help for migration. How will you handle CONSTANT
> declaration? With Oracle it is possible to declare a constant as follow:
>
>
>   varname CONSTANT INTEGER:= 500;
>
>
> for a variable that can't be changed. Do you plan to add a CONSTANT or
> READONLY keyword or do you want use GRANT on the object to deal with
> this case?
>

Plpgsql  declaration supports CONSTANT

I forgot it. Thank you

Pavel



>
> Regards
>
> --
> Gilles Darold
> Consultant PostgreSQL
> http://dalibo.com - http://dalibo.org
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] proposal: schema variables

2017-10-27 Thread Gilles Darold 
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

Re: [HACKERS] proposal: schema variables

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

> From: pgsql-hackers-ow...@postgresql.org
> > [mailto:pgsql-hackers-ow...@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
> Takayuki Tsunakawa
>
>
>

Re: [HACKERS] proposal: schema variables

2017-10-26 Thread Tsunakawa, Takayuki 
From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@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

Re: [HACKERS] proposal: schema variables

2017-10-26 Thread Tatsuo Ishii 
> 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

Re: [HACKERS] proposal: schema variables

2017-10-26 Thread Pavel Stehule 
Hi

2017-10-27 0:07 GMT+02:00 Nico Williams :

> On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> > Comments, notes?
>
> I like it.
>
> I would further like to move all of postgresql.conf into the database,
> as much as possible, as well as pg_ident.conf and pg_hba.conf.
>
> Variables like current_user have a sort of nesting context
> functionality: calling a SECURITY DEFINER function "pushes" a new value
> onto current_user, then when the function returns the new value of
> current_user is "popped" and the previous value restored.
>

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

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


> It might be nice to be able to generalize this.
>
> Questions that then arise:
>
>  - can one see up the stack?
>  - are there permissions issues with seeing up the stack?
>

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

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

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

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

Regards

Pavel



> Nico
> --
>

Re: [HACKERS] proposal: schema variables

2017-10-26 Thread Nico Williams 
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