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 1g2dbY-0002ls-5T for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Sep 2018 14:37:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2dbW-00027w-Ob for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Sep 2018 14:37:22 +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 1g2dbW-00027n-Bw for pgsql-hackers@lists.postgresql.org; Wed, 19 Sep 2018 14:37:22 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g2dbT-0002v1-7r for pgsql-hackers@lists.postgresql.org; Wed, 19 Sep 2018 14:37:21 +0000 Received: by mail-wr1-x436.google.com with SMTP id k5-v6so6052591wre.10 for ; Wed, 19 Sep 2018 07:37:18 -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=lhooOmaRA7Pbabar2TKKKgY5Xq3b1Ux4rq5smJiaUOY=; b=fc6r6PqxJwg6M9YaiHOcaSgFIjzMZs6GPQmKbDs0x4XEpeOjxpi72oOBj/J5L5Ql58 IvN0miWYUZzoYPIW6BA8CnJa99ZlxuL8CeXEoNBSZ+y3uXt1okcXJ4CiCMUszrhlVkhw Yndyd2OUx5OZ95zKrRDi4lPa9ag9g1qz6bh6AwXFzmDUzTYkdvhWCy0TYvuTwDCrsn0q SWnowx2DZpA6LlvFgJFtkkabtPbVGQYaC4QSVM4x5d+e3V4sdNSQ+fFj2C+6n/d5dKot xsiqJyOUNJUTzwZVsUVdgIXeG/2dBe8+a31bYPpEF304m4UdtrqzoN1gXtHrVPuyieyP LvKg== 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=lhooOmaRA7Pbabar2TKKKgY5Xq3b1Ux4rq5smJiaUOY=; b=UvBqmkOod2ysaGuzcErNSoCiitttID+pGrUGul18EPvx2JvwW0odDXLaZ0UW25qfev yUPhbTCpPRru9yrKUYuo9lKZ4Rb6cFgupzKZ3lPt2775IFZM9e5RyVMV6pob+VFG0mlJ 3oxFatKuLzyRw2M68BTqA3r4hlNdUdjcd5vrsUlfACmiT2Ey/lT6Ct9oW8psMbpZpIAz Vbx1zJXEMMm3e3/0YPOmeUqxLQraokEQgupFXPsRparQabBB02yAzkhliXoW4H7Yrkv6 OfapJIQLdoK3uH1UEquOVWHNJ1qJfVk2OPykiD/QrQ+G2zkLMQKVC5dZOoqDXJKLT6w7 8ASA== X-Gm-Message-State: APzg51CDUM4bctxgkFeaOuUzQ5q26USF3YuExbm6Mekf8PkSobD6VgHH NFgLQRN3h+J5skDlzA28jtRi/pSCs7fRcjiZfhEO5xKL X-Google-Smtp-Source: ANB0VdbuM/JAPh6WLjEMsFmtqrk1YumCTlR3EoQK95lp9o3SGbK1FlhCf0cvkoY18MiGPq29Zfa2gzFvVb2tIuZQV8g= X-Received: by 2002:adf:8567:: with SMTP id 94-v6mr29734658wrh.223.1537367837388; Wed, 19 Sep 2018 07:37:17 -0700 (PDT) MIME-Version: 1.0 References: <20180919112305.GA18604@zakirov.localdomain> <20180919125324.GA31127@zakirov.localdomain> In-Reply-To: <20180919125324.GA31127@zakirov.localdomain> From: Pavel Stehule Date: Wed, 19 Sep 2018 16:36:40 +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="000000000000d6317e05763a57a0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000d6317e05763a57a0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable st 19. 9. 2018 v 14:53 odes=C3=ADlatel Arthur Zakirov napsal: > On Wed, Sep 19, 2018 at 02:08:04PM +0200, Pavel Stehule wrote: > > Unfortunately we cannot to use standard > > "SET" command, because it is used in Postgres for different purpose. > > READ|WRITE are totally clear, and for user it is another signal so > > variables are different than tables (so it is not one row table). > > > > I prefer current state, but if common opinion will be different, I have > not > > problem to change it. > > I see. I grepped the thread before writhing this but somehow missed the > discussion. > > > The content of variables is not transactional (by default). It is not > > destroyed by rollback. So I have to calculate with rollback too. So the > > most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is > little > > bit messy and I used "ON TRANSACTION END". It should be signal, so this > > event is effective on rollback event and it is valid for not transactio= n > > variable. This logic is not valid to transactional variables, where ON > > COMMIT RESET has sense. But this behave is not default and then I prefe= r > > more generic syntax. > > ... > > So I see two different cases - work with catalog (what is transactional= ) > > and work with variable value, what is (like other variables in > programming > > languages) not transactional. "ON TRANSACTION END RESET" means - does > reset > > on any transaction end. > > > > I hope so I explained it cleanly - if not, please, ask. > > I understood what you mean, thank you. I thought that > { ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only > for transactional variables in the first place. But is there any sense > in using this parameters with non-transactional variables? That is when > we create non-transactional variable we don't want that the variable > will rollback or reset its value after the end of a transaction. > 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 and 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 variable 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 cheaper to reset variable than drop it (what ON COMMIT DROP does) What do you think? Pavel > -- > Arthur Zakirov > Postgres Professional: http://www.postgrespro.com > Russian Postgres Company > --000000000000d6317e05763a57a0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


