Re: [HACKERS] proposal: schema variables

st 19. 9. 2018 v 14:53 odesílatel Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>
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 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.
> > ...
> > 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
>