Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

> pavel(dot)stehule(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>>> pavel(dot)stehule(at)gmail(dot)com wrote:
>>>
>>>> bryn(at)yugabyte(dot)com wrote:
>>>>
>>>> I’m still hoping that I might get some pointers to whitepapers or blog posts that expand on those bullets that I quoted from the PG doc: «Instead of packages, use schemas to organize your functions into groups.» and «Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.»
>>>
>>> I fixed a patch https://commitfest.postgresql.org/36/1608/ so you can check it.
>>>
>>> Using temporary tables instead of session variables is not too practical. There are more alternative ways - a) one extension, b) using global variables from Perl, c) using global configuration variables. The @c is most common today
>>>
>>> http://okbob.blogspot.com/2021/06/current_setting
>>
>> Do you have a plain English account of what your patch will bring for the application programmer?
>
> I wrote about it. Did you read this article?
>
> https://okbob.blogspot.com/2018/02/schema-variables.html
>
> The goals of this project:
> • fast non transactional non persistent (session based) storage,
> • possibility to control access to stored data with PostgreSQL GRANT/REVOKE commands - schema variable can be filled by security definer function, and anywhere in session can be read, but cannot be changed,
> • possibility to share data between different PostgreSQL environments (client side, server side, PL/Python, PL/Perl, ...)
> • possibility to have an analogy of package variables for PLpgSQL,
> • it should not block a possibility to check PLpgSQL code by plpgsql_check.
>
>> bryn(at)yugabyte(dot)com continued:
>>
>> Your current_setting blog post shows me that you use this:
>>
>> pts := current_setting('tps.ts', true)::timestamp with time zone;
>>
>> Is that your point? I so, then thanks. Yes, I’ve used this technique myself.
>
> The advantage of my implementation against GUC is:
> a) performance and correctness - session variables are typed and stored in binary format, GUC is just text, data should be converted every time
> b) possibility to set secure access,
> c) persistence in schema (data are not persistent)
> d) more comfortable work - there is not necessary to use helper functions
>
> The advantages/disadvantage against PL/SQL package variables is deeper integration with SQL engine and generally missing schema private objects. But this is not supported by Postgres yet, and this needs to be supported by Postgres, if we want to use this feature from PL/pgSQL.
>
> There is an important difference between PL/SQL and PL/pgSQL. PL/SQL is an independent environment with possibility to embedded SQL. PL/pgSQL is just glue for SQL - any expression in PL/pgSQL is SQL expression and it is evaluated by SQL engine. So everything supported in PL/pgSQL has to be supported by SQL engine.

Yes, I did read your “schema variables” post on your site “Pavel Stehule’s blog — Some notes about PostgreSQL”. It gives me a very good idea about what you have in mind.

But as I’ve come to understand the term “Functional Spec”, this denotes a formal deliverable that a product development (sub)group owns and maintains collectively. Especially, it has a status which, eventually, is set to “Approved”. And apart from minor bug fixes, no code makes its way into a released version of the (sub)product in question unless the group has approved its Functional Spec.

Is this model not used for the PostgreSQL system?

Your blogpost and all the comments are dated Feb 2018—so almost four years ago now. What needs to happen for your project to be exposed in a PG Release? And which release might this be?

Back to the substance of your proposal,

1. I see that a schema variable will be another kind of object in the class that has tables, views, sequences, indexes, UDTs, procedures, functions, and so on. So it’s persisted within a database; it’s owned by a user; and it’s localized in a schema. Does it have its own namespace too? For example, can a table x, a function x(), and a schema variable x all live in the same schema s in some database. And can I use the normal qualified name syntax to access a schema variable?

2. It seems, then, that all the familiar notions, and especially the ability for a non-owner to access it apply. I see that the SELECT privilege governs who can read the value of a schema variable. But there seems to be no mechanism that governs who can change the value of a schema variable. It looks like only the owner can change the value—using the new LET SQL statement. Does this mean both that a top-level call from a client session that’s authorized as the owner can change it and a “security definer” subprogram with the same owner can change it—but that there is no explicit (say, WRITE) privilege for this. Is this what you intend? If so, why not have a WRITE privilege?

4. You said “security definer function”. Is this what you mean? Or do you mean ANY function or procedure as long as the current user (i.e. the owner of the most tightly enclosing security definer unit) is the schema variable’s owner?

5. Could you please consider allowing a CONSTANT schema variable (with the usual syntax and requirement for an initialization expression)? One very popular use of a spec-level package variable is for a universal constant that has to be accessed in several places—like, for example, the conversion factor between some metric unit and some imperial unit.

3. What is the precedence scheme? For example, if a SQL statement in a PL/pgSQL unit has a restriction like this:

…where col = x…

and x is both the name of an in-scope variable (or formal parameter) in the unit and the name of a schema variable? When the table has a column called x, then there’s (at least sometimes) no way round a run-time collision error except to rename one of the X’s. (Qualifying the names doesn’t fix it.) Will it be the same with schema variables? The question extends to ordinary assignment statements that become SQL statements under the covers:

v := a + b;

where b happens to be both an in-scope variable and a schema variable.