Re: [HACKERS] proposal: schema variables - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: [HACKERS] proposal: schema variables |
| Date | |
| Msg-id | CAFj8pRBchBfyPKAVPnJKhDjh7r6kTJ9L1=etMt-pjdDzpsKOUA@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] proposal: schema variables ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: [HACKERS] proposal: schema variables
|
| List | pgsql-hackers |
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.3. possibility to store short life data in fast secured storage2. available from any PL used by PostgreSQL, data can be shared between different PL1. feature like PL/SQL package variables (with similar content life cycle)I recap a goals (the order is random):HiI 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.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 blocks5. session variables with possibility to process static code checkWhat 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 functionsI 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 formThoughts 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:andcould 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.
pgsql-hackers by date: