Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e85Mi-0004JY-Te for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 14:12:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e85Mi-0000s5-Dj for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 14:12:04 +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 1e85L7-0006ah-Tr for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 14:10:26 +0000 Received: from mail-wm0-x22c.google.com ([2a00:1450:400c:c09::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e85L0-0000GO-GZ for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 14:10:24 +0000 Received: by mail-wm0-x22c.google.com with SMTP id r196so4094460wmf.2 for ; Fri, 27 Oct 2017 07:10:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=4d03JOhSAl3jQSuBf9BN+oa3+qQ+gvWf7IV3SB0Ziak=; b=uYis+nUOvTha91kxODrUf2P4lgz15Ow2NMhC9KtMBWuL2oBan3TTF+BrDaqrR0Fjwy 4HN0otoRFPJABWs2oRuhgFiaVvLDiOgwvCdgc8fysgMOdV/Ef4L35e77oi1gzRGQR/49 TB5qUtMKR8lJQiRPkFZkF7GfPCKlc6RHrG07ZrwsYdTGMnyIqzmJic1FK3EPu9Bw3GdQ 4VLjxm5mYpANmqWmKigtUvlK3s9Qiu1VX32ho1zaWL5q9fTktr+wjbzu0kGMQH6qkRfa WYPeBZDVCKuQZwLctU0NrnN0RzgRslz75pn8ZXJGcVglA+ObTekhmIab/S/I0smJM4Wf hZnQ== 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=4d03JOhSAl3jQSuBf9BN+oa3+qQ+gvWf7IV3SB0Ziak=; b=OfAMzZRCIy5zTplTBjFhTMyFQrIaZ9sapl5tceKUMgvsyPiL3kmXawb3tPTSBZiZOB 3vxIu08qF3pNi5I2ZY3Jtdggzh7CaS7YhxZOy+0/VQWuktqoLQhDC0W/hoxZ151RUVFt 81d9NEzAcmMw3SwQehzq7VFr5SwRJ24ejFGSbfDTR5YVr1qVz6fAiRjMM5lD5tg7bNE8 56S5jmWCqEoHddPQCEaf4M6zA79hZAoVHhHXYcK2FPGwAC7XJOg51Y9A/8CqCvbuPDfp CDH+ITKJxkfk3g32G0qxPYZAiHWh5mi8CJNE5uW6kaUQNNtqAZMuGgpYx2UiW9df3Vkp j3XA== X-Gm-Message-State: AMCzsaUDD1UhxNfWquu9lsSz5OJrEosiwwX8RHj4FRxq/g6xSrokYgid 0MRKi5ShHzCf0Mk1lb8u+F02e4xwi0vUpE/FUhs= X-Google-Smtp-Source: ABhQp+TNRVbqcOhOrZRyJ0BziUORX2THuelDgatk6mYTrEUcUrCH1DbXoDWhbGeIMjozkQDpIsXUJEd0ekFyYYAEX5E= X-Received: by 10.28.48.150 with SMTP id w144mr510573wmw.23.1509113416883; Fri, 27 Oct 2017 07:10:16 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.146.7 with HTTP; Fri, 27 Oct 2017 07:09:36 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Fri, 27 Oct 2017 16:09:36 +0200 Message-ID: Subject: Re: proposal: schema variables To: Gilles Darold Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a1142427e23a09e055c87d94a" 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 --001a1142427e23a09e055c87d94a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 2017-10-27 15:38 GMT+02:00 Gilles Darold : > Le 26/10/2017 =C3=A0 09:21, Pavel Stehule a =C3=A9crit : > > 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 =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 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 :=3D 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 > --001a1142427e23a09e055c87d94a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-10-27 15:38 GMT+02:00 Gilles Darold <gilles.darold@dalibo= .com>:
Le 26/10/2017 =C3=A0 09:21, Pavel Stehule a =C3=A9crit= =C2=A0:
> Hi,
>
> I propose a=C2=A0 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.<= br> > 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=C2=A0 (T-SQL) design is good for interactive work, but it i= s
> 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
> =C2=A0 [ DEFAULT expression ] [[NOT] NULL]
> =C2=A0 [ ON TRANSACTION END { RESET | DROP } ]
> =C2=A0 [ { VOLATILE | STABLE } ];
>
> It is dropped by command DROP VARIABLE=C2=A0 [ 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 disallow a custom GUC and allow only schema variables.<= br> > 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:

=C2=A0 varname =C2=A0=C2=A0=C2=A0 CONSTANT INTEGER =C2=A0=C2=A0 :=3D 500;

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

Plpgsql=C2= =A0 declaration supports CONSTANT

I forgot it. Thank you

Pavel




Regards

--
Gilles Darold
Consultant PostgreSQL
http://d= alibo.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-hacker= s

--001a1142427e23a09e055c87d94a--