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

út 21. 12. 2021 v 19:28 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com>
napsal:

> *pavel(dot)stehule(at)gmail(dot)com <pavel(dot)stehule(at)gmail(dot)com> wrote:*
>
>
> *bryn(at)yugabyte(dot)com <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.

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

Regards

Pavel