Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fyHo0-0008Qb-C7 for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Sep 2018 14:32:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fyHny-0002ns-Sd for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Sep 2018 14:32:14 +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 1fyHkd-0000V1-KX for pgsql-hackers@lists.postgresql.org; Fri, 07 Sep 2018 14:28:47 +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 1fyHkY-0004aJ-00 for pgsql-hackers@lists.postgresql.org; Fri, 07 Sep 2018 14:28:45 +0000 Received: by mail-wm0-x243.google.com with SMTP id j25-v6so19323128wmc.1 for ; Fri, 07 Sep 2018 07:28:41 -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=DU4NMKOTW8lyHzQeFeYDpH1UrEaWfvEUVNhhntaPND0=; b=StznLp/YIo2/1BUKsuFB/32gR6/s8cTePj54kFKbgsnagbWWNTR6PQadHCmtiqElNZ dUu9i9P6t7hLFMy79oMs0R3IFF+EY4BK6mhDL5K3pQOLpHmWMzAvJ8l3OnbLc4Yzsalv RW0oW2J3Rc1+uYRKab+vnIRMzXTZNvK6zkeMHPbJSTuTU/bw11XaB1z+41iTzk88thOr HszxP/MJ/3dhqDVZUBKXrntoHuYoASr2FI/Kq9h26oCJNAAQphl5c1JdNRDimbnf68pP X2Z2Rs37uSAHTjzrF+mU51MWw6Oj2HPmr8YPmGqQs7A3A2Tg+B1fmDwSbBFCFWrVHc6x 8wZA== 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=DU4NMKOTW8lyHzQeFeYDpH1UrEaWfvEUVNhhntaPND0=; b=rZyYPJKWel2IPcy9u3mr1U1yqkxqW5PbLVr0twSyfnEkVTzok2PdqvbmCGDA/cEVwX IhMz+HoxIEhvhFkEyMos9Nox2lX/xauGVfApB8Se9RlziikuAyYvqS9NUxdTX11UVUgo f3yJGCuqLzDRh6y9QHoIK7Kb0wnqcMK7W+Kwa6ue49Idib2CQ0pFLSN8JcEFUI8Hvik6 1w9X6n/DX6tYWSvFYox//C07QDKzHSj6sw9u3ynbWKICVzaUEM4IwfhqshBA0gI/BV98 mOk9h+izejUxlMYKl6+aS+Rf594iJrceVp7ztHurfBMnH86FREOlTvk+79mOQFFq2gZT CK+g== X-Gm-Message-State: APzg51ABrujUcZi/tuh4syfuQ1uMe8hJP0e4VpX/2C/MaUqOLLVF1CIJ GAFy5ecVTBlpkm9I14ZQJYsHC4QoKUqJrTzH5s4= X-Google-Smtp-Source: ANB0VdZAdihZoiCpZPcSUP8Rnv4OjHXNZhgQztdpr1/+mEJIYevxR3avfiZGxqAt1I8E4GiFbOi+/bkK1ncz+eJ8HXo= X-Received: by 2002:a1c:e70b:: with SMTP id e11-v6mr5189743wmh.22.1536330521001; Fri, 07 Sep 2018 07:28:41 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:adf:978e:0:0:0:0:0 with HTTP; Fri, 7 Sep 2018 07:28:00 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Fri, 7 Sep 2018 16:28:00 +0200 Message-ID: Subject: Re: [HACKERS] proposal: schema variables To: Fabien COELHO Cc: Dean Rasheed , Gilles Darold , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000f643ed057548d2f4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000f643ed057548d2f4 Content-Type: text/plain; charset="UTF-8" 2018-09-07 14:34 GMT+02:00 Fabien COELHO : > > Hello Pavel, > > here is updated patch - I wrote some transactional support >> >> I am not sure how these new features are understandable and if these >> features does it better or not. >> > > There are possibility to reset to default value when >> >> a) any transaction is finished - the scope of value is limited by >> transaction >> >> CREATE VARIABLE foo int ON TRANSACTION END RESET; >> > > With this option I understand that it is a "within a transactionnal" > variable, i.e. when the transaction ends, whether commit or rollback, the > variable is reset to a default variable. It is not really a "session" > variable anymore, each transaction has its own value. > yes, the correct name should be "schema variable with transaction scope". I think it can be useful like short life global variable. These variables can works like transaction caches. > -- begin session > -- foo has default value, eg NULL > BEGIN; > LET foo = 1; > COMMIT/ROLLBACK; > -- foo has default value again, NULL > > b) when transaction finished by rollback >> >> CREATE VARIABLE foo int ON ROLLBACK RESET >> > > That is a little bit safer and you are back to a SESSION-scope variable, > which is reset to the default value if the (any) transaction fails? > > -- begin session > -- foo has default value, eg NULL > BEGIN; > LET foo = 1; > COMMIT; > -- foo has value 1 > BEGIN; > -- foo has value 1... > ROLLBACK; > -- foo has value NULL > > c) A more logical (from a transactional point of view - but not necessary > simple to implement, I do not know) feature/variant would be to reset the > value to the one it had at the beginning of the transaction, which is not > necessarily the default. > > -- begin session > -- foo has default value, eg NULL > BEGIN; > LET foo = 1; > COMMIT; > -- foo has value 1 > BEGIN; > LET foo = 2; (*) > -- foo has value 2 > ROLLBACK; > -- foo has value 1 back, change (*) has been reverted > > Now, when I am thinking about it, the @b is simple, but not too practical - >> when some fails, then we lost a value (any transaction inside session can >> fails). >> > > Indeed. > > The @a has sense - the behave is global value (what is not possible >> in Postgres now), but this value is destroyed by any unhandled exceptions, >> and it cleaned on transaction end. The @b is just for information and for >> discussion, but I'll remove it - because it is obscure. >> > > Indeed. > > The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is >> little bit unclean, because it has semantic "on transaction end", but if I >> didn't implement @b, then ON COMMIT syntax can be used. >> > > I was more arguing on the third (c) option, i.e. on rollback the value is > reverted to its value at the beginning of the rollbacked transaction. > > At the minimum, ISTM that option (b) is enough to implement the audit > pattern, but it would mean that any session which has a rollback, for any > reason (deadlock, serialization...), would have to be reinitialized, which > would be a drawback. > > The to options could be non-transactional session variables "ON ROLLBACK > DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON > ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c). > @b is hardly understandable for not trained people, because any rollback in session does reset. But people expecting @c, or some near @c. I understand so you talked about @c. Now I think so it is possible to implement, but it is not trivial. The transactional behave have to calculate not only with transactions, but with SAVEPOINTS and ROLLBACK TO savepoints. On second hand, the implementation will be relatively compact. I'll hold it in my memory, but there are harder issues (support for parallelism). Regards Pavel > -- > Fabien. > > --000000000000f643ed057548d2f4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2018-09-07 14:34 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr&g= t;:

