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

On 12/16/21 11:22, Bryn Llewellyn wrote:

Folks who develop applications for Oracle Database have had the features that the subject line of this email lists since the arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages and inner subprograms is huge. The absence of parameterizable anonymous blocks is a smaller limitation.

Notice that this point is entirely separable from the endeavor of migrating an extant application. It has first and foremost to do with how you think of designing code.

I’ve heard rumors that some contributors to the PostgreSQL implementation are interested in bringing the PL/pgSQL features that I mentioned. If there is any such thinking, please let me know. I’m not a C coder but I’d be very interested in reader any ordinary prose that describes how these features might be exposed to the PostgreSQL application developer.

Not following. To be exposed they have to exist and that is not the case
in the community Postgres. The relevant question would seem to be, how
do I get these features built?

________________________________________________________________________________

* Full disclosure: I was the product manager for PL/SQL, working at Oracle HQ, from about 2000 through 2019 when I started with Yugabyte, Inc. At least some people on this list have heard of YugabyteDB and know that it uses Postgres’s SQL processing code “as is” (currently Version 11.2, but presently Version 13) on top of its own implementation of a distributed storage layer (inspired by Google Spanner).

--
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

Folks who develop applications for Oracle Database have had the features that the subject line of this email lists since the arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages and inner subprograms is huge. The absence of parameterizable anonymous blocks is a smaller limitation.
Notice that this point is entirely separable from the endeavor of migrating an extant application. It has first and foremost to do with how you think of designing code.

I’ve heard rumors that some contributors to the PostgreSQL implementation are interested in bringing the PL/pgSQL features that I mentioned. If there is any such thinking, please let me know. I’m not a C coder but I’d be very interested in reader any ordinary prose that describes how these features might be exposed to the PostgreSQL application developer.

Not following. To be exposed they have to exist and that is not the case in the community Postgres. The relevant question would seem to be, how do I get these features built?

Bryn continued:

* Full disclosure: I was the product manager for PL/SQL, working at Oracle HQ, from about 2000 through 2019 when I started with Yugabyte, Inc. At least some people on this list have heard of YugabyteDB and know that it uses Postgres’s SQL processing code “as is” (currently Version 11.2, but presently Version 13) on top of its own implementation of a distributed storage layer (inspired by Google Spanner).

Oops. I did a typo. I’d meant to write “I’d be very interested in *reading* any ordinary prose…”

I can’t parse your “To be exposed they have to exist and that is not the case…” Do you mean that the rumor that I heard is wrong and that nobody has said to the Postgres community that they’ve embarked on, or at least are interested in, implementing what I’m asking about?

I had assumed that the answer to “How do I get these features built?” was “Write a C implementation and submit it for consideration”. But I can’t do that. The obvious Google searches like “Submit enhancement request for PostgreSQL” turn up only informal emails to lists like this. Is there a better answer?

On 12/16/21 12:36, Bryn Llewellyn wrote:

//

/adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:
/

Not following. To be exposed they have to exist and that is not the
case in the community Postgres. The relevant question would seem to
be, how do I get these features built?

/Bryn continued:/

* Full disclosure: I was the product manager for PL/SQL, working at

Oops. I did a typo. I’d meant to write “I’d be very interested in
*reading* any ordinary prose…”

That I figured out:)

I can’t parse your “To be exposed they have to exist and that is not the
case…” Do you mean that the rumor that I heard is wrong and that nobody
has said to the Postgres community that they’ve embarked on, or at least
are interested in, implementing what I’m asking about?

Not that I know of. What was being danced around is that Oracle
compatibility is a key feature of EDB's business model:

https://www.enterprisedb.com/products/edb-postgres-advanced-server-secure-ha-oracle-compatible

I had assumed that the answer to “How do I get these features built?”
was “Write a C implementation and submit it for consideration”. But I
can’t do that. The obvious Google searches like “Submit enhancement
request for PostgreSQL” turn up only informal emails to lists like this.
Is there a better answer?

Not really, though if you want to bring this up --hackers is the best list.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Dec 16, 2021 at 11:22 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:

The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages and inner subprograms is huge.

Why are those things huge? It's not self-evident to me. I can only
speak for myself, but throwing around terms like "shocking
disappointment" is never going to convince me of anything. You can
make similar statements about many other things.

Any functionality that gets added to PostgreSQL should ideally be
compelling to users that have not worked with Oracle in the past.
Maybe that happens to result in a feature that very much looks like
what you already have in mind, or maybe there are significant
differences. If you're not going to meet others closer to where they
are, then how can you expect it for yourself?

