Message-ID: <62426ced-0795-4191-876a-4fa16d277757@uni-muenster.de>
Date: Wed, 3 Dec 2025 14:44:16 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: proposal: schema variables
To: Pavel Stehule <pavel.stehule@gmail.com>
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>
References: 
 <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
 <9d6897bd-855d-44a6-8ebe-17b2b1d04430@uni-muenster.de>
 <CAFj8pRBDVrm_Ju9RJe1J1Yhb5BPtOq49cQH7RvQ_ZPuh__Nrcw@mail.gmail.com>
 <CAFj8pRAe1BN1iLoVaD4CLtMY4pg2XROVqmU8HngqBaFRhF4zCg@mail.gmail.com>
 <CAFj8pRBryCY4LqtrPu9E_mtZYkNfZb64-MetWE0232whdOLgjA@mail.gmail.com>
 <CAFj8pRCPjHT8xfnMHRroc_wx4wBT4ocqzjH--jqo0xHqec4qhA@mail.gmail.com>
 <CAFj8pRBTmSPUPET-Jo=REsLLx15kmHqYXmXwL2BKLeZjZqUUEg@mail.gmail.com>
 <CAFj8pRAOTQ5BkKvCFHkUE=HWzirZhVzxE5iugbcOsz=z5R0ogA@mail.gmail.com>
 <CAFj8pRDmCF05tVDbzkwL45WHLhdZnbkQLn9HA3RA-5LGu=wEXg@mail.gmail.com>
 <CAFj8pRCACo9_fwfe3ixgrRfThon+Nu26b1phd-ge7v5XCx4=9A@mail.gmail.com>
 <CAFj8pRDNBHEf2nrU3Xx3S-Vmtj+YUiGxc0CV78u7uwbxXP_acw@mail.gmail.com>
 <CAFj8pRAnWR5TV=kRu9L-xUUkxoeoWUXDKS_Qixfqj4YptjGUsA@mail.gmail.com>
 <CAFj8pRBHjSEE3A500MLyOPQogqaYGu=XVKvq4LnSnuHsFSxeJg@mail.gmail.com>
 <CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@mail.gmail.com>
Content-Language: de-DE, en-GB
From: Jim Jones <jim.jones@uni-muenster.de>
In-Reply-To: 
 <CAFj8pRDisL7v_t9-QYqxYOLvdQJCbZBY8hjzXNHMWxqEoVun4g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/62426ced-0795-4191-876a-4fa16d277757%40uni-muenster.de>
Precedence: bulk

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);

}


== 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;

               ^


Best, Jim