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 1evHHl-0003zo-Nb for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 06:50:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1evHHk-0001ps-9q for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 06:50:16 +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 1evHH7-00061w-7Y for pgsql-hackers@lists.postgresql.org; Mon, 12 Mar 2018 06:49:37 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1evHH3-0006a7-3U for pgsql-hackers@postgresql.org; Mon, 12 Mar 2018 06:49:35 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id DCBC921C0935; Mon, 12 Mar 2018 09:49:31 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.0.1.12] (unknown [77.232.15.212]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 50FB621C0928; Mon, 12 Mar 2018 09:49:31 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1520837371; bh=9nzUtyBxEvzbf1/8Ipn+uhaiLmSB6UmWjosHyyd4Jeg=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=FxofwWdvVXACJK5kkYZsWwNnSLE47xetU2wvGDE1RV+0+VYAdOO3nBOapnY/IHFmy 1PMjEZ+01C6M5FQZdkZadQdK4tFYALu7QplXG0Txw2elr8hLLorjN+HHQWc918XrEH BeZkHgQZYT8/HKolY+0VNU+jgrP2J6sBf1omuTMk= Subject: Re: [HACKERS] proposal: schema variables To: Pavel Stehule , "David G. Johnston" Cc: Pavel Golub , PostgreSQL Hackers References: <623395617.20171113141500@gf.microolap.com> From: Pavel Luzanov Message-ID: Date: Mon, 12 Mar 2018 09:49:29 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6A2F978A7AFB54F1BD0DC0D6" Content-Language: ru-RU List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6A2F978A7AFB54F1BD0DC0D6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 ? ----- 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 > > --------------6A2F978A7AFB54F1BD0DC0D6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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 ?

-----
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 <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=# 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
 


--------------6A2F978A7AFB54F1BD0DC0D6--