Thread

Re: proposal: schema variables

Pavel Stehule <pavel.stehule@gmail.com> — 2025-11-24T19:59:48Z

Hi

I was asked to write the most reduced implementation of session variables.
I thought about possible reductions and there is one - catalog. We
can implement temporary session variables. In this case the catalog is not
necessary. After reduction the total size of the patchset is 134KB (doc +
code + tests). The size of the previous patchset was 390KB.

Unfortunately - without catalog some the dependency check is not available,
and then features that depends on dependencies are not available:

1. no catalog, no dependencies - only buildin types can be used (no custom
types, no domains),
2. no catalog, no dependencies - variables cannot be used in objects that
depend on some objects (views, sql functions),
3. simplified syntax - only scalar types can be used (no arrays, no
composites),
4. no catalog, no access rights, only owner check (variables can be used
just by their creator (owner)),
5. no catalog, no plan cache invalidation support (instead type check
before any usage),
6. no catalog, only temp variables are supported - schema cannot be
specified,
7. without direct access from the expression executor - variables cannot be
a parameter of CALL statement
8. session variables block parallel execution
9. no catalog, no object address - no event triggers for session variables
10. no catalog - DDL for session variables (CREATE TEMP VARIABLE, DROP
VARIABLE) are not transactional. Without catalog, implementation of
transactional DDL can be very difficult.

Although there are lot of strong limits, I think so implemented feature can
be still useful (parametrization of anonymous block and secure
session scope storage)

CREATE TEMP VARIABLE x AS int;
CREATE TEMP VARIABLE y AS int;
LET x = 100;
SELECT VARIABLE(x);
DO $$
BEGIN
  RAISE NOTICE '%', VARIABLE(x);
  LET y = VARIABLE(x) + 10;
 END $$;
SELECT VARIABLE(y);
DROP VARIABLE x;
DROP VARIABLE y;

This simple and reduced implementation doesn't block continuous development
in different directions (without compatibility breaks).

When I worked on this reduced version I found the importance of catalog
based implementation. It is almost impossible to implement variables with
life scope longer than transactions without a catalog. There is a strong
necessity of using dependency mechanisms. With catalog, the plan cache
invalidation can be used (instead typecheck). It is more robust.

Comments, notes?

Regards

Pavel