st 19.= 9. 2018 v=C2=A014:53 odes=C3=ADlatel Arthur Zakirov <a.zakirov@postgrespro.ru> napsal:
On Wed, Sep 19, 2018 at 02:08:04PM +0200, P= avel Stehule wrote:
> Unfortunately we cannot to use standard
> "SET" command, because it is used in Postgres for different = purpose.
> READ|WRITE are totally clear, and for user it is another signal so
> variables are different than tables (so it is not one row table).
>
> I prefer current state, but if common opinion will be different, I hav= e not
> problem to change it.

I see. I grepped the thread before writhing this but somehow missed the
discussion.

> The content of variables is not transactional (by default). It is not<= br> > destroyed by rollback. So I have to calculate with rollback too. So th= e
> most correct syntax should be "ON COMMIT ON ROLLBACK RESET" = what is little
> bit messy and I used "ON TRANSACTION END". It should be sign= al, so this
> event is effective on rollback event and it is valid for not transacti= on
> variable. This logic is not valid to transactional variables, where ON=
> COMMIT RESET has sense. But this behave is not default and then I pref= er
> more generic syntax.
> ...
> So I see two different cases - work with catalog (what is transactiona= l)
> and work with variable value, what is (like other variables in program= ming
> languages) not transactional. "ON TRANSACTION END RESET" mea= ns - does reset
> on any transaction end.
>
> I hope so I explained it cleanly - if not, please, ask.

I understood what you mean, thank you. I thought that
{ ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only
for transactional variables in the first place. But is there any sense
in using this parameters with non-transactional variables? That is when
we create non-transactional variable we don't want that the variable will rollback or reset its value after the end of a transaction.

ON COMMIT DROP is used only for temp variables (t= ransaction or not transaction). The purpose is same like for tables. Someti= mes you can to have object with shorter life than is session.

ON TRANSACTION END RESET has sense mainly for not transact= ion variables. I see two use cases.

1. protec= t some sensitive data - on transaction end guaranteed reset and cleaning on= end transaction. So you can be sure, so variable is not initialized (has d= efault value), or you are inside transaction.

2. a= utomatic initialization - ON TRANSACTION END RESET ensure so variable is in= init state for any transaction.

Both cases has se= nse for transaction or not transaction variables.

= I am thinking so transaction life time for content has sense. Is cheaper to= reset variable than drop it (what ON COMMIT DROP does)

What do you think?

Pavel



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