Hello Pavel,

here is updated patch - I wrote some transactional support

I am not sure how these new features are understandable and if these
features does it better or not.

There are possibility to reset to default value when

a) any transaction is finished - the scope of value is limited by
transaction

CREATE VARIABLE foo int ON TRANSACTION END RESET;

 With this option I understand that it is a "within a transactionnal&qu= ot; variable, i.e. when the transaction ends, whether commit or rollback, t= he variable is reset to a default variable. It is not really a "sessio= n" variable anymore, each transaction has its own value.

yes, the correct name should be "schema variabl= e with transaction scope". I think it can be useful like short life gl= obal variable. These variables can works like transaction caches.
=


=C2=A0-- begin session
=C2=A0-- foo has default value, eg NULL
=C2=A0BEGIN;
=C2=A0 =C2=A0 LET foo =3D 1;
=C2=A0COMMIT/ROLLBACK;
=C2=A0-- foo has default value again, NULL

b) when transaction finished by rollback

CREATE VARIABLE foo int ON ROLLBACK RESET

 That is a little bit safer and you are back to a SESSION-scope variable, wh= ich is reset to the default value if the (any) transaction fails?

=C2=A0 -- begin session
=C2=A0 -- foo has default value, eg NULL
=C2=A0 BEGIN;
=C2=A0 =C2=A0 LET foo =3D 1;
=C2=A0 COMMIT;
=C2=A0 -- foo has value 1
=C2=A0 BEGIN;
=C2=A0 =C2=A0 -- foo has value 1...
=C2=A0 ROLLBACK;
=C2=A0 -- foo has value NULL

c) A more logical (from a transactional point of view - but not necessary s= imple to implement, I do not know) feature/variant would be to reset the va= lue to the one it had at the beginning of the transaction, which is not nec= essarily the default.

=C2=A0 -- begin session
=C2=A0 -- foo has default value, eg NULL
=C2=A0 BEGIN;
=C2=A0 =C2=A0 LET foo =3D 1;
=C2=A0 COMMIT;
=C2=A0 -- foo has value 1
=C2=A0 BEGIN;
=C2=A0 =C2=A0 LET foo =3D 2; (*)
=C2=A0 =C2=A0 -- foo has value 2
=C2=A0 ROLLBACK;
=C2=A0 -- foo has value 1 back, change (*) has been reverted

Now, when I am thinking about it, the @b is simple, but not too practical -=
when some fails, then we lost a value (any transaction inside session can fails).

 Indeed.

The @a has sense - the behave is global value (what is not possible
in Postgres now), but this value is destroyed by any unhandled exceptions,<= br> and it cleaned on transaction end. The @b is just for information and for discussion, but I'll remove it - because it is obscure.

 Indeed.

The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is little bit unclean, because it has semantic "on transaction end",= but if I
didn't implement @b, then ON COMMIT syntax can be used.

 I was more arguing on the third (c) option, i.e. on rollback the value is r= everted to its value at the beginning of the rollbacked transaction.

At the minimum, ISTM that option (b) is enough to implement the audit patte= rn, but it would mean that any session which has a rollback, for any reason= (deadlock, serialization...), would have to be reinitialized, which would = be a drawback.

The to options could be non-transactional session variables "ON ROLLBA= CK DO NOT RESET/DO NOTHING", and somehow transactional session variabl= es "ON ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET = TO INITIAL" (c).

@b is hardly understandable for= not trained people, because any rollback in session does reset. But people= expecting @c, or some near @c.

I understand so yo= u talked about @c. Now I think so it is possible to implement, but it is no= t trivial. The transactional behave have to calculate not only with transac= tions, but with SAVEPOINTS and ROLLBACK TO savepoints. On second hand, the = implementation will be relatively compact.

I&#= 39;ll hold it in my memory, but there are harder issues (support for parall= elism).

Regards

Pave= l



--
Fabien.


--000000000000f643ed057548d2f4--