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 1evQ5k-0003We-LK for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 16:14:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1evQ5j-0004be-CL for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Mar 2018 16:14:27 +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.89) (envelope-from ) id 1evQ5i-0004bU-T2 for pgsql-hackers@lists.postgresql.org; Mon, 12 Mar 2018 16:14:27 +0000 Received: from mail-wm0-x243.google.com ([2a00:1450:400c:c09::243]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1evQ5f-0007FI-1o for pgsql-hackers@postgresql.org; Mon, 12 Mar 2018 16:14:25 +0000 Received: by mail-wm0-x243.google.com with SMTP id e194so17681661wmd.3 for ; Mon, 12 Mar 2018 09:14:22 -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=2Tkr5ClDylWYS5Z0qhV3eCJksXRzHbAh+8Wg0djRKLA=; b=lKtGb2r/kywUM9qgvovO/uO5ZV8jMmeRsSJRaa1i549Qc/+ba+o3Rb4dComhN+NrA4 3f4V0pNtx3Ph+kBRryyh0lGDwVny/0I9yTNQtANeT/0VWQnt/7H0AAIncsIJClfvnwfT UjL+3Yuil6OrMR/pVtuvvaKY590wixo6Wg0FW17nPDCwgMBTE9pN9z4SGRiXmApZoQSr 3FG+H5Lp5+tz9Hf5SrFn3kYOKBiFJSxBF3K5c3iXxvYuSSWB8qJUvE/cvGF3DR+CBjmt uGwLc+GM74CKQA56GTXdkDG9K9byd+pfmD+FQ6eTzfYZgOdI3isK3SucYltP5g2s8Vyo HZNw== 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=2Tkr5ClDylWYS5Z0qhV3eCJksXRzHbAh+8Wg0djRKLA=; b=Xdr+BlZ9jc7+iv50wby3kPGuPQz+QzGqGheRN4SVOwPh2syfNvOUxhNEiz4hUmL52A AVev2FzEk4xCCpVc3a1cGBgfmTGqOGTkLFzza3vV54do/mT/ZswQRcq3BycfyrTAD7Yb pIBDCLqOM3iJph9jr3UAWh7rQwmZdCxa5TC74azKvnddiREHwNvdkYPKHevj0g24FVcc Kt292YtjN4jTBWWAJt1LBxvlx+0SAaAU8Ly7EDwS56A+3mTWEEjKaZGHUZg9E57vYzJQ Vhlra3b4cpRlfuQU0POWXsfLcAvXnxga6Tw/gDBBK+Y2bEAlnUE29eNkdQbyuExt8kz5 gGIg== X-Gm-Message-State: AElRT7HDS56kaI3VfZkoAPS8sEIyXO5lVHdoZhizlESVG8gJmzq7oCsU lzFQlhlSymrfR8gEO24SE3JNj+7q6ojRBludmN4= X-Google-Smtp-Source: AG47ELsuHnysy3xeFlktpbR3INCVvaAH7Yd2dogIBK4NTbTVoQDVoHnd1gdNGBAYUfZsnhApaVWpgCax76Py9kV91nU= X-Received: by 10.28.23.143 with SMTP id 137mr6471127wmx.153.1520871261213; Mon, 12 Mar 2018 09:14:21 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.179.65 with HTTP; Mon, 12 Mar 2018 09:13:40 -0700 (PDT) In-Reply-To: References: <623395617.20171113141500@gf.microolap.com> From: Pavel Stehule Date: Mon, 12 Mar 2018 17:13:40 +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="001a114714a846886f0567396f47" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a114714a846886f0567396f47 Content-Type: text/plain; charset="UTF-8" 2018-03-12 16:38 GMT+01:00 Pavel Luzanov : > > On 12.03.2018 09:54, Pavel Stehule wrote: > > > 2018-03-12 7:49 GMT+01:00 Pavel Luzanov : > >> >> 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 } ]; > Now, it is too early for review - it is in development. Some features are not implemented yet - DEFAULTs, ON TRANSACTION END .., others has not sense (what I know now VOLATILE, STABLE). Schema variables are passed as parameters to query, so the behave is like any other params - it is STABLE only. > > 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 > > defaults are not implemented yet > > 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. > > It is not well handled collision. This should be detected and prohibited. In this case, because x is scalar, then x.xx has not sense, and then it should not be handled like variable. So the current design is not too practical - it generates more collisions than it is necessary and still, there are some errors. Now, there is one important question - storage - Postgres stores all objects to files - only memory storage is not designed yet. This is part, where I need a help. Regards Pavel > > ----- > Pavel Luzanov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > --001a114714a846886f0567396f47 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2018-03-12 16:38 GMT+01:00 Pavel Luzanov <p.luzanov@postgrespr= o.ru>:
=20 =20 =20

On 12.03.2018 0= 9:54, Pavel Stehule wrote:
2018-03-12 7:49 G= MT+01:00 Pavel Luzanov <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 <= br> =C2=A0 [ DEFAULT expression ] [[NOT] NULL]
=C2=A0 [ ON TRANSACTION END { RESET | DROP } ]
=C2=A0 [ { VOLATILE | STABLE } ];
<= /blockquote>

Now, it is too early for review - it is in = development. Some features are not implemented yet - DEFAULTs, ON TRANSACTI= ON END .., others has not sense (what I know now VOLATILE, STABLE). Schema = variables are passed as parameters to query, so the behave is like any othe= r params - it is STABLE only.
=C2=A0

But in psql I see only:
\h create variable
Command:=C2=A0=C2=A0=C2=A0=C2=A0 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=3D# create variable i int default 0;
CREATE VARIABLE
postgres=3D# select i;
=C2=A0i
---
=C2=A0
(1 row)

postgres=3D# \d+ i
=C2=A0schema variable "public.i"
=C2=A0Column |=C2=A0 Type=C2=A0=C2=A0 | Storage
--------+---------+---------
=C2=A0i=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | integer | plain


defaults are = not implemented yet
=C2=A0

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

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

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


It is not wel= l handled collision. This should be detected and prohibited. In this case, = because x is scalar, then x.xx has not sense, and then it should not be han= dled like variable. So the current design is not too practical - it generat= es more collisions than it is necessary and still, there are some errors.
Now, there is one important question - storage - Postgres = stores all objects to files - only memory storage is not designed yet. This= is part, where I need a help.

Regards

Pavel
=C2=A0

-----
Pavel Luzanov
Postgres Professional: http://www.postgr=
espro.com
The Russian Postgres Company

--001a114714a846886f0567396f47--