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 1g3Yib-0002Qb-I5 for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Sep 2018 03:36:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g3YiZ-0003Z2-Dh for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Sep 2018 03:36:27 +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 1g3YiZ-0003Yv-3R for pgsql-hackers@lists.postgresql.org; Sat, 22 Sep 2018 03:36:27 +0000 Received: from mail-wr1-x42a.google.com ([2a00:1450:4864:20::42a]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g3YiV-0002hl-Kh for pgsql-hackers@lists.postgresql.org; Sat, 22 Sep 2018 03:36:26 +0000 Received: by mail-wr1-x42a.google.com with SMTP id j15-v6so10463366wrt.8 for ; Fri, 21 Sep 2018 20:36:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=uYGFlaKfWI6AVeMfHAujHvB5ti99J1OaqA51xp+z/kU=; b=aVg3yzoj8ONP/WJXtQf32SI3BEp6krBZ3hf19NlDlDokGjk1r851AUBMt1L+grHILq +dKwO1m5Tk+cppzaGKG5I0jAWmlN1mFCJ5IP7v8NsLtnCNWIHH3QmfmXMgW6xVPvuscG /FG+PuM4YG4cj/w7GbG2LVvyuYj3bDptt2oI2OMdHROtvix6LtyK9XfLOoMfO3BVJAG6 I/yNkB5+sSgTECMhLrA/HDE+d4s6nFdyGeuvKOh1J8JOWv9aeokmcjc2P0s6jW2XttYk +pDY4Zc/ONF6OhTHsOBHLG4f98rI/mDFOiu64DoP3ORd4ue2F4tzdfB5z09BoTsHpM00 YCRg== 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:from:date :message-id:subject:to:cc; bh=uYGFlaKfWI6AVeMfHAujHvB5ti99J1OaqA51xp+z/kU=; b=koTeP4MvxU2JsR1UFzCD7bLIhkYthl/j4EPPA2JoT2B/VtN2iJ0nHhZYXOYLmwkOKR zmq8e20WhGRCtFhL7MgZ2HUzFWgchBQoEwl2sDBPIlk6HT89+nmhIHFPVOGmcQB4sL7E VVwNfDJzIindnlzb6k5Xc3TjrXa/KkXFZLTnHPBktjspSYe+jeQCOUB7u+zBK4kQh3pB TIB0hY9s5MPnHbRdrlWDe8DgYjXkeNR9Ievb7DqP+yLSQD7zHjdfKuBFgh+/zqMPrkux 27yIZV5vp6uQ5lSR94OqNvD/qZXPaq5CYRbjkVAuTxGjbFoJ/u13wU52nwfXKNM5LrIa EnLw== X-Gm-Message-State: ABuFfogRq+shmlZIAcxT0VSRSgM3h1pAQKZbmo/1EmwIE2Bg0dAN1k6m CmHkb+rx/IUPoOr5D0tEqAQ3AFwgCNnJ5uDvur0= X-Google-Smtp-Source: ACcGV62LcBiL3EXFGbSKpiVXblcQYkg5H67IR+IPOyfdrJOc9WgMv1dbbUgwY9KrgAiN4whY2W+6ehgXhCJsYAaomjA= X-Received: by 2002:adf:ac4a:: with SMTP id v68-v6mr396793wrc.165.1537587381817; Fri, 21 Sep 2018 20:36:21 -0700 (PDT) MIME-Version: 1.0 References: <20180919112305.GA18604@zakirov.localdomain> <20180919125324.GA31127@zakirov.localdomain> <20180921194617.GA8051@artur-book.localdomain> In-Reply-To: <20180921194617.GA8051@artur-book.localdomain> From: Pavel Stehule Date: Sat, 22 Sep 2018 05:35:45 +0200 Message-ID: Subject: Re: [HACKERS] proposal: schema variables To: Artur Zakirov Cc: Dean Rasheed , Fabien COELHO , Gilles Darold , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000b455e105766d758c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000b455e105766d758c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable p=C3=A1 21. 9. 2018 v 21:46 odes=C3=ADlatel Arthur Zakirov napsal: > On Wed, Sep 19, 2018 at 04:36:40PM +0200, Pavel Stehule wrote: > > ON COMMIT DROP is used only for temp variables (transaction or not > > transaction). The purpose is same like for tables. Sometimes you can to > > have object with shorter life than is session. > > > > ON TRANSACTION END RESET has sense mainly for not transaction variables= . > I > > see two use cases. > > > > 1. protect some sensitive data - on transaction end guaranteed reset an= d > > cleaning on end transaction. So you can be sure, so variable is not > > initialized (has default value), or you are inside transaction. > > > > 2. automatic initialization - ON TRANSACTION END RESET ensure so variab= le > > is in init state for any transaction. > > > > Both cases has sense for transaction or not transaction variables. > > > > I am thinking so transaction life time for content has sense. Is cheape= r > to > > reset variable than drop it (what ON COMMIT DROP does) > > > > What do you think? > > Thanks, I understood the cases. > > But I think there is more sense to use these options only with > transactional > variables. It is more consistent and simple for me. > I agree so it can be hard to imagine - and if I return back to start discussion about schema variables - it can be hard because it joins some concepts - variables has persistent transactional metadata, but the content is not transactional. I don't think so the variability is a issue in this case. There is a lot of examples, so lot of combinations are possible - global temp tables and package variables (Oracle), local temp tables and local variables (Postgres), session variables and memory tables (MSSQL). Any combination of feature has cases where can be very practical and useful. ON TRANSACTION END RESET can be useful, because we have not a session event triggers (and in this moment I am not sure if it is necessary and practical - their usage can be very fragile). But some work can do ON xxx clauses, that should not to have negative impact on performance or fragility. ON TRANSACTION END RESET ensure cleaned and initialized to default value for any transaction. Other possibility is ON COMMAND END RESET (but I would not to implement it now), ... > As a summary, it is 1 voice vs 1 voice :) So it is better to leave the > syntax as is without changes for now. > :) now is enough time to think about syntax. Some features can be removed and returned back later, where this concept will be more absorbed. Regards Pavel > > -- > Arthur Zakirov > Postgres Professional: http://www.postgrespro.com > Russian Postgres Company > --000000000000b455e105766d758c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


p=C3= =A1 21. 9. 2018 v=C2=A021:46 odes=C3=ADlatel Arthur Zakirov <a.zakirov@postgrespro.ru> napsal:
On Wed, Sep 19, 2018 at 04:36:40PM += 0200, Pavel Stehule wrote:
> ON COMMIT DROP is used only for temp variables (transaction or not
> transaction). The purpose is same like for tables. Sometimes you can t= o
> have object with shorter life than is session.
>
> ON TRANSACTION END RESET has sense mainly for not transaction variable= s. I
> see two use cases.
>
> 1. protect some sensitive data - on transaction end guaranteed reset a= nd
> cleaning on end transaction. So you can be sure, so variable is not > initialized (has default value), or you are inside transaction.
>
> 2. automatic initialization - ON TRANSACTION END RESET ensure so varia= ble
> is in init state for any transaction.
>
> Both cases has sense for transaction or not transaction variables.
>
> I am thinking so transaction life time for content has sense. Is cheap= er to
> reset variable than drop it (what ON COMMIT DROP does)
>
> What do you think?

Thanks, I understood the cases.

But I think there is more sense to use these options only with transactiona= l
variables. It is more consistent and simple for me.
I agree so it can be hard to imagine - and if I return back to= start discussion about schema variables - it can be hard because it joins = some concepts - variables has persistent transactional metadata, but the co= ntent is not transactional.

I don't think= so the variability is a issue in this case. There is a lot of examples, so= lot of combinations are possible - global temp tables and package variable= s (Oracle), local temp tables and local variables (Postgres), session varia= bles and memory tables (MSSQL). Any combination of feature has cases where = can be very practical and useful.

ON TRANSACT= ION END RESET can be useful, because we have not a session event triggers (= and in this moment I am not sure if it is necessary and practical - their u= sage can be very fragile). But some work can do ON xxx clauses, that should= not to have negative impact on performance or fragility.

ON TRANSACTION END RESET ensure cleaned and initialized to default = value for any transaction. Other possibility is ON COMMAND END RESET (but I= would not to implement it now), ...


As a summary, it is 1 voice vs 1 voice :) So it is better to leave the
syntax as is without changes for now.

:= ) now is enough time to think about syntax. Some features can be removed an= d returned back later, where this concept will be more absorbed.
=
Regards

Pavel
=C2=A0<= /div>

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
--000000000000b455e105766d758c--