Re: proposal: session server side variables

2016-12-26 18:20 GMT+01:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hello Pavel,
>
> I don't understand to "expensive" word.
>>
>
> Hmmm...
>
> How much often you create/drop these variables?
>>
>
> Hmmm... As for "session variables" à la MY/MS-SQL, ISTM that a variable is
> "created" each time it is asked for, and it disappears completely at the
> end of the session... So you can have some kind of minimal SQL scripting
> with variables executed server-side, without resorting to a PL. Also useful
> in interactive mode, although there the performance is less an issue.
>
> The deployment cycle is similar to functions. I don't
>> propose any feature, that can enforce bloating of system catalogue.
>>
>
> Hmmm....
>
> The variable metadata will be persistent across sessions. I can imagine the
>> local (temporal) session variable where life cycle of metadata will be
>> related to session like our current temporary tables. But it is not in
>> this
>> proposal.
>>
>
> So it seems that I misunderstood a key detail in your proposal. Please
> accept my apology for my slow witedness. It is better to discuss in front
> of a white board...
>

sure, mainly with my language skills

>
> Now I understand that you want to create a kind of "persistant" session
> variable... that is at each new session the variable is instancianted in
> the session "automatically", whether the session will use it or not... Or
> is it instanciated on demand, i.e. when explicitely accessed?
>

I am starting with simple default solution - variable is initialized on
demand (when it is used first time in session). Attention: in my concept -
initialization and creation are different things.

In future initialization can be joined with login - we can have a autologin
function (trigger), or we can have a login initialized variables (and if
default expr is function call, then these functions can be used as
autologin functions).

>
> Could you confirm the interpretation? ISTM that "on demand" would be
> better.
>

Now, what I propose, and what is implemented in prototype is "on demand"

>
> As you gathered I was understanding that the "CREATE VARIABLE & GRANTS"
> were to be issued in each session for each variable used, inducing catalog
> changes each time, hence my quite heavy ranting...
>
> What I understand now is still a somehow strange object, but nevertheless
> the CREATE, DROP, GRANT, REVOKE at least are more justified because then
> object is somehow really persistent in the database, even if not with a
> value.
>

metadata are persistent like functions, sequences - the value is related to
session, only value.

> So I'll have to think about it...
>
> A few more questions:
>
> Bar the grantability, why wouldn't simple session variables work for this
> purpose? That is what is the added value of having them "declared"
> permanently, compared to created on demand, if the creation is really light
> weight?
>

the rights should be persistent, and should be attached to some persistent
object. Hypothetically, we can introduce new kind of objects, but it
disallow future usage of direct DML and SELECT statements.

>
> ISTM that in the Oracle package version, they are linked to PL/SQL, they
> are not part of SQL layer itself, so maybe they are only created when some
> PL/SQL from the package is invoked, and not created otherwise?
>

PL/SQL is different creature - it is living outside SQL catalogue - in
packages. I would not to reimplemented it for PL/pgSQL from following
reasons: we have schemas (that can be used as Oracle packages), we have a
extensions (that can be used partially as Oracle's packages), we have a mix
PL languages - more time I mixed PLpgSQL and PLPerlu. So mapping 1:1 from
Oracle is not good for Postgres.

>
> How would this feature interact with a kind of non persistent "simple"
> session variables that are found in MY/MS/Oracle SQL? One of my concern is
> that such a feature should not preclude other kind of session variables.
>

depends .. In my terminology your proposal is "untrusted temporary local
session variables" - it can share 50% of code - more if implementation will
be based on getter/setter function, less if it will be based on gram
implementation.

These variables should not be declared explicitly - it can be declared
implicitly by setting. They should not use any schema - bat can use
getter/setter functions

so you can write

select setvar('@var1', 10);
select getvar('@var1')

I little bit afraid of direct using the variables in query - inside special
functions we (and users) have control to choose volatility: direct using
can do some unexpected behave for users.

> And how would it interact with some "fully persistent/shared" variables?
>

I have not any use case for this. I don't know about any similar feature in
other database systems. Oracle uses dbms_pipe or dbms_alert for
interprocess communication.

I am thinking so it is possible to implement. If it is not ACID, then it
can work as custom statistic counters. If it should be ACID? Then is better
to use table. What I know, now is preferred share nothing design in
parallel systems - so for me, it looks like little bit dangerous feature -
and I see only one use case - custom statistics - where possible race
condition is not hard issue.

But I don't plan to implement it in first stage. There should be strong use
case for implementing any complex feature in shared memory. But any
implementation doesn't breaking to implement it in feature.

Regards

Pavel

>
> --
> Fabien.