Re: Deleting prepared statements from libpq.

On Fri, Jun 16, 2023 at 11:28 PM Jelte Fennema <me(at)jeltef(dot)nl> wrote:
>
> On Fri, 16 Jun 2023 at 16:26, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> > Nobody's implemented it.
> >
> > A patch to add PQclosePrepared and PQsendClosePrepared would be welcome. At least, I think so...
>
> This might have been a pretty old thread. But I just took it upon me
> to implement these functions (or well I mostly copied the
> PQsendDescribe related code and did s/describe/close). I haven't
> tested this code yet but I'm pretty sure it should just work (it
> compiles at least).
>
> The main reason I'm interested in this is because we're actively
> working on implementing named prepared statement support for PgBouncer
> in transaction pooling mode. It works with lots of client libraries
> already. But sadly it doesn't work with psycopg at the moment, or at
> least the closing part does not. And the reason is that psycopg closes
> statements using a DEALLOCATE query instead of the Close protocol
> message, because libpq does not support sending the Close protocol
> message.
>
> Afaict this is not just a problem for PgBouncer its implementation. As
> far as I can tell the same is true for the Odyssey connection pooler
> (which implemented named prepared statement support first).

I failed to link it. I don't know why.
if I comment out {assert(PQsendClosePrepared(conn,stmtName) == 1);}
then it works.
I use the following 4 commands, they all yield results, so it seems
PQsendClosePrepare is there.

nm /home/jian/postgres/pg16_test/lib/libpq.so.5.16 | grep PQsendClosePrepare
nm /home/jian/postgres/pg16_test/lib/libpq.so.5 | grep PQsendClosePrepare
nm /home/jian/postgres/pg16_test/lib/libpq.so | grep PQsendClosePrepare
nm /home/jian/postgres/pg16_test/lib/libpq.a | grep PQsendClosePrepare

------------------------------------------
/*
gcc -I/home/jian/postgres/pg16_test/include
/home/jian/misc/testlibpq.c -L/home/jian/postgres/pg16_test/lib -lpq

// nm /home/jian/postgres/pg16_test/lib/libpq.so | grep PQsendClosePrepared
000000000008df90 b __gcov0.PQsendClosePrepared
000000000007fda0 d __gcov_.PQsendClosePrepared
000000000002d060 t PQsendClosePrepared
*/

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
#include <assert.h>

void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
};

int main()
{
char *conninfo = "dbname=test16beta port=5455 host=/tmp user=jian";
PGconn *conn;
PGresult *res;

/* Make a connection to the database */
conn = PQconnectdb(conninfo);

/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
exit_nicely(conn);
}

// PREPARE q3(text, int, float, boolean, smallint) AS
// SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
// ten = $3::bigint OR true = $4 OR odd = $5::int)
// ORDER BY unique1;
// EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint);
// select 'text'::regtype::oid,'int'::regtype::oid,'float'::regtype::oid,'bool'::regtype::oid,'smallint'::regtype::oid;

const char *stmtName = "q3";
const char *query =
"SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR "
"ten = $3::bigint OR true = $4 OR odd = $5::int) "
"ORDER BY unique1 ";
int nParams = 5;
const Oid oidTypes[5] = {25, 23, 701,16, 21};
const char *const paramValues[] = {"AAAAxx","5","10.5","false","4"};

res = PQprepare(conn, stmtName, query,nParams, oidTypes);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("here %d\n",__LINE__);
fprintf(stderr, "PQprepare failed: %s\n",PQresultErrorMessage(res));
PQclear(res);
}
else
{
PQclear(res);

res = PQexecPrepared(conn, stmtName, 5, paramValues,NULL, NULL,0);

if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
PQclear(res);
printf("PQexecPrepared failed\n");
exit_nicely(conn);
}
assert(PQsendClosePrepared(conn,stmtName) == 1);

res = PQexec(conn,"select from pg_prepared_statements where
name = 'q3'");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
PQclear(res);
printf("this command should return zero rows now it's not\n");
exit_nicely(conn);
}
}

PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);

return 0;
}