--
Peter Geoghegan

čt 16. 12. 2021 v 20:22 odesílatel Bryn Llewellyn <bryn@yugabyte.com>
napsal:

Folks who develop applications for Oracle Database have had the features
that the subject line of this email lists since the arrival of PL/SQL in
the early nineties. The advantages are self-evident to these programmers;
and their lack comes as a shocking disappointment when they start to write
application code for PostgreSQL*. The absence of packages and inner
subprograms is huge. The absence of parameterizable anonymous blocks is a
smaller limitation.

I don't think Postgres needs packages - this is a redundant concept in
Postgres, when Postgres has schemas (different from Oracle's schemas) and
extensions.

There are a lot of successful migrations from Oracle to Postgres that shows
so that the absence of mentioned features isn't too huge. Postgres is just
not compatible with Oracle. The compatibility with Oracle is not possible
without monstrous increasing size and complexity, and this is a benefit
just for a small part of users. A lot of packages and concepts in Oracle
are obsolete, or maybe not too well designed (from today's perspective).
After my experience I think there are a lot of things that are possible in
stored procedures, but I am sure it is not good to do it, and I don't think
we need to promote these patterns in Postgres.

This doesn't mean that we can stop developing functionality around stored
procedures in Postgres. I am working on session variables (an alternative
to package variables), and a few years ago I proposed a concept of schema's
private objects. Oracle has packages as inheritance of Ada language. The
stored procedures in Oracle use a slightly different conceptual model. It
is more like an application executed on the server side. On the other hand,
the schema in Postgres has different functionality than in Oracle, and
allows a more precious game with access rights, and although the syntax of
stored procedures is very near to Oracle, the concept is different, because
PL/pgSQL is executed inprocess inside SQL engine (all PL). More - PLpgSQL
is an easy fully interpreted language without optimization (the environment
is very well integrated with SQL engine, but still with low complexity),
and trying to repeat some patterns from PL/SQL can be very
counterproductive.

On 12/16/21 16:48, Pavel Stehule wrote:

I don't think Postgres needs packages - this is a redundant concept in
Postgres, when Postgres has schemas (different from Oracle's schemas)
and extensions.

Yes, ORAFCE uses schemas as the package names. However, one very
practical thing is missing: session variables. Yes, you can emulate
those with ON COMMIT PRESERVE ROWS temporary tables, but that's a rather
ugly hack.

On the other hand, packages can easily be emulated by using Python.
Having packages would make PLPg/SQL programming much prettier. It would
be much prettier to group related routines into a package than to have
them laying around without anything indicating that the routines are
related. On the plus side, packages would make it much easier to migrate
from Oracle to Postgres. And you do want that, don't you?

I am very well aware of the animosity toward the Oracle community and
the reasons for that animosity. Oracle wiped the floor with the
predecessor of Postgres, the database called "Ingres". Sandra Kurtzig,
the Ingres CEO at the the time, and Michael Stonebraker were both
involved in very public spat with Larry Ellison. Stonebraker is still
very much a factor in Postgres community and I doubt that his feelings
toward the Larry and his company have got any warmer with years.
Postgres was created with the idea of fighting the Dark Lord in the land
of Belmont, CA. However, if it is the goal of the community to have
users convert from Oracle to Postgres, than making that conversion easy
would be conducive to that goal.  A long time ago a former pastry baker
wrote the following article:

https://www.toolbox.com/tech/data-management/blogs/why-postgresql-doesnt-have-query-hints-020411/

Disclosure: I am the Oracle DBA that Josh Berkus is talking about in
that article. Recent events with "advice" to a very well known Oracle
personality testify to the fact that the emotions haven't changed much
since that article was written. Of course, Oracle is still the most
popular database in the world.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Thu, Dec 16, 2021 at 6:39 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

I am very well aware of the animosity toward the Oracle community and
the reasons for that animosity. Oracle wiped the floor with the
predecessor of Postgres, the database called "Ingres". Sandra Kurtzig,
the Ingres CEO at the the time, and Michael Stonebraker were both
involved in very public spat with Larry Ellison.

These events happened several years before I was born.

Stonebraker is still
very much a factor in Postgres community and I doubt that his feelings
toward the Larry and his company have got any warmer with years.

No, he isn't. I think that Stonebraker appeared at a few events that
were hosted by EDB, but to the best of my knowledge that's the extent
of his involvement in the project after university POSTGRES. I've
personally never met the man, or communicated with him online, and
I've worked on Postgres more or less full time for a full decade now.
As far as I'm aware he hasn't ever publicly posting to any of the
mailing lists.

--
Peter Geoghegan

Hi

pá 17. 12. 2021 v 3:39 odesílatel Mladen Gogala <gogala.mladen@gmail.com>
napsal:

On 12/16/21 16:48, Pavel Stehule wrote:

I don't think Postgres needs packages - this is a redundant concept in
Postgres, when Postgres has schemas (different from Oracle's schemas)
and extensions.

Yes, ORAFCE uses schemas as the package names. However, one very
practical thing is missing: session variables. Yes, you can emulate
those with ON COMMIT PRESERVE ROWS temporary tables, but that's a rather
ugly hack.

https://commitfest.postgresql.org/36/1608/

Regards

Pavel

On the other hand, packages can easily be emulated by using Python.

On Thu, 2021-12-16 at 11:22 -0800, Bryn Llewellyn wrote:

The advantages are self-evident to these programmers

I am not trying to belittle this, but when you are used to system A and
start working with system B you always miss some features of A, until you
get to know B better and figure out how to do things there.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

laurenz.albe@cybertec.at wrote:

bryn@yugabyte.com wrote:

The advantages are self-evident to these programmers…

I am not trying to belittle this, but when you are used to system A and start working with system B you always miss some features of A, until you get to know B better and figure out how to do things there.

Several people have responded with various points—some technical and some political. Thanks to all of you. I’m simply using the most recent turn in the thread as the hook for my present turn.

I made a terrible essay design choice with my “advantages of packages are self-evident”. I used this as a wrong-headed shortcut to save myself the effort of writing about modular software design principles—and to save you all the effort of (re)reading that stuff. So I’ll make the briefest attempt here.

«
Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

Inner subprograms are a very nice bonus because they allow locally useful helpers to be implemented right where they’re needed in one subprogram so that they’re invisible to all other subprograms in the body. The scope rules here a simply an extension of what PG already supports with block statements inside block statements.

There’s also the business of globals, at any level, and package-level globals that bring package state. Some programmers have religious objections here. But the value of constants declared in the package spec seems to be seen by all who program using packages as only useful.
»

I firmly believe that the intrinsic value of all of this has nothing to do with Oracle Database, with migrating from it to PG, or with Ada. It’s just that Oracle’s PL/SQL has a working implementation. And many people find it easier to think when they can experiment with something concrete rather than trying to hold, and run, a pretty big abstract model entirely in their head.

Anyway… enough of this. I fear that even what I said above will annoy some folks on this list.

It seems to me that there’s sufficiently vigorous opposition to anything like packages for PL/pgSQL that such an enhancement will never happen. So I must just accept this and (as Laurenz recommends) learn the best design patterns for singleton PL/pgSQL functions and procedures in PG as it presently is.

When I first started to use PG, I read “43.13. Porting from Oracle PL/SQL” (www.postgresql.org/docs/current/plpgsql-porting.html <http://www.postgresql.org/docs/current/plpgsql-porting.html&gt;). These are the relevant bullets: «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.»

This says nothing about how to model the spec/body distinction. I experimented with various schemes. For example, one schema for the exposed API and another one for the to-be-hidden implementation. This depends on a careful, practice-based, use of the privileges scheme and implies using “security definer” units. But you can’t do transaction control in such a unit—and this brings its own problems. I tried to model package state using temporary tables but I hit what seemed to be a bootstrap conundrum. How, in the absence of a trigger that fires when a session starts, can I make sure that the table that I want is in place?

Can anybody please recommend a whitepaper, or similar, that explains the recommended practice in this space?

On Fri, Dec 17, 2021 at 11:43 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:

Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

I still don't get it. It sounds like you're mostly talking about
encapsulation, or Information hiding, for stored procedures. I can
certainly see how plpgsql doesn't do those things very well, but it
still seems like there might be a lot of nuance that isn't getting
across. The list of specific features that seem to be missing are not
unreasonable, individually, and yet it feels like I cannot see some
bigger picture that's apparent to you.

Maybe you should explain your position by way of a motivating example,
involving a real world use case. Something that makes the issues
concrete. Are these items compelling because of how they allow an
organization to deploy a program in a production environment, complete
with version control? Does it have something to do with decoupling the
mutable business data stored in tables from the programs contained/run
in the same database?

--
Peter Geoghegan

pg@bowt.ie wrote:

bryn@yugabyte.com wrote:

Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

I still don’t get it. It sounds like you’re mostly talking about encapsulation, or Information hiding, for stored procedures. I can certainly see how plpgsql doesn’t do those things very well, but it still seems like there might be a lot of nuance that isn’t getting across. The list of specific features that seem to be missing are not unreasonable, individually, and yet it feels like I cannot see some bigger picture that's apparent to you.

Maybe you should explain your position by way of a motivating example, involving a real world use case. Something that makes the issues concrete. Are these items compelling because of how they allow an organization to deploy a program in a production environment, complete with version control? Does it have something to do with decoupling the mutable business data stored in tables from the programs contained/run in the same database?

I can certainly make up an example. I’ll do this over the weekend. However, I fear that it will be time wasted because at least some of the addressees here who’ve expressed strong opposition to the notion of PL/pgSQL packages must understand very well what they’re objecting to. For example, pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com> with his “schema variables, LET command” work.

Anyway… I’ll give it my best shot. I’ll try to address your specific questions in my follow-up reply. Hang on for a couple of days, please.

bryn@yugabyte.com wrote:

pg@bowt.ie <mailto:pg@bowt.ie> wrote:

bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:

Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

I still don’t get it. It sounds like you’re mostly talking about encapsulation, or Information hiding, for stored procedures. I can certainly see how plpgsql doesn’t do those things very well, but it still seems like there might be a lot of nuance that isn’t getting across. The list of specific features that seem to be missing are not unreasonable, individually, and yet it feels like I cannot see some bigger picture that's apparent to you.

Maybe you should explain your position by way of a motivating example, involving a real world use case. Something that makes the issues concrete. Are these items compelling because of how they allow an organization to deploy a program in a production environment, complete with version control? Does it have something to do with decoupling the mutable business data stored in tables from the programs contained/run in the same database?

I can certainly make up an example. I’ll do this over the weekend. However, I fear that it will be time wasted because at least some of the addressees here who’ve expressed strong opposition to the notion of PL/pgSQL packages must understand very well what they’re objecting to. For example, pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com> with his “schema variables, LET command” work.

Anyway… I’ll give it my best shot. I’ll try to address your specific questions in my follow-up reply. Hang on for a couple of days, please.

I made a start on this. But I want to think carefully about the example use case(s). So I won’t promise a delivery date. Like I said, I don’t expect to change anybody’s mind. But I do hope that I might get some useful suggestions on how, using PG Version 14, I can best meet the requirements that I’ll aim to explain.

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

Hi

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/calculating-derivation-from.html

Regards

Pavel

pavel.stehule@gmail.com wrote:

bryn@yugabyte.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?

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.

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

*pavel.stehule@gmail.com <pavel.stehule@gmail.com> wrote:*

*bryn@yugabyte.com <bryn@yugabyte.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

On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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,

Would there be statistics that are considered in query planning, or would

that be impossible or just out of scope initially?

út 21. 12. 2021 v 19:58 odesílatel Michael Lewis <mlewis@entrata.com>
napsal:

On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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,

Would there be statistics that are considered in query planning, or would

that be impossible or just out of scope initially?

The session variable has no statistics - but it is used like any other
external parameter - like PL/pgSQL variables.

postgres=# create variable xx as int;
CREATE VARIABLE
postgres=# create table xxx(a int);
CREATE TABLE
postgres=# insert into xxx select 0 from generate_series(1,10000);
INSERT 0 10000
postgres=# insert into xxx select 1 from generate_series(1,10);
INSERT 0 10
postgres=# analyze xxx;
ANALYZE
postgres=# create index on xxx(a);
CREATE INDEX
postgres=# let xx = 1;
LET
postgres=# explain analyze select * from xxx where a = xx;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Index Only Scan using xxx_a_idx on xxx (cost=0.29..8.46 rows=10 width=4)
(actual time=0.044..0.048 rows=10 loops=1) │
│ Index Cond: (a = xx)
│
│ Heap Fetches: 10
│
│ Planning Time: 0.237 ms
│
│ Execution Time: 0.072 ms
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

postgres=# let xx = 0;
LET
postgres=# explain analyze select * from xxx where a = xx;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on xxx (cost=0.00..170.12 rows=10000 width=4) (actual
time=0.036..4.373 rows=10000 loops=1) │
│ Filter: (a = xx)
│
│ Rows Removed by Filter: 10
│
│ Planning Time: 0.281 ms
│
│ Execution Time: 5.711 ms
│
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

pavel.stehule@gmail.com wrote:

bryn@yugabyte.com wrote:

pavel.stehule@gmail.com wrote:

bryn@yugabyte.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@yugabyte.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.