Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e7xxf-0002yc-MW for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 06:17:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e7xxf-00075s-54 for pgsql-hackers@arkaria.postgresql.org; Fri, 27 Oct 2017 06:17:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e7xxd-000733-IG for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 06:17:41 +0000 Received: from mail-wr0-x233.google.com ([2a00:1450:400c:c0c::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e7xxa-0004iV-3D for pgsql-hackers@postgresql.org; Fri, 27 Oct 2017 06:17:40 +0000 Received: by mail-wr0-x233.google.com with SMTP id y9so5173044wrb.2 for ; Thu, 26 Oct 2017 23:17:37 -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=DKtCMxM4eFvDqpDFVjT2VTeywFmv9R8C5GgZEKvU9O0=; b=gxmXWirHZ41xH93l7dTFnVN3mRj0XFs7d2T6lZEmbHMRkPKmAOs9uSo5AhPFUrUHO1 7kFMqKtwjQ+dWRRes03pXBFj+tx3p+5tmfcTW+naIRJ3+2r1SP6O0b9WRwV2S5CCYgnx 2YQlZfGMQKdheQur7kl7WgGUsuPsHTar7xBDUfroScl9cBGMsXSEWn2vkaHceWW+ZcNo hnVTudexQ5rR8MYCEUdaqQBdfC/h3ulEHf/n2DML0QUtvqH6kukGGaeN4CHcAhIgWLWo CS8kOLzOyd8BMdtj/dOhTinpLftEmPKs34p0Awx/gZag5oJ74n4V0rK0AilOX2RcJtEh D9Cw== 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=DKtCMxM4eFvDqpDFVjT2VTeywFmv9R8C5GgZEKvU9O0=; b=U6MJmb/5lqi6x/BZscwRH32oCyjZ2+mX5UraC7fIEoqfn2XSzmhsP8wvZ1g36Uy+YI RJpWAceFT/OAZCHediwGD5Vu43abPpYDupG7X6d+/yfCCEnIVY2svqgs3J5hhGVeS/Vy /Hu5OlLwE+EikyvrQ+Cp/ABxCTtX+myuy8h6XU/FEL2MrDUh8iRW726sqzfVPgn6QirL 6VvbGWEVTogJGsJR/g7AEg8N4rdn/AVwno8eIW7uOTq7jPWUqwt6Qp9IrhtVKBgUPk4t XVP3GBPlSxDc8CM6bo+qsqQpG5By9cp17ZjTdNWA1TckuWk/ITWwdxaknh+nClmREck6 wmWw== X-Gm-Message-State: AMCzsaU0idqiGuyBLaf9vlrQ3q+54E3PpiRlj0msOZiFk1qO91hpCGmy Y5TtGIvfSSwbTlrILW91p1jnnUobw+y5dwCIxIU= X-Google-Smtp-Source: ABhQp+TqeKOmgvZLMGYt+OOOLARGic532e3y8zjhDTVcZgtuu1ZpNXpVHDgNU/kPBZL6ps5mA/rB7/Beb2NKkVckU4M= X-Received: by 10.223.170.67 with SMTP id q3mr6643855wrd.193.1509085057031; Thu, 26 Oct 2017 23:17:37 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.146.7 with HTTP; Thu, 26 Oct 2017 23:16:56 -0700 (PDT) In-Reply-To: <0A3221C70F24FB45833433255569204D1F80CE8C@G01JPEXMBYT05> References: <0A3221C70F24FB45833433255569204D1F80CE8C@G01JPEXMBYT05> From: Pavel Stehule Date: Fri, 27 Oct 2017 08:16:56 +0200 Message-ID: Subject: Re: proposal: schema variables To: "Tsunakawa, Takayuki" Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="94eb2c1cc5c8c2a2a8055c813e40" 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 --94eb2c1cc5c8c2a2a8055c813e40 Content-Type: text/plain; charset="UTF-8" 2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki < tsunakawa.takay@jp.fujitsu.com>: > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Pavel Stehule > > I propose a new database object - a variable. The variable is persistent > > object, that holds unshared session based not transactional in memory > value > > of any type. Like variables in any other languages. The persistence is > > required for possibility to do static checks, but can be limited to > session > > - the variables can be temporal. > > > > > > My proposal is related to session variables from Sybase, MSSQL or MySQL > > (based on prefix usage @ or @@), or package variables from Oracle (access > > is controlled by scope), or schema variables from DB2. Any design is > coming > > from different sources, traditions and has some advantages or > disadvantages. > > The base of my proposal is usage schema variables as session variables > for > > stored procedures. It should to help to people who try to port complex > > projects to PostgreSQL from other databases. > > Very interesting. I hope I could join the review and testing. > you are welcome. I wrote a prototype last year based on envelope functions. But the integration must be much more close to SQL to be some clear benefit of this feature. So there is lot of work. I hope so I have a prototype after this winter. It is my plan for winter. > > How do you think this would contribute to easing the port of Oracle PL/SQL > procedures? Would the combination of orafce and this feature promote > auto-translation of PL/SQL procedures? I'm curious what will be the major > road blocks after adding the schema variable. > It depends on creativity of PL/SQL developers. Usual .. 80% application is possible to migrate with current GUC - some work does ora2pg. But GUC is little bit slower (not too important) and is not simple possibility to secure it. So work with variables will be similar like GUC, but significantly more natural (not necessary to build wrap functions). It should be much better when value is of some composite type. The migrations will need some inteligence still, but less work and code will be more readable and cleaner. I talked already about "schema pined" functions (schema private/public objects) - but I didn't think about it more deeply. There can be special access right to schema variables, the pined schema can be preferred before search_path. With this feature the schema will have very similar behave like Oracle Modules. Using different words - we can implement scope access rights based on schemas. But it is far horizon. What is important - proposal doesn't block any future enhancing in this case, and is consistent with current state. In future you can work with schema private functions, tables, variables, sequences. So variables are nothing special. Regards Pavel Regards > Takayuki Tsunakawa > > > --94eb2c1cc5c8c2a2a8055c813e40 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki <tsunakawa.= takay@jp.fujitsu.com>:
From= : pgsql-hackers-owner= @postgresql.org
> [mailto:pgsql-ha= ckers-owner@postgresql.org] On Behalf Of Pavel Stehule
> I propose a=C2=A0 new database object - a variable. T= he 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 se= ssion
> - the variables can be temporal.
>
>
> My proposal is related to session variables from Sybase, MSSQL or MySQ= L
> (based on prefix usage @ or @@), or package variables from Oracle (acc= ess
> is controlled by scope), or schema variables from DB2. Any design is c= oming
> from different sources, traditions and has some advantages or disadvan= tages.
> 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.=C2=A0 I hope I could join the review and testing.<= br>

you are welcome. I wrote a prototype la= st 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 w= ork. I hope so I have a prototype after this winter. It is my plan for wint= er.
=C2=A0

How do you think this would contribute to easing the port of Oracle PL/SQL = procedures?=C2=A0 Would the combination of orafce and this feature promote = auto-translation of PL/SQL procedures?=C2=A0 I'm curious what will be t= he 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 ora2p= g. But GUC is little bit slower (not too important) and is not simple possi= bility to secure it.

So work with variables w= ill be similar like GUC, but significantly more natural (not necessary to b= uild wrap functions). It should be much better when value is of some compos= ite type. The migrations will need some inteligence still, but less work an= d code will be more readable and cleaner.

I ta= lked already about "schema pined" functions (schema private/publi= c objects) - but I didn't think about it more deeply. There can be spec= ial access right to schema variables, the pined schema can be preferred bef= ore 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 - propos= al doesn't block any future enhancing in this case, and is consistent w= ith current state. In future you can work with schema private functions, ta= bles, variables, sequences. So variables are nothing special.
Regards

Pavel


Regards
Takayuki Tsunakawa



--94eb2c1cc5c8c2a2a8055c813e40--