While looking into a pgpool-II problem reported by a user, I found weird behavior of PQexecPrepared().
Strange thing is, it seems PQexecPrepared() sends B(bind), Describe, Execute and Sync at once without checking the result of Bind message. Is this leagal from a point of view of the frontend/backend protocol? PostgreSQL version is 8.4.0. Here is the trace. To backend> Msg Q To backend> "begin;" To backend> Msg complete, length 12 >From backend> C >From backend (#4)> 10 >From backend> "BEGIN" >From backend> Z >From backend (#4)> 5 >From backend> Z >From backend (#4)> 5 >From backend> T Preparing the statement... To backend> Msg P To backend> "" To backend> "INSERT INTO test_table (a) VALUES ($1)" To backend (2#)> 1 To backend (4#)> 0 To backend> Msg complete, length 51 To backend> Msg S To backend> Msg complete, length 5 >From backend> 1 >From backend (#4)> 4 >From backend> Z >From backend (#4)> 5 >From backend> Z >From backend (#4)> 5 >From backend> T Executing the statement... To backend> Msg B <-- Bind To backend> "" To backend> "" To backend (2#)> 0 To backend (2#)> 1 To backend (4#)> 5 To backend> 123.5 To backend (2#)> 1 To backend (2#)> 0 To backend> Msg complete, length 24 To backend> Msg D <-- Describe To backend> P To backend> "" To backend> Msg complete, length 7 To backend> Msg E <-- Execute To backend> "" To backend (4#)> 0 To backend> Msg complete, length 10 To backend> Msg S <-- Sync To backend> Msg complete, length 5 >From backend> E >From backend (#4)> 88 >From backend> S >From backend> "ERROR" >From backend> C >From backend> "22P02" >From backend> M >From backend> "invalid input syntax for integer: "123.5"" >From backend> F >From backend> "numutils.c" >From backend> L >From backend> "106" >From backend> R >From backend> "pg_atoi" >From backend> >From backend> Z >From backend (#4)> 5 >From backend> Z >From backend (#4)> 5 >From backend> E Error executing the statement... Error Message:ERROR: invalid input syntax for integer: "123.5" To backend> Msg X To backend> Msg complete, length 5 Here is the program provided by the user: #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sys/types.h> #include <sys/stat.h> #include <fcntl.h> #include <unistd.h> #include "libpq-fe.h" #include "libpq/libpq-fs.h" int main(int argc, char **argv) { PGconn *conn; PGresult *res; Oid *param_types; char **param_values; int doTransaction = 1; conn = PQconnectdb("user=t-ishii dbname=test port=5433"); if (PQstatus(conn) == CONNECTION_BAD) { printf("Unable to connect to db\n"); PQfinish(conn); return 1; } PQtrace(conn, stdout); if(doTransaction == 1) { PQexec(conn,"begin;"); } printf("Preparing the statement...\n"); param_types = calloc(1,sizeof(Oid)); param_types[0] = 0; param_values = calloc(1,sizeof(char *)); param_values[0] = "123.5"; res = PQprepare(conn,"","INSERT INTO test_table (a) VALUES ($1)",1,param_types); switch(PQresultStatus(res)) { case PGRES_COMMAND_OK: case PGRES_TUPLES_OK: break; default: printf("%s\nError Message:%s\n","Error preparing the statement...",PQresultErrorMessage(res)); PQclear(res); free(param_types); PQfinish(conn); return 1; break; } PQclear(res); printf("Executing the statement...\n"); res = PQexecPrepared(conn,"",1,param_values,NULL,NULL,0); switch(PQresultStatus(res)) { case PGRES_COMMAND_OK: case PGRES_TUPLES_OK: break; default: printf("%s\nError Message:%s","Error executing the statement...\n",PQresultErrorMessage(res)); PQclear(res); free(param_types); PQfinish(conn); return 1; break; } if(doTransaction == 1) { PQexec(conn,"commit;"); } free(param_types); PQfinish(conn); return 0; } -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers