Re: [HACKERS] proposal: schema variables

Hi

2018-02-03 1:48 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

> ​I've done a non-compilation documentation review, the diff from the poc
> patch and the diff from master are attached.
>
> Comments are inter-twined in the patch in xml comment format; though I
> reiterate (some of?) them below.
>
> On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> I wrote proof concept of schema variables. The patch is not nice, but the
>> functionality is almost complete (for scalars only) and can be good enough
>> for playing with this concept.
>>
>> I recap a goals (the order is random):
>>
>> 1. feature like PL/SQL package variables (with similar content life cycle)
>> 2. available from any PL used by PostgreSQL, data can be shared between
>> different PL
>> 3. possibility to store short life data in fast secured storage
>>
>
> ​The generic use of the word secure here bothers me.  I'm taking it to be
> "protected by grant/revoke"-based privileges; plus session-locality.
>

I have not a problem with any other formulation.


>
> 4. possibility to pass parameters and results to/from anonymous blocks
>> 5. session variables with possibility to process static code check
>>
>
> What does "process static code check" means here?​
>

It mean the possibility to check validity of code without code execution.
You can use plpgsql_check for example.


>
>
>> 6. multiple API available from different environments - SQL commands, SQL
>> functions, internal functions
>>
>
> I made the public aspect of this explicit in the CREATE VARIABLE doc
> (though as noted below it probably belongs in section II)
> ​
>
>> 7. data are stored in binary form
>>
>
> Thoughts during my review:
>
> There is, for me, a cognitive dissonance between "schema variable" and
> "variable value" - I'm partial to the later.  Since we use "setting" for
> GUCs the term variable here hopefully wouldn't cause ambiguity...
>

The "schema" is important in this case. 1) it is a analogy to "package
variable", 2) not necessary, but probably often it will be used together
with PLpgSQL. There are variables too. "Session variables" doesn't well
specify the implementation. The session variables can be GUC, psql client
variables or some custom implementation in Postgres or package variables in
Oracle.


> I've noticed that we don't seem to have or enforce any policy on how to
> communicate "SQL standards compatibility" to the user...
>
> We are missing the ability to alter ownership (or at least its
> undocumented), and if that brings into existing ALTER VARIABLE we should
> probably add ALTER TYPE TO new_type USING (cast) for completeness.
>

good note. I didn't test it. I am not sure, what variants of ALTER should
be supported. Type of variables is interface. Probably we can allow to add
new field, but change type or remove field can break other object. So it
can be prohibited like we doesn't support ALTER on views. ALTERing is
another and pretty complex topic, and I don't think it is necessary to
solve it now. This feature can be valuable without ALTER support, and
nothing block later ALTER VARIABLE implementation.

This design allows lot of interesting features (that can be implemented
step by step)

1. support for default expression
2. support for constraints and maybe triggers
3. reset on transaction end
4. initialization of session start - via default expression or triggers it
can be way how to start code on session start.


>
> Its left for the reader to presume that because these are schema
> "relations" that namespace resolution via search_path works the same as any
> other relation.
>
> I think I've answered my own question regarding DISCARD in that
> "variables" discards values while if TEMP is in effect all temp variables
> are dropped.
>

DISCARD should to remove TEMP variables and should to remove content of all
variables.


>
> Examples abound though it doesn't feel like too much: but saying "The
> usage is very simple:" before giving the example in the function section
> seems to be outside of our general style.  A better preamble than "An
> example:" would be nice but the example is so simple I could not think of
> anything worth writing.
>

This doc is just design frame. I invite any enhancing because this feature
can be difficult for some people, because mix persistent object with
temporal/session content - and term "variable" can be used in relation
algebra in different semantic. It is natural for people with stored
procedures experience - mainly with Oracle, but for any other can be little
bit difficult. I believe so there should be more practical examples -
related to RLS for example.



>
> Its worth considering how both:
>
> https://www.postgresql.org/docs/10/static/ddl.html
> and
> https://www.postgresql.org/docs/10/static/queries.html
>
> could be updated to incorporate the broad picture of schema variables,
> with examples, and leave the reference (SQL and functions) sections mainly
> relegated to syntax and reminders.
>
> A moderate number of lines changed are for typos and minor grammar edits.
>

Thank you very much

Regards

Pavel


> David J.
>
>