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
