Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j9rDp-0006Nl-Nk for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Mar 2020 14:11:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j9rDo-0006PS-3u for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Mar 2020 14:11:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j9rDn-0006PL-GW for pgsql-hackers@lists.postgresql.org; Thu, 05 Mar 2020 14:11:31 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j9rDk-0007q0-Nn for pgsql-hackers@lists.postgresql.org; Thu, 05 Mar 2020 14:11:30 +0000 Received: by mail-ed1-x52c.google.com with SMTP id h62so6899181edd.12 for ; Thu, 05 Mar 2020 06:11:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:reply-to:from:date:message-id :subject:to:cc; bh=PMEzyJhrCT1mqWMksiQBwUPT2MpLSzM5sEh8AytmOxI=; b=En8ddVNg9y98fby7Pxb9LjEzBeRUQkmdx4+xwxIv8qM5FhEhzzngA5KIQeMfAvkwv2 SC0wKe+3MVoX/b2Bvc+DONJAV92n5vAwQfw+ZO14IzE/6cfjJHa7C+QG9tjP5dCQ4KHS j8e8S43vsyGMiux94d02PrE3O2HLnT6Au45K/MxpXsrzInT1cgZVliGsV1uapwrZKiTS sTYjRPsVtISUoV4UdCKlmQdE76UFMpT5vd/n79xZjHjKjFdk+SFtwQYwp/oHZLXFo+88 AXcnSeC7Qv7Y3TeTKJlw4Zv6ATyaQNkMz39NgipGwufw1WLgusEQRtCWFIgJiRRcRmPc XQcQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:reply-to :from:date:message-id:subject:to:cc; bh=PMEzyJhrCT1mqWMksiQBwUPT2MpLSzM5sEh8AytmOxI=; b=HOL0Bw2/1Ut5Fh+WPJp1C4JWxFipIk8FT6JlXph3d6uysy5mwLn/0g8G57BY+ZSmAf dly53Gz9+/4tK6MvNHWx2R914DrAho2Q7wHRAs6dq+2lk+9ne2ttRZJ50HhGy/95Rmsf fYTsG4Am+IrBUUM3Fg2NBMOBZn7V7v6SKb0amdJIt0Bh26IQeuMds7KaD0U3lXj2Xe4p 8xo/M4IcVf937MiaqjDzLgRT6bdyPBPbp799KWA6FMfWjU8JwAEJyP+BcR0842dHJ9m1 lj08fazWR1tndqRJ9+VoVOnFp1oeP1NimaBvNDchyHKw1IfxOXtIl3yIS+nRDVcWOT28 nQNg== X-Gm-Message-State: ANhLgQ1rdAU3VKY+PjvFe8ztc5JLLTnqOmqaD7AqRC7lg2wm8HF9dwNx obNgdnI7jTbWTp9TWQnac3lNtUtWpLmumdFIka8= X-Google-Smtp-Source: ADFU+vuK5xr3aMjEbq8ZDZq2kNzNWDjsWcE/XxJyQOqmYUOKwpYs/qy48XkggROQi92PBIk2jG6l7oAJnzpFGnjk8t8= X-Received: by 2002:a17:906:14d6:: with SMTP id y22mr7595371ejc.289.1583417486285; Thu, 05 Mar 2020 06:11:26 -0800 (PST) MIME-Version: 1.0 References: <158272883575.1651.2316685587547965508.pgcf@coridan.postgresql.org> In-Reply-To: Reply-To: asifr.rehman@gmail.com From: Asif Rehman Date: Thu, 5 Mar 2020 19:10:49 +0500 Message-ID: Subject: Re: proposal: schema variables To: Pavel Stehule Cc: remi duval , PostgreSQL Hackers Content-Type: multipart/mixed; boundary="000000000000cd407405a01c1c40" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000cd407405a01c1c40 Content-Type: multipart/alternative; boundary="000000000000cd407205a01c1c3e" --000000000000cd407205a01c1c3e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Feb 29, 2020 at 2:10 PM Pavel Stehule wrote: > > > p=C3=A1 28. 2. 2020 v 16:30 odes=C3=ADlatel Pavel Stehule > napsal: > >> >> >> =C4=8Dt 27. 2. 2020 v 15:37 odes=C3=ADlatel Pavel Stehule >> napsal: >> >>> >>> Hi >>> >>> >>>> 3) Any way to define CONSTANTs ? >>>> We already talked a bit about this subject and also Gilles Darold >>>> introduces it in this mailing-list topic but I'd like to insist on it. >>>> I think it would be nice to have a way to say that a variable should >>>> not be changed once defined. >>>> Maybe it's hard to implement and can be implemented later, but I just >>>> want to know if this concern is open. >>>> >>> >>> I played little bit with it and I didn't find any nice solution, but >>> maybe I found the solution. I had ideas about some variants, but almost= all >>> time I had a problem with parser's shifts because all potential keyword= s >>> are not reserved. >>> >>> last variant, but maybe best is using keyword WITH >>> >>> So the syntax can looks like >>> >>> CREATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT >>> expression ] [ WITH [ OPTIONS ] '(' ... ')' ] ] >>> >>> What do you think about this syntax? It doesn't need any new keyword, >>> and it easy to enhance it. >>> >>> CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT); >>> >> >> After some more thinking and because in other patch I support syntax >> CREATE TRANSACTION VARIABLE ... I change my opinion and implemented supp= ort >> for >> syntax CREATE IMMUTABLE VARIABLE for define constants. >> > > second try to fix pg_dump > > Regards > > Pavel > > >> >> See attached patch >> >> Regards >> >> Pavel >> >> >>> >>> ? >>> >>> Regards >>> >>> Pavel >>> >>> >>> Hi Pavel, I have been reviewing the latest patch (schema-variables-20200229.patch.gz) and here are few comments: 1- There is a compilation error, when compiled with --with-llvm enabled on CentOS 7. llvmjit_expr.c: In function =E2=80=98llvm_compile_expr=E2=80=99: llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default] build_EvalXFunc(b, mod, "ExecEvalParamVariable", ^ llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(anonymou= s)[0]=E2=80=99) [enabled by default] llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default] llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(anonymou= s)[0]=E2=80=99) [enabled by default] llvmjit_expr.c:1090:5: warning: initialization from incompatible pointer type [enabled by default] llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(anonymou= s)[0]=E2=80=99) [enabled by default] llvmjit_expr.c:1090:5: warning: passing argument 5 of =E2=80=98build_EvalXF= uncInt=E2=80=99 from incompatible pointer type [enabled by default] llvmjit_expr.c:60:21: note: expected =E2=80=98struct ExprEvalStep *=E2=80= =99 but argument is of type =E2=80=98LLVMValueRef=E2=80=99 static LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod= , ^ llvmjit_expr.c:1092:29: error: =E2=80=98i=E2=80=99 undeclared (first use in= this function) LLVMBuildBr(b, opblocks[i + 1]); ^ llvmjit_expr.c:1092:29: note: each undeclared identifier is reported only once for each function it appears in make[2]: *** [llvmjit_expr.o] Error 1 After looking into it, it turns out that: - parameter order was incorrect in build_EvalXFunc() - LLVMBuildBr() is using the undeclared variable 'i' whereas it should be using 'opno'. 2- Similarly, If the default expression is referencing a function or object= , dependency should be marked, so if the function is not dropped silently. otherwise, a cache lookup error will come. postgres=3D# create or replace function foofunc() returns timestamp as $$ begin return now(); end; $$ language plpgsql; CREATE FUNCTION postgres=3D# create schema test; CREATE SCHEMA postgres=3D# create variable test.v1 as timestamp default foofunc(); CREATE VARIABLE postgres=3D# drop function foofunc(); DROP FUNCTION postgres=3D# select test.v1; ERROR: cache lookup failed for function 16437 3- Variable DEFAULT expression is apparently being evaluated at the time of first access. whereas I think that It should be at the time of variable creation. consider the following example: postgres=3D# create variable test.v2 as timestamp default now(); CREATE VARIABLE postgres=3D# select now(); now ------------------------------- 2020-03-05 12:13:29.775373+00 (1 row) postgres=3D# select test.v2; v2 ---------------------------- 2020-03-05 12:13:37.192317 -- I was expecting this to be earlier than the above timestamp. (1 row) postgres=3D# select test.v2; v2 ---------------------------- 2020-03-05 12:13:37.192317 (1 row) postgres=3D# let test.v2 =3D default; LET postgres=3D# select test.v2; v2 ---------------------------- 2020-03-05 12:14:07.538615 (1 row) To continue my testing of the patch I made few fixes for the above-mentione= d comments. The patch for those changes is attached if it could be of any use= . -- Asif Rehman Highgo Software (Canada/China/Pakistan) URL : www.highgo.ca --000000000000cd407205a01c1c3e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On = Sat, Feb 29, 2020 at 2:10 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:


