PQexecPrepared() behavior - Mailing list pgsql-hackers

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
connectto 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,"","INSERTINTO 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
preparingthe 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);
return1;        break;}if(doTransaction == 1) {    PQexec(conn,"commit;");}free(param_types);
 
PQfinish(conn);return 0;
}
--
Tatsuo Ishii
SRA OSS, Inc. Japan