Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1evHMX-0004CC-LH for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 06:55:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1evHMW-0004Ql-5S for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 06:55:12 +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.89) (envelope-from ) id 1evHMV-0004QQ-Rt for pgsql-hackers@lists.postgresql.org; Mon, 12 Mar 2018 06:55:11 +0000 Received: from mail-wm0-x243.google.com ([2a00:1450:400c:c09::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1evHMS-0006kg-G5 for pgsql-hackers@postgresql.org; Mon, 12 Mar 2018 06:55:11 +0000 Received: by mail-wm0-x243.google.com with SMTP id h21so14166775wmd.1 for ; Sun, 11 Mar 2018 23:55:07 -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=r1aS8bAv4wPoy+c6/3zPEGz0UvWpxz519g5/GtfiujM=; b=LHsLm7Jh/hSp+piBhx9Q6zNoANxwei5gc0VqY3KRYsQrbWcU8PYAJh6KfydXxic5Eu Sj/msfw+PtmWlCPLj0bp7rYTZZaPD1gzEkT0ZwyuIZUgYOLJa6Ak/dtKHTLylRvG0sBz 9Ucjc6lZkJ5eJtKzTZwh070nuSqZuLyOaK47T8T1pLTKUOfYwCQRdO2VkHPOfSb0QttE cgXHa1qfz2RUgJd2HGSNbhXcoyAjc8yk3NuFiepdgAVFvPzpJXB61EE2gp1dlJK3XF+W X2ReexXsr8UF32XUfV2QnO3jIE3j46XXjG/RvRcmA71kFXvgkFEtb20Xed7uhVgEeRvu biRg== 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=r1aS8bAv4wPoy+c6/3zPEGz0UvWpxz519g5/GtfiujM=; b=ZvoVzvdz0tJ/EMt+DMGo5h7YnaA3J0AgwZYPi1i96hGTh0Btdi19FqYbyJK+gZKRUY OLm+kUXymgRvzRdTMEK7UEffqR8bxInyZuHULWHXQdjuweP0LUvcEJAZFUQCwLZDHeHx eqkpZfTXaIYF+dfxE9gODq2tRyD+UHTEjiOXeOhpZDGX+6om/YX8w+iLMLXBXvFo9/RP qWETLgCaxnqUyA0ZBbxyyvmITuUjq0xV588w7dhVJbj2j4zJqXMthWXKEPG+IQBu6zI8 3e9B5mRnXj2R3tE1W7BX6sNpEQAM+FRhx+CajrII1Gh5kcw1iyfu/tP7Ug9aILxwebEd wIgQ== X-Gm-Message-State: AElRT7EhJSpGxQEs9sf1W8VLUMpxNv0/H6/ULHu2tkESef1CZJP6ZTjV DqoAZBBVHnIGSrESV1dvkO1UsC/Vj0XWUN1MOYA= X-Google-Smtp-Source: AG47ELtItobQM+l31dW3Z1qxz4k9CN2Bef+EekcDqzMWhs/ilIuz7QGI8AFFlT4d6cEt1Bs+6boG7cc7ZsRtLbQ0UKk= X-Received: by 10.28.23.143 with SMTP id 137mr4910360wmx.153.1520837706732; Sun, 11 Mar 2018 23:55:06 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.179.65 with HTTP; Sun, 11 Mar 2018 23:54:26 -0700 (PDT) In-Reply-To: References: <623395617.20171113141500@gf.microolap.com> From: Pavel Stehule Date: Mon, 12 Mar 2018 07:54:26 +0100 Message-ID: Subject: Re: [HACKERS] proposal: schema variables To: Pavel Luzanov Cc: "David G. Johnston" , Pavel Golub , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="001a114714a84559da0567319fac" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a114714a84559da0567319fac Content-Type: text/plain; charset="UTF-8" 2018-03-12 7:49 GMT+01:00 Pavel Luzanov : > Hi, > > I plan to make usability and feature test review in several days. > > Is there any chances that it will work on replicas? > Such possibility is very helpful in generating reports. > Now, LET command produces an error: > > ERROR: cannot execute LET in a read-only transaction > > > But if we say that variables are non-transactional ? > sure, it should to work. Now, I am try to solve a issues on concept level - the LET code is based on DML code base, so probably there is check for rw transactions. But it is useless for LET command. Regards Pavel > > ----- > Pavel Luzanov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > On 08.03.2018 21:00, Pavel Stehule wrote: > > Hi > > 2018-02-07 7:34 GMT+01:00 Pavel Stehule : > >> Hi >> >> updated patch with your changes in documentation and pg_dump (initial) >> support >> >> Main issue of this patch is storage. We can reuse local buffers used for >> temp tables. But it does allocation by 8KB and it creates temp files for >> every object. That is too big overhead. Storing just in session memory is >> too simple - then there should be lot of new code used, when variable will >> be dropped. >> >> I have ideas how to allow work with mix of scalar and composite types - >> so it will be next step of this prototype. >> >> Regards >> >> Pavel >> > > new update - rebased, + some initial support for composite values on right > side and custom types, arrays are supported too. > > omega=# CREATE VARIABLE xx AS (a int, b numeric); > CREATE VARIABLE > omega=# LET xx = (10, 20)::xx; > LET > omega=# SELECT xx; > +---------+ > | xx | > +---------+ > | (10,20) | > +---------+ > (1 row) > > omega=# SELECT xx.a + xx.b; > +----------+ > | ?column? | > +----------+ > | 30 | > +----------+ > (1 row) > > omega=# \d xx > schema variable "public.xx" > +--------+---------+ > | Column | Type | > +--------+---------+ > | a | integer | > | b | numeric | > +--------+---------+ > > > Regards > > Pavel > > > > --001a114714a84559da0567319fac Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespr= o.ru>:
=20 =20 =20
Hi,

I plan to make usability and feature test review in several days.

Is there any chances that it will work on replicas?
Such possibility is very helpful in generating reports.
Now, LET command produces an error:

ERROR:=C2=A0 cannot execute LET in a read-only transaction
=C2=A0

But if we say that variables are non-transactional ?

sure, it should to work. Now, I am try to solve a i= ssues on concept level - the LET code is based on DML code base, so probabl= y there is check for rw transactions. But it is useless for LET command.
Regards

Pavel
=C2=A0

-----
Pavel Luzanov
Postgres Professional: http://www.postgr=
espro.com
The Russian Postgres Company
On 08.03.2018 21:00= , Pavel Stehule wrote:
Hi

2018-02-07 7:34 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
Hi

updated patch with your changes in documentation and pg_dump (initial) support

Main issue of this patch is storage. We can reuse local buffers used for temp tables. But it does allocation by 8KB and it creates temp files for every object. That is too big overhead. Storing just in session memory is too simple - then there should be lot of new code used, when variable will be dropped.

I have ideas how to allow work with mix of scalar and composite types - so it will be next step of this prototype.

Regards

Pavel

new update - rebased, + some initial support for composite values on right side and custom types, arrays are supported too.

omega=3D# CREATE VARIABLE xx AS (a int, b numeric);
CREATE VARIABLE
omega=3D# LET xx =3D (10, 20)::xx;
LET
omega=3D# SELECT xx;
+---------+
|=C2=A0=C2=A0 xx=C2=A0=C2=A0=C2=A0 |
+---------+
| (10,20) |
+---------+
(1 row)

omega=3D# SELECT xx.a + xx.b;
+----------+
| ?column? |
+----------+
|=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 30 |
+----------+
(1 row)

omeg= a=3D# \d xx
schema variable "public.xx"
+--------+---------+
| Column |=C2=A0 Type=C2=A0=C2=A0 |
+--------+---------+
| a=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | integer |
| b=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | numeric |
+--------+---------+


Regards

Pavel
=C2=A0



--001a114714a84559da0567319fac--