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 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?
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