Re: [HACKERS] proposal: schema variables

Hi
st 19. 9. 2018 v 13:23 odesílatel Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)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
> processing
> >
> > 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 {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.

> =# ...
> =# begin;
> =# create variable int1 int on commit drop;
> =# rollback;
> =# -- 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
>