Thread

Re: proposal: schema variables

Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-05T06:50:25Z

Hi

st 3. 12. 2025 v 14:44 odesílatel Jim Jones <jim.jones@uni-muenster.de>
napsal:

> Hi Pavel
>
> On 03/12/2025 05:27, Pavel Stehule wrote:
> > Hi
> >
> > fresh rebase after a87987cafca683e9076c424f99bae117211a83a4
>
>
> I'm going through the patch again and have a few initial comments.
>


>
> == Memory Management ==
>
> DROP VARIABLE seems to be leaking memory:
>
> postgres=# CREATE TEMPORARY VARIABLE var AS text;
> CREATE VARIABLE
> postgres=# LET var = repeat('🐘', 100000000);
> LET
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  381 MB
> (1 row)
>
> postgres=# DROP VARIABLE var;
> DROP VARIABLE
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  381 MB
> (1 row)
>
>
> If we simply set the variable to NULL it works as expected:
>
>
> postgres=# LET var = repeat('🐘', 100000000);
> LET
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  381 MB
> (1 row)
>
> postgres=# LET var = NULL;
> LET
> postgres=# SELECT pg_size_pretty(used_bytes)
> FROM pg_backend_memory_contexts
> WHERE name = 'session variables';
>  pg_size_pretty
> ----------------
>  240 bytes
> (1 row)
>
> Most likely you forgot to pfree "svar->value" at DropVariableByName(), e.g.
>
> void
> DropVariableByName(char *varname)
> {
>
> ...
>
> if (!svar->typbyval && !svar->isnull)
>         pfree(DatumGetPointer(svar->value));
>
> (void) hash_search(sessionvars,
>                    varname,
>                    HASH_REMOVE,
>                    NULL);
>
> }
>
>
yes, there was a bug, fixed


>
> == TAB completion ==
>
> Why suggest CREATE VARIABLE (non-temporary) if it is not supported?
>
> postgres=# CREATE V<TAB>
> VARIABLE  VIEW
> postgres=# CREATE VARIABLE var AS int;
> ERROR:  only temporal session variables are supported
>
> It would be nice to have tab completion for DROP VARIABLE and LET as well:
>
> postgres=# DROP VARIABLE <TAB>
>
> postgres=# LET <TAB>
>
>
>
> == Missing IF EXISTS in DROP VARIABLE ==
>
> DROP VARIABLE IF EXISTS var;
> ERROR:  syntax error at or near "EXISTS"
> LINE 1: DROP VARIABLE IF EXISTS var;
>
>                ^
>

Both mentioned issues are related to the declared target of this patchset -
maximal reduction of the size.

But It not difficult to support these requested features - see the patches
0008 and 0009

Best regards

Pavel



>
>
> Best, Jim
>
>
>
>