Re: proposal: schema variables
Pavel Stehule <pavel.stehule@gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Jim Jones <jim.jones@uni-muenster.de>
Cc: Bruce Momjian <bruce@momjian.us>, Dmitry Dolgov <9erthalion6@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, Erik Rijkers <er@xs4all.nl>, Michael Paquier <michael@paquier.xyz>, Amit Kapila <amit.kapila16@gmail.com>, DUVAL REMI <REMI.DUVAL@cheops.fr>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
jian he <jian.universality@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, PegoraroF10 <marcos@f10.com.br>
Date: 2025-12-05T06:50:25Z
Lists: pgsql-hackers, pgsql-performance
Attachments
- v20251205-0007-DISCARD-TEMP.patch (text/x-patch)
- v20251205-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch (text/x-patch)
- v20251205-0009-use-names-of-currently-used-temp-variables-for-tab-c.patch (text/x-patch)
- v20251205-0005-svariableReceiver.patch (text/x-patch)
- v20251205-0006-LET-command-assign-a-result-of-expression-to-the-ses.patch (text/x-patch)
- v20251205-0003-collect-session-variables-used-in-plan-and-assign-pa.patch (text/x-patch)
- v20251205-0002-parsing-session-variable-fences.patch (text/x-patch)
- v20251205-0004-fill-an-auxiliary-buffer-with-values-of-session-vari.patch (text/x-patch)
- v20251205-0001-CREATE-VARIABLE-DROP-VARIABLE.patch (text/x-patch)
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
>
>
>
>