p=C3=A1 28. 2. 2020 v=C2=A016:30= odes=C3=ADlatel Pavel Stehule <pavel.stehule@gmail.com> napsal:


=C4=8Dt 27. 2. 2020 v=C2= =A015:37 odes=C3=ADlatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

Hi


3) Any way to define CONSTANTs ?
We already talked a bit about this subject and also Gilles Darold introduce= s it in this mailing-list topic but I'd like to insist on it.
I think it would be nice to have a way to say that a variable should not be= changed once defined.
Maybe it's hard to implement and can be implemented later, but I just w= ant to know if this concern is open.

I playe= d little bit with it and I didn't find any nice solution, but maybe I f= ound the solution. I had ideas about some variants, but almost all time I h= ad a problem with parser's shifts because all potential keywords are no= t reserved.

last variant, but maybe best is using keyword WITH

So the syntax can looks = like

C= REATE [ TEMP ] VARIABLE varname [ AS ] type [ NOT NULL ] [ DEFAULT expressi= on ] [ WITH [ OPTIONS ] '(' ... ')' ] ]

What do you think ab= out this syntax? It doesn't need any new keyword, and it easy to enhanc= e it.

= CREATE VARIABLE foo AS int DEFAULT 10 WITH OPTIONS ( CONSTANT);

