Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e8S4g-0002l6-0I for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Oct 2017 14:26:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e8S4e-00050q-I7 for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Oct 2017 14:26:56 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e8S35-0002Hh-N7 for pgsql-hackers@postgresql.org; Sat, 28 Oct 2017 14:25:20 +0000 Received: from mail-qk0-x232.google.com ([2607:f8b0:400d:c09::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e8S31-0005Hw-C4 for pgsql-hackers@postgresql.org; Sat, 28 Oct 2017 14:25:18 +0000 Received: by mail-qk0-x232.google.com with SMTP id b15so11458452qkg.9 for ; Sat, 28 Oct 2017 07:25:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=adjust.com; s=google; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=ANxZRZj7VtEsTgkRPG2xRdtwgeD+zOsV7E3+YfXiCpY=; b=ATsSoiGTzJmaY1PqBs6xKWVlzxC28FMHeVpFeJ1MdUt0+eDh7WbIhybfdvirvw0Aji 9nmLyi8YtocDqcBWkueAl7uj8YukATjQvJ8mHOoK7bQGYNSIKm3ia/75ObL8bo4PH1fo z7AdpR+NiFPkhJu1lMoewUMZJ4ODmCxIxQP1s+/bPVN0SdMK27cEb9RBz8C8pFyQlnDH BMtGFMdfkOHanrWWpmQRALcZ/susIjwoCkv9VDWBFKzG2fnk+nYyabnyRQBY/WJivtEe r171gd3YUOZebTHDy/d62o+MAdVKgbg07bJM5HvXOXCDox7Ukc5WMVEFccabjEZjRo6P K+Rg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=ANxZRZj7VtEsTgkRPG2xRdtwgeD+zOsV7E3+YfXiCpY=; b=jIvDzdAhYnZecOJ6HEijRpCoaRvivRXbpsBaZ4rSf5xMhjZq4czu9SQmvLzcMznoxm nqT1WdDFqt5nqGS84CSO4J+aVfEzYRTv72InRi2k92mTY5AsBHflQavhg2j0XK+rditI Z95+aBC6c0YqIS8GtB4cXhut/12MnfRXQxsE0EWJiZKS4Yp6sAkc4DlrLhS4/gesKz4/ kmW/4B8bCgoT/yCMPPymLMOjgSeZjyNc1ON1gPo2FqMQM2yk3dhZZp7bVX3y067QLrmB ymwjIwDAo2IbLqcxHqJxU115q7ptzzgF+dGFD5l1TnzJHg7Lpa3lwo9N13g8b0ghmjTh tZTQ== X-Gm-Message-State: AMCzsaWEKZhZlkr0LslHYnFwf/BXoQHU8D++xyXFn4Kgx7f1KI0/1CH+ mW6by5KrKm3Z+Mo//iIqm6B2kiU8gngM5ftoohzBcg== X-Google-Smtp-Source: ABhQp+TMwRUcdkupzk4OIbt12cb0D7tM5gGveR+14ODSh8RFoLUZwrPidPDy+UzsZXTfTlNiCYEoHs+oFyOoQJR1uok= X-Received: by 10.55.53.9 with SMTP id c9mr5202122qka.177.1509200714081; Sat, 28 Oct 2017 07:25:14 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.96.40 with HTTP; Sat, 28 Oct 2017 07:24:43 -0700 (PDT) In-Reply-To: References: From: Chris Travers Date: Sat, 28 Oct 2017 16:24:43 +0200 Message-ID: Subject: Re: proposal: schema variables To: Pavel Stehule Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a114900b2753a81055c9c2caa" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --001a114900b2753a81055c9c2caa Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 val= ue > of any type. Like variables in any other languages. The persistence is > required for possibility to do static checks, but can be limited to sessi= on > - 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 comi= ng > from different sources, traditions and has some advantages or > disadvantages. The base of my proposal is usage schema variables as sessi= on > 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 =3D 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 disallo= w > 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 > > > --=20 Best Regards, Chris Travers Database Administrator Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com Saarbr=C3=BCcker Stra=C3=9Fe 37a, 10405 Berlin --001a114900b2753a81055c9c2caa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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

I propose a=C2=A0 new database object - a variable. = The variable is persistent object, that holds unshared session based not tr= ansactional in memory value of any type. Like variables in any other langua= ges. The persistence is required for possibility to do static checks, but c= an 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 cont= rolled by scope), or schema variables from DB2. Any design is coming from d= ifferent sources, traditions and has some advantages or disadvantages. The = base of my proposal is usage schema variables as session variables for stor= ed procedures. It should to help to people who try to port complex projects= to PostgreSQL from other databases.

The Sybase=C2=A0 (T-SQL) = design is good for interactive work, but it is weak for usage in stored pro= cedures - the static check is not possible. Is not possible to set some acc= ess rights on variables.

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

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

The variable can be created by CREA= TE statement:

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

CREATE [TEMP] VARI= ABLE [IF NOT EXISTS] name AS type
=C2=A0 [ DEFAULT expression ] [= [NOT] NULL]
=C2=A0 [ ON TRANSACTION END { RESET | DROP } ]
<= div>=C2=A0 [ { VOLATILE | STABLE } ];

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

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

The variables can be modi= fied by SQL command SET (this is taken from standard, and it natural)
SET varname =3D expression;

Unfortunately we use the S= ET 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 contr= ol 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.

Th= e variables should be used in queries implicitly (without JOIN)
<= br>
SELECT varname;

The SEARCH_PATH is u= sed, when varname is located. The variables can be used everywhere where qu= ery parameters are allowed.

I hope so this pro= posal is good enough and simple.

Comments, notes?<= /div>


I ha= ve a question on this.=C2=A0 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.=C2=A0
<= br>
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 sett= ings then we could have a CREATE SETTING command which would behave like th= is and then use SET the same way across both.

In e= ssence I am wondering if this really needs to be as separate from GUC as yo= u are proposing.

If done this way then:
=
1.=C2=A0 You could issue grant or revoke on GUC settings, al= lowing some users but not others to set things like work_mem for their quer= ies
2.=C2=A0 You could specify allowed types in custom settings.<= /div>
3.=C2=A0 In a subsequent stage you might be able to SELECT .... I= NTO setting_name FROM ....; =C2=A0allowing access to setting writes based o= n queries.



regards

Pavel


=



--
Best Regards,
Chris Travers
<= div>Database Administrator

Tel:=C2=A0+49 162 9037 210=C2=A0| Skype: einhverfr |=C2=A0www.adjust.com=C2= =A0
Saarbr=C3=BCcker Stra=C3=9Fe 37a, 10405 Berlin

--001a114900b2753a81055c9c2caa--