Subject: Re: [HACKERS] proposal: schema variables
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 Pavel Golub <pavel@gf.microolap.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: 
 <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
 <623395617.20171113141500@gf.microolap.com>
 <CAFj8pRDdS7ViLBJ6eA93u=C_x15EBv2deiNQDGkBS=LNrjzLLw@mail.gmail.com>
 <CAFj8pRBfb-GTZSHSRVTpMzGr26-7e-_RmOmRpmuk+xuDTgC=mA@mail.gmail.com>
 <CAKFQuwa00-4HTujbnYNy_OdZ2OfjUO3AX4R91DbQ1wPBZegCfg@mail.gmail.com>
 <CAFj8pRBchBfyPKAVPnJKhDjh7r6kTJ9L1=etMt-pjdDzpsKOUA@mail.gmail.com>
 <CAFj8pRCTsxzsxX1cWgX3cjqsSZb=gqOMd3-2FdbGz5a2+shGMg@mail.gmail.com>
 <CAFj8pRA6YWwV=sZj5iSgDUixr-S+u9W2+v0BJqqxHQDXS2oZww@mail.gmail.com>
 <dde8f23f-a15f-e88b-482e-5c26cce14cca@postgrespro.ru>
 <CAFj8pRDLBtPM9PAVx-VFtLyZJktKL5R4hX4V4O4KvcyjbOPRbw@mail.gmail.com>
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
Message-ID: <ef39f19b-9d5c-78d1-6afa-adbe1288a20b@postgrespro.ru>
Date: Mon, 12 Mar 2018 18:38:39 +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: 
 <CAFj8pRDLBtPM9PAVx-VFtLyZJktKL5R4hX4V4O4KvcyjbOPRbw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------FFA0A37FC97E557C3F4338B0"
Content-Language: ru-RU
Precedence: bulk

This is a multi-part message in MIME format.
--------------FFA0A37FC97E557C3F4338B0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit


On 12.03.2018 09:54, Pavel Stehule wrote:
>
> 2018-03-12 7:49 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespro.ru 
> <mailto:p.luzanov@postgrespro.ru>>:
>
>
>     Is there any chances that it will work on replicas?
>
> ...
>
> 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.

Very, very good!

As I understand, the work on this patch now in progress and it not in 
commitfest.
Please explain what features of schema variables I can review now.

 From first post of this thread the syntax of the CREATE VARIABLE command:
CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
   [ DEFAULT expression ] [[NOT] NULL]
   [ ON TRANSACTION END { RESET | DROP } ]
   [ { VOLATILE | STABLE } ];

But in psql I see only:
\h create variable
Command:     CREATE VARIABLE
Description: define a new permissioned typed schema variable
Syntax:
CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]

I can include DEFAULT clause in CREATE VARIABLE command, but the value 
not used:
postgres=# create variable i int default 0;
CREATE VARIABLE
postgres=# select i;
  i
---

(1 row)

postgres=# \d+ i
  schema variable "public.i"
  Column |  Type   | Storage
--------+---------+---------
  i      | integer | plain


BTW, I found an error in handling of table aliases:

postgres=# create variable x text;
CREATE VARIABLE
postgres=# select * from pg_class AS x where x.relname = 'x';
ERROR:  type text is not composite

It thinks that x.relname is an attribute of x variable instead of an 
alias for pg_class table.


-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--------------FFA0A37FC97E557C3F4338B0
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <tt><br>
    </tt>
    <div class="moz-cite-prefix"><tt>On 12.03.2018 09:54, Pavel Stehule
        wrote:</tt><tt><br>
      </tt></div>
    <blockquote type="cite"
cite="mid:CAFj8pRDLBtPM9PAVx-VFtLyZJktKL5R4hX4V4O4KvcyjbOPRbw@mail.gmail.com">
      <div dir="ltr"><tt><br>
        </tt>
        <div class="gmail_extra">
          <div class="gmail_quote"><tt>2018-03-12 7:49 GMT+01:00 Pavel
              Luzanov </tt><tt><span dir="ltr">&lt;<a
                  href="mailto:p.luzanov@postgrespro.ru" target="_blank"
                  moz-do-not-send="true">p.luzanov@postgrespro.ru</a>&gt;</span></tt><tt>:</tt><tt><br>
            </tt>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">
              <div><tt><br>
                </tt><tt> Is there any chances that it will work on
                  replicas?</tt></div>
            </blockquote>
            <tt>...</tt><tt><br>
            </tt>
            <div><tt><br>
              </tt></div>
            <div><tt>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.</tt><tt><br>
              </tt>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <tt><br>
    </tt><tt>Very, very good! </tt><tt><br>
    </tt><tt><br>
      As I understand, the work on this patch now in progress and it not
      in commitfest.<br>
    </tt><tt>Please explain what features of schema variables I can
      review now. </tt><tt><br>
      <br>
      From first post of this thread the syntax of the CREATE VARIABLE
      command:</tt><tt><br>
    </tt><tt>CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type </tt><tt><br>
    </tt><tt>  [ DEFAULT expression ] [[NOT] NULL]</tt>
    <div><tt>  [ ON TRANSACTION END { RESET | DROP } ]</tt></div>
    <div><tt>  [ { VOLATILE | STABLE } ];<br>
        <br>
        But in psql I see only:<br>
        \h create variable <br>
        Command:     CREATE VARIABLE<br>
        Description: define a new permissioned typed schema variable<br>
        Syntax:<br>
        CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]<br>
        <br>
        I can include DEFAULT clause in CREATE VARIABLE command, but the
        value not used:<br>
      </tt><tt></tt></div>
    <div><tt>postgres=# create variable i int default 0;<br>
        CREATE VARIABLE<br>
        postgres=# select i;<br>
         i <br>
        ---<br>
          <br>
        (1 row)<br>
        <br>
        postgres=# \d+ i<br>
         schema variable "public.i"<br>
         Column |  Type   | Storage <br>
        --------+---------+---------<br>
         i      | integer | plain<br>
        <br>
        <br>
        BTW, I found an error in handling of table aliases:<br>
        <br>
        postgres=# create variable x text;<br>
        CREATE VARIABLE<br>
        postgres=# select * from pg_class AS x where x.relname = 'x';<br>
        ERROR:  type text is not composite<br>
        <br>
        It thinks that x.relname is an attribute of x variable instead
        of an alias for pg_class table.<br>
        <br>
        <br>
      </tt></div>
    <pre class="moz-signature" cols="72">-----
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>
    <tt>
    </tt>
  </body>
</html>

--------------FFA0A37FC97E557C3F4338B0--