After some more thinking a= nd because in other patch I support syntax CREATE TRANSACTION VARIABLE ... = I change my opinion and implemented support for=C2=A0
syntax CREA= TE IMMUTABLE VARIABLE for define constants.
<= div>
second try to fix pg_dump

Regar= ds

Pavel
=C2=A0

See attac= hed patch

Regards

Pavel
=C2=A0

?

Regards

Pavel



Hi Pavel,

I have been reviewing the latest pa= tch (schema-variables-20200229.patch.gz)
and here are few comment= s:

1- There is a compilation error, when compiled = with --with-llvm enabled on
CentOS 7.

<= /div>
=
llvmjit_expr.c: In function =E2=80=98llvm_compile_expr= =E2=80=99:
llvmjit_expr.c:1090:5= : warning: initialization from incompatible pointer type [enabled by defaul= t]
=C2=A0 =C2=A0 =C2=A0build_Eva= lXFunc(b, mod, "ExecEvalParamVariable",
=C2=A0 =C2=A0 =C2=A0^
llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(= anonymous)[0]=E2=80=99) [enabled by default]
llvmjit_expr.c:1090:5: warning: initialization from incompatib= le pointer type [enabled by default]
=
llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(ano= nymous)[0]=E2=80=99) [enabled by default]
=
llvmjit_expr.c:1090:5: warning: initialization from incompatible = pointer type [enabled by default]
llvmjit_expr.c:1090:5: warning: (near initialization for =E2=80=98(anonym= ous)[0]=E2=80=99) [enabled by default]
llvmjit_expr.c:1090:5: warning: passing argument 5 of =E2=80=98build= _EvalXFuncInt=E2=80=99 from incompatible pointer type [enabled by default]<= /div>
llvmjit_expr.c:60:21: note: expe= cted =E2=80=98struct ExprEvalStep *=E2=80=99 but argument is of type =E2=80= =98LLVMValueRef=E2=80=99
=C2=A0s= tatic LLVMValueRef build_EvalXFuncInt(LLVMBuilderRef b, LLVMModuleRef mod,<= /div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
=
llvmjit_expr.c:1092:29: error: =E2=80=98i=E2=80=99 undeclare= d (first use in this function)
= =C2=A0 =C2=A0 =C2=A0LLVMBuildBr(b, opblocks[i + 1]);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
llvmjit_expr.c:1092:29: note: each undeclared identif= ier is reported only once for each function it appears in
=
make[2]: *** [llvmjit_expr.o] Error 1
=


After looking into it, it turns out that:
- parameter order was incorrect in build_EvalXFunc()
- LLVMBuild= Br() is using the undeclared variable 'i' whereas it should be
using 'opno'.


2- Simi= larly, If the default expression is referencing a function or object,
=
dependency should be marked, so if the function is not dropped silentl= y.
otherwise, a cache lookup error will come.

postgres=3D# create or replace function foofunc= () returns timestamp as $$ begin return now(); end; $$ language plpgsql;
CREATE FUNCTION
<= /div>
postgres=3D# create schema test;
=
CREATE SCHEMA
postgres=3D# create variable test.v1 as timestamp default foofunc()= ;
CREATE VARIABLE
postgres=3D# drop function foofunc();
DROP FUNCTION
postgres=3D# select test.v1;
<= div>
ERROR: =C2=A0cache lookup failed for function 16437

=

3- Variable DEFAULT expression is apparently bein= g evaluated at the time of
first access. whereas I think that It = should be at the time of variable
creation. consider the followin= g example:

postgres=3D# crea= te variable test.v2 as timestamp default now();
CREATE VARIABLE
= postgres=3D# select now();
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 now =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
-------= ------------------------
=C2=A02= 020-03-05 12:13:29.775373+00
(1 = row)
postgres=3D# select test.v2= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0v2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
----------------------------
=C2=A02020-03-05 12:13:37.192317 -- I was= expecting this to be earlier than the above timestamp.
(1 row)
=
postgres=3D# select test.v2= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0v2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
----------------------------
=C2=A02020-03-05 12:13:37.192317
<= /div>
(1 row)
postgres=3D# let test.v2 =3D default;
<= div>
LET
postgres= =3D# select test.v2;
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0=C2=A0
------------------= ----------
=C2=A02020-03-05 12:1= 4:07.538615
(1 row)
<= /div>

To continue my testing of the patch I made few fixes for the above-= mentioned
comments. The patch for those changes is attached if it= could be of any use.

--
Asif Rehman
Highgo Soft= ware (Canada/China/Pakistan)
URL : www.highgo.ca

--000000000000cd407205a01c1c3e-- --000000000000cd407405a01c1c40 Content-Type: application/octet-stream; name="sv-fixes.patch" Content-Disposition: attachment; filename="sv-fixes.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_k7etrrq90 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvcGdfdmFyaWFibGUuYyBiL3NyYy9iYWNr ZW5kL2NhdGFsb2cvcGdfdmFyaWFibGUuYwppbmRleCBmMzJkMDQ5YmQ1OS4uYmRlZTEyMWNiNTcg MTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvcGdfdmFyaWFibGUuYworKysgYi9zcmMv YmFja2VuZC9jYXRhbG9nL3BnX3ZhcmlhYmxlLmMKQEAgLTM1MCw2ICszNTAsMTEgQEAgVmFyaWFi bGVDcmVhdGUoY29uc3QgY2hhciAqdmFyTmFtZSwKIAlyZWZlcmVuY2VkLm9iamVjdFN1YklkID0g MDsKIAlyZWNvcmREZXBlbmRlbmN5T24oJm15c2VsZiwgJnJlZmVyZW5jZWQsIERFUEVOREVOQ1lf Tk9STUFMKTsKIAorCS8qIGRlcGVuZGVuY3kgb24gZGVmYXVsdCBleHByICovCisJaWYgKHZhckRl ZmV4cHIpCisJCXJlY29yZERlcGVuZGVuY3lPbkV4cHIoJm15c2VsZiwgKE5vZGUgKikgdmFyRGVm ZXhwciwKKwkJCQkJCQkgICBOSUwsIERFUEVOREVOQ1lfTk9STUFMKTsKKwogCS8qIGRlcGVuZGVu Y3kgb24gYW55IHJvbGVzIG1lbnRpb25lZCBpbiBBQ0wgKi8KIAlpZiAodmFyYWNsICE9IE5VTEwp CiAJewpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvaml0L2xsdm0vbGx2bWppdF9leHByLmMgYi9z cmMvYmFja2VuZC9qaXQvbGx2bS9sbHZtaml0X2V4cHIuYwppbmRleCBkZGU2YTVhY2JkYi4uMDhj ZjdlNjNiYmYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2ppdC9sbHZtL2xsdm1qaXRfZXhwci5j CisrKyBiL3NyYy9iYWNrZW5kL2ppdC9sbHZtL2xsdm1qaXRfZXhwci5jCkBAIC0xMDg4LDggKzEw ODgsOCBAQCBsbHZtX2NvbXBpbGVfZXhwcihFeHByU3RhdGUgKnN0YXRlKQogCiAJCQljYXNlIEVF T1BfUEFSQU1fVkFSSUFCTEU6CiAJCQkJYnVpbGRfRXZhbFhGdW5jKGIsIG1vZCwgIkV4ZWNFdmFs UGFyYW1WYXJpYWJsZSIsCi0JCQkJCQkJCXZfc3RhdGUsIHZfZWNvbnRleHQsIG9wKTsKLQkJCQlM TFZNQnVpbGRCcihiLCBvcGJsb2Nrc1tpICsgMV0pOworCQkJCQkJCQl2X3N0YXRlLCBvcCwgdl9l Y29udGV4dCk7CisJCQkJTExWTUJ1aWxkQnIoYiwgb3BibG9ja3Nbb3BubyArIDFdKTsKIAkJCQli cmVhazsKIAogCQkJY2FzZSBFRU9QX1BBUkFNX0NBTExCQUNLOgo= --000000000000cd407405a01c1c40--