Re: Deleting prepared statements from libpq.

On Fri, Jun 16, 2023 at 11:28 PM Jelte Fennema <me@jeltef.nl> wrote:
>
> On Fri, 16 Jun 2023 at 16:26, Craig Ringer <craig@2ndquadrant.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;
}