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 1g2bHn-0002wu-Nr for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Sep 2018 12:08:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2bHj-0001gW-H5 for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Sep 2018 12:08:47 +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 1g2bHj-0001gO-0R for pgsql-hackers@lists.postgresql.org; Wed, 19 Sep 2018 12:08:47 +0000 Received: from mail-wr1-x430.google.com ([2a00:1450:4864:20::430]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g2bHf-0007tB-K6 for pgsql-hackers@lists.postgresql.org; Wed, 19 Sep 2018 12:08:45 +0000 Received: by mail-wr1-x430.google.com with SMTP id y8-v6so1883634wrh.7 for ; Wed, 19 Sep 2018 05:08:43 -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=LgiVPGNdzAY5HeG2mnrDcnsBu1S2BeLEiz5eMbpLlpY=; b=hMfh24cZxJLt7JImGenuZeaEZHC43xG1xhvDYNsWdKVocQUkQVfdhROAZ3R8WqUZJg GuzrHwt63ooAXRai0yQgAa+DFhZPHV+6w3jDgnhb6eN7k0A+48beZnz6Xwja9suSjMso oeL3zZw0lKeWCMU0M8tXg7ZQPQi9cwnz1n6PaVyTpynAeoaM++1BhEWvUrSSvxmfLnDL prvA/w2bRfOqBdggD5KUsLIVUVx75uTd5E+fYV7QaWrf5t6wh9nChJ6G7VtKGntNIulL C7rMFq7H40CIdRqJAyLLJbSH+AMQJAjKt3nakoM8aKuVgFjBkCReMsIcgdJsegit31BE xQXQ== 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=LgiVPGNdzAY5HeG2mnrDcnsBu1S2BeLEiz5eMbpLlpY=; b=cRfDWHITLde+OJZKMIKFO47Ddn9s3PPzIwnqESXvMnmiIRI9sqFc/Ullb0Q1swjoEy YE1HmBgMFzczNDcIIzpPpw+QTw3Z9W/nhtDUr4pjwhSJh313VWrsG8mrhEQ4LmoRV8lH N2KK8r7++WNk/SAs+fiObRFW/k3ggp58/sI8RT06up2Q71tEy20oPFwvknim52mLXzzR WbQDiGc28h5YF6SyE8cmQUsCuLi+nUvO9V1xbxwnLTaXF95dfoWqxxDbRfOkcwlpElMx ySpqGhvE8CmoGDAIxC8kvlGL4EqYUIYNl6L8+BQJxW4TJMn8nTMuZ3jC2LiKWHzf2m0P LlXw== X-Gm-Message-State: APzg51Atm0hOJQpsUGZFp81kgkYNT3IvsYdQCvsgARZZlh8kGougqLCN /Nwx79hojBM4ixeMwMt5vmz4vUYiG1vDjSMgLyU= X-Google-Smtp-Source: ANB0VdYDTpEfy7RD9OHK15I/ML/nx7kmHtPROQL+wxKaGAOOuJ8dh3myBBOP5W2Eka9pmccZpLFCdwvMCHdxO92zkQw= X-Received: by 2002:adf:8567:: with SMTP id 94-v6mr29015225wrh.223.1537358922052; Wed, 19 Sep 2018 05:08:42 -0700 (PDT) MIME-Version: 1.0 References: <20180919112305.GA18604@zakirov.localdomain> In-Reply-To: <20180919112305.GA18604@zakirov.localdomain> From: Pavel Stehule Date: Wed, 19 Sep 2018 14:08:04 +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="00000000000070fa6d05763844a9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000070fa6d05763844a9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi st 19. 9. 2018 v 13:23 odes=C3=ADlatel Arthur Zakirov napsal: > Hello, > > On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote: > > Hi > > > > new update: > > > > I fixed pg_restore, and I cleaned a code related to transaction > processing > > > > There should be a full functionality now. > > I reviewed a little bit the patch. I have a few comments. > > > <structname>pg_views</structname> Columns > > I think there is a typo here. It should be "pg_variable". > I'll fix it. > > > GRANT { READ | WRITE | ALL [ PRIVILEGES ] } > > Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the > constistency. Same for REVOKE. I'm not experienced syntax developer > though. But we use SELECT and LET commands when working with variables. > So we should GRANT and REVOKE priveleges for this commands. > I understand to your proposal, - and I have not strong opinion. Originally I proposed {SELECT|UPDATE), but some people prefer {READ|WRITE}. Now I prefer Peter's proposal (what is implemented now) - READ|WRITE, because it is very illustrative - and the mentioned difference is good because the variables are not tables (by default), are not persistent, so different rights are good for me. I see "GRANT LET" like very low clear, because nobody knows what LET command does. 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. > > [ { ON COMMIT DROP | ON TRANSACTION END RESET } ] > > I think we may join them and have the syntax { ON COMMIT DROP | RESET } > to get more simpler syntax. If we create a variable with ON COMMIT > DROP, PostgreSQL will drop it regardless of whether transaction was > committed or rollbacked: > I though about it too. I'll try to explain my idea. Originally I was surprised so postgres uses "ON COMMIT" syntax, but in documentation is used term "at transaction end". But it has some sense. ON COMMIT DROP is allowed only for temporary tables and ON COMMIT DELETE ROWS is allowed for tables. With these clauses the PostgreSQL is more aggressive in cleaning. It doesn't need to calculate with rollback, because the rollback does cleaning by self. So syntax "ON COMMIT" is fully correct it is related only for commit event. It has not sense on rollback event (and doesn't change a behave on rollback event). 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 transaction variable. This logic is not valid to transactional variables, where ON COMMIT RESET has sense. But this behave is not default and then I prefer more generic syntax. Again I have not a problem to change it, but I am thinking so current design is logically correct. > =3D# ... > =3D# begin; > =3D# create variable int1 int on commit drop; > =3D# rollback; > =3D# -- There is no variable int1 > > PostgreSQL catalog is transactional (where the metadata is stored), so when I am working with metadata, then I use ON COMMIT syntax, because the behave of ON ROLLBACK cannot be changed. 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. CREATE TABLE syntax has similar options [1]. ON COMMIT controls > the behaviour of temporary tables at the end a transaction block, > whether it was committed or rollbacked. But I'm not sure is this a good > example of precedence. > > > - ONCOMMIT_DROP /* ON COMMIT DROP */ > > + ONCOMMIT_DROP, /* ON COMMIT DROP */ > > } OnCommitAction; > > There is the extra comma here after ONCOMMIT_DROP. > I'll fix it. Regards Pavel > > 1 - https://www.postgresql.org/docs/current/static/sql-createtable.html > > -- > Arthur Zakirov > Postgres Professional: http://www.postgrespro.com > Russian Postgres Company > --00000000000070fa6d05763844a9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi
st 19. 9. 2018 v=C2=A013:23 odes=C3=ADlatel Arthur Zakirov <= a.zakirov@postgrespro.ru>= ; napsal:
Hello,

On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:
> Hi
>
> new update:
>
> I fixed pg_restore, and I cleaned a code related to transaction proces= sing
>
> There should be a full functionality now.

I reviewed a little bit the patch. I have a few comments.

> <title><structname>pg_views</structname> Columns<= /title>

I think there is a typo here. It should be "pg_variable".

I'll fix it.

> GRANT { READ | WRITE | ALL [ PRIVILEGES ] }

Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the
constistency. Same for REVOKE. I'm not experienced syntax developer
though. But we use SELECT and LET commands when working with variables.
So we should GRANT and REVOKE priveleges for this commands.

I understand to your proposal, - and I have not strong= opinion. Originally I proposed {SELECT|UPDATE), but some people prefer {RE= AD|WRITE}. Now I prefer Peter's proposal (what is implemented now) - RE= AD|WRITE, because it is very illustrative - and the mentioned difference is= good because the variables are not tables (by default), are not persistent= , so different rights are good for me. I see "GRANT LET" like ver= y low clear, because nobody knows what LET command does. Unfortunately we c= annot to use standard "SET" command, because it is used in Postgr= es 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 opi= nion will be different, I have not problem to change it.

> [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]

I think we may join them and have the syntax { ON COMMIT DROP | RESET }
to get more simpler syntax. If we create a variable with ON COMMIT
DROP, PostgreSQL will drop it regardless of whether transaction was
committed or rollbacked:

I though about= it too. I'll try to explain my idea. Originally I was surprised so pos= tgres uses "ON COMMIT" syntax, but in documentation is used term = "at transaction end". But it has some sense. ON COMMIT DROP is al= lowed only for temporary tables and ON COMMIT DELETE ROWS is allowed for ta= bles. With these clauses the PostgreSQL is more aggressive in cleaning. It = doesn't need to calculate with rollback, because the rollback does clea= ning by self. So syntax "ON COMMIT" is fully correct it is relate= d only for commit event. It has not sense on rollback event (and doesn'= t change a behave on rollback event).

The content = of variables is not transactional (by default). It is not destroyed by roll= back. 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 eve= nt is effective on rollback event and it is valid for not transaction varia= ble. This logic is not valid to transactional variables, where ON COMMIT RE= SET has sense. But this behave is not default and then I prefer more generi= c syntax.

Again I have not a problem to change= it, but I am thinking so current design is logically correct.
<= br>

=3D# ...
=3D# begin;
=3D# create variable int1 int on commit drop;
=3D# rollback;
=3D# -- There is no variable int1


PostgreSQL catalog is transactional (w= here the metadata is stored), so when I am working with metadata, then I us= e ON COMMIT syntax, because the behave of=C2=A0 ON ROLLBACK cannot be chang= ed.

So I see two different cases - work with catal= og (what is transactional) and work with variable value, what is (like othe= r variables in programming languages) not transactional. "ON TRANSACTI= ON END RESET" means - does reset on any transaction end.

I hope so I explained it cleanly - if not, please, ask.

CREATE TABLE syntax has similar options [1]. ON COMMIT controls
the behaviour of temporary tables at the end a transaction block,
whether it was committed or rollbacked. But I'm not sure is this a good=
example of precedence.

> -=C2=A0 =C2=A0 =C2=A0ONCOMMIT_DROP=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/* ON COMMIT = DROP */
> +=C2=A0 =C2=A0 =C2=A0ONCOMMIT_DROP,=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 /* ON COMMIT DROP *= /
> } OnCommitAction;

There is the extra comma here after ONCOMMIT_DROP.
I'll fix it.

Regards
Pavel

1 - https://www.postgresql.org/do= cs/current/static/sql-createtable.html

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
--00000000000070fa6d05763844a9--