Date: 2019-07-17 14:06:59 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @mlkersten, @PedroTadim

Last updated: 2020-06-03 16:58:54 +0200

Comment 27144

Date: 2019-07-17 14:06:59 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:67.0) Gecko/20100101 Firefox/67.0
Build Identifier:

There is currently no way to close a specific prepared statement.
To free resources on the server, it would be good if the user/application could close a prepared statement after it is no longer needed.

PostgreSQL supports: DEALLOCATE [ PREPARE ] { name | ALL }
See https://www.postgresql.org/docs/current/sql-deallocate.html

MySQL supports: {DEALLOCATE | DROP} PREPARE stmt_name
See https://dev.mysql.com/doc/refman/8.0/en/deallocate-prepare.html

Reproducible: Always

See https://www.postgresql.org/docs/current/sql-deallocate.html
and https://dev.mysql.com/doc/refman/8.0/en/deallocate-prepare.html
This request is related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=6731

Comment 27207

Date: 2019-08-12 09:47:29 +0200
From: @mlkersten

In this context it is also good to review the use of () as the name
for the prepared statement. I would prefer an identifier, possibly controlled
by the user, e.g.

sql>prepare

A possibly cleaner approach is to consider them as short-hand function/procedure
definitions which end up in the function table. Then releasing them aligns
with dropping functions/procedures.

sql>prepare select 1;
statement turned into a CREATE FUNCTION p5(...)

sql> select p5();
sql> drop function p5();

Unfortunately, the PREPARE can also be prepended to a create table, which is not a function call.

Comment 27208

Date: 2019-08-12 10:06:08 +0200
From: @mlkersten

sql> prepare myquery select ?, count(*) from tables
sql> call myquery('total')