Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e8SYM-0004SZ-R4 for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Oct 2017 14:57:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e8SYL-0000OG-Mn for pgsql-hackers@arkaria.postgresql.org; Sat, 28 Oct 2017 14:57:37 +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 1e8SYK-0000O6-MV for pgsql-hackers@postgresql.org; Sat, 28 Oct 2017 14:57:36 +0000 Received: from mail-wm0-x233.google.com ([2a00:1450:400c:c09::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e8SYH-0005zZ-4M for pgsql-hackers@postgresql.org; Sat, 28 Oct 2017 14:57:35 +0000 Received: by mail-wm0-x233.google.com with SMTP id m72so5628701wmc.0 for ; Sat, 28 Oct 2017 07:57:32 -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=ueIlZspczdhoBXAal9xoMPLQxY/YCznIyE33y1hUDFI=; b=RuF0Cjbha5Mj2aiaPrZbDdCFcdi8u8G9twZ4mX6jj3lF5oS/XGLCVmJqjhCqNPZSKq FK4kvNTf7F/hIm3psA120KPnlhzbcKRSf7MB+pmUPL6V8NJgozKfPjVJrWB0aozgChII E0c6SP6wel0bpxszSsDxgpmemv2bG83aYHLKRRZaqnRr0GhmJfA3qTKSH4fdLQVH//IN EJcMg/M/Ewe41/VXwlOBPk9Y5WOVJRljBG5OjI7f+E6vIfqoZsP6JC6PKlcQQxoJv16X v5rRPDKfrZ2vUQPArE8Q9cfWoBPs+xTXTdsj7jnqhrad98dLV9RQbJEbVdiYvso3hSr4 iuyA== 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=ueIlZspczdhoBXAal9xoMPLQxY/YCznIyE33y1hUDFI=; b=HlLlCdrY9W3l61zggVh42MubjOQWX3zpSXErHy24PhXDhhiHc3ptmHcRoz1TeDQGlx BOKpnV7zWM3X5be1CNLhtxJg22xy6Kv0PSjoQ1MJXXO3xTaX7J24C1sr0/YqGqBbLuCT 9y5w2q/1I5+ZdasnOcyHgJZfCeI4tr6PU/YdOnY+uBM8CrkboKqfzw8Ibb2l8BlnnQbI bA+cSNYCEVOtubNUZNefEgFz3NiA3Rn4Wk5SAwevDMOiyqx0IH2f6CPkzsgVRnMj8KFq Sm8G4TjMtOjsh0MWV82l4kHAK2dP3IVvoH9LbnZ7yhK9fDbxdWwO8RVK+XaECuR8g/zk O/Pg== X-Gm-Message-State: AMCzsaVMbL6KBJ/sU9biv7xI1X71O9oleUZYGqkGpGs/knzZ8ViQ+mNA tV72ZUffl8Gmou5C5PFOD9/dF2BAMot3Oq0itwc= X-Google-Smtp-Source: ABhQp+SEJXGZyPcRDJwaOG1EQ9GzaKVXY0OboqVabhivoeMd1rn20yV8Glt5rPg7AiIZWezodvkU4SQmB/qqPkIUDsg= X-Received: by 10.28.48.150 with SMTP id w144mr2760197wmw.23.1509202649954; Sat, 28 Oct 2017 07:57:29 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.146.7 with HTTP; Sat, 28 Oct 2017 07:56:49 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Sat, 28 Oct 2017 16:56:49 +0200 Message-ID: Subject: Re: proposal: schema variables To: Chris Travers Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a1142427ed84064055c9c9fe9" 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 --001a1142427ed84064055c9c9fe9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 persisten= t >> object, that holds unshared session based not transactional in memory va= lue >> of any type. Like variables in any other languages. The persistence is >> required for possibility to do static checks, but can be limited to sess= ion >> - 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 (acces= s >> is controlled by scope), or schema variables from DB2. Any design is com= ing >> from different sources, traditions and has some advantages or >> disadvantages. The base of my proposal is usage schema variables as sess= ion >> 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 no= t >> 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 PLpgSQ= L >> more strongly. >> >> There is not too much other possibilities - the variable that should be >> accessed from different PL, different procedures (in time) should to liv= e >> 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 disall= ow >> 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 creat= ed > 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 queri= es. > > 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 > > >> regards >> >> Pavel >> >> >> > > > -- > Best Regards, > Chris Travers > Database Administrator > > Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr | > www.adjust.com > Saarbr=C3=BCcker Stra=C3=9Fe 37a, 10405 Berlin > > > --001a1142427ed84064055c9c9fe9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

2017-10-28 16:24 GMT+02:00 Chris Travers <<= a href=3D"mailto:chris.travers@adjust.com" target=3D"_blank">chris.travers@= adjust.com>:


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

I propose a=C2=A0 new database object - a variabl= e. The variable is persistent object, that holds unshared session based not= transactional in memory value of any type. Like variables in any other lan= guages. The persistence is required for possibility to do static checks, bu= t can be limited to session - the variables can be temporal.

= My proposal is related to session variables from Sybase, MSSQL or MySQL (ba= sed on prefix usage @ or @@), or package variables from Oracle (access is c= ontrolled by scope), or schema variables from DB2. Any design is coming fro= m different sources, traditions and has some advantages or disadvantages. T= he base of my proposal is usage schema variables as session variables for s= tored procedures. It should to help to people who try to port complex proje= cts to PostgreSQL from other databases.

The Sybase=C2=A0 (T-SQ= L) 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) ba= sed 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 diffe= rent PL, different procedures (in time) should to live somewhere over PL, a= nd there is the schema only.

The variable can be created by C= REATE statement:

CREATE VARIABLE public.myvar AS integer;
<= /div>CREATE VARIABLE myschema.myvar AS mytype;

CREATE [TEMP] V= ARIABLE [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 drop= ped by command DROP VARIABLE=C2=A0 [ IF EXISTS] varname.

The a= ccess rights is controlled by usual access rights - by commands GRANT/REVOK= E. The possible rights are: READ, WRITE

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

Unfortunately we use th= e SET command for different purpose. But I am thinking so we can solve it w= ith few tricks. The first is moving our GUC to pg_catalog schema. We can co= ntrol the strictness of SET command. In one variant, we can detect custom G= UC and allow it, in another we can disallow a custom GUC and allow only sch= ema variables. A new command LET can be alternative.

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

SELECT varname;

The SEARCH_PATH i= s 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, note= s?


I have a question on this.=C2=A0 Since one can issue set commands on = arbitrary settings (and later ALTER database/role/system on settings you ha= ve 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

Maybe it would be simpler to treat variables and GU= C 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 beha= ve 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:<= /div>

1.=C2=A0 You could issue grant or revoke on GUC se= ttings, allowing some users but not others to set things like work_mem for = their queries
2.=C2=A0 You could specify allowed types in custom = settings.
3.=C2=A0 In a subsequent stage you might be able to SEL= ECT .... INTO setting_name FROM ....; =C2=A0allowing access to setting writ= es 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 a= re zero intersection because variables and GUC are designed for different p= urposes. 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 d= ifferent default granularity - global, database, user, session, function. T= his can be unwanted behave for variables - it can be source of hard to dete= cted bugs. I afraid so this behave can be too messy for usage as variables.=

@1 I have not clean opinion about it - not s= ure if rights are good enough - probably some user limits can be more pract= ical - 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


=C2=A0
=

=

regards

Pavel





--
Best Regards,
Chris Travers
Database Administrator

Tel:=C2=A0+49 162 9037 210=C2=A0| Skype: einhverfr |=C2=A0<= /span>www.adjus= t.com=C2=A0


--001a1142427ed84064055c9c9fe9--