Re: Deleting prepared statements from libpq. - Mailing list pgsql-hackers
From | jian he |
---|---|
Subject | Re: Deleting prepared statements from libpq. |
Date | |
Msg-id | CACJufxHRbW4nRa6RMttknzV7tz64L5nLFMp7tveOed3JgWFDhA@mail.gmail.com Whole thread Raw |
In response to | Re: Deleting prepared statements from libpq. (Jelte Fennema <me@jeltef.nl>) |
Responses |
Re: Deleting prepared statements from libpq.
|
List | pgsql-hackers |
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; }
pgsql-hackers by date: