BUG #8524: PQsendQueryParams with RETURNING clause on a INSERT - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8524
Logged by:          Peter Reijnders
Email address:      peter.reijnders@verpeteren.nl
PostgreSQL version: 9.1.9
Operating system:   Debian Wheezy
Description:

Hai


I am implementing a postgresql database interface with mozilla's
spidermonkey.
So that users can use Postgresql querys in serverside javascript. It goes
rather well!
I prefer to give the users flexibility:
 - they should be able to use multiple statements in one request.
 - they should be able to send parameterised querys.
 - the result of the query should be accessible.
I thougth that the following code was providing a good balance:


if (queue->nParams == 0) {
    rc = PQsendQuery(myhandle->conn, queue->statement);
} else {
    //only Version 2 protocoll, and only one command per statement
    rc = PQsendQueryParams(myhandle->conn, queue->statement, queue->nParams,
NULL, queue->paramValues, queue->paramLengths, NULL, 1);
}



I am having troubles with a the RETURNING clause on a INSERT when using
PQsendQueryParams.
PQsendQuery is returning the record as it has been written to to database.
PQsendQueryParams appears just to be returning the records, before these
have been written.
That means that defaults and serial fields are ('') empty.
Am I doing something wrong, or is PQsendQueryParams indeed behaving
differently then PQsendQuery.
I looked on the TODO list for libpq, the faq, the documentation, and
google-fu. but I have not found any reference.
Any pointers, tips, comments, workarounds etc are greatly appreciated.


I am using:
    debian wheezy`s postgresql-server: 9.1.9-1 and libpq-dev 9.1.9-1.
    select version();: PostgreSQL 9.1.9 on i686-pc-linux-gnu, compiled by gcc
(Debian 4.7.2-5) 4.7.2, 32-bit
    uname -a: Linux P2201 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686
GNU/Linux
    installed via apt-get
    changes to the configuration are basically involving more logging, so I can
see the results of my development
        log_connections = on
        log_disconnections = on
        log_statements = 'all'
        listen_address = '10.0.0.25'
I could reproduce this also on another machine using
    debian sid`s postgresql-server 9.3.1-1 and libpq-dev 9.3.1-1.
    select version();: PostgreSQL 9.3.1 on i686-pc-linux-gnu, compiled by gcc
(Debian 4.8.1-10) 4.8.1, 32-bit
    uname -a: Linux L1866 2.6.32-5-686 #1 SMP Sun May 6 04:01:19 UTC 2012 i686
GNU/Linux
    installed via apt-get
    changes to the configuration are basically involving more logging, so I can
see the results of my development
        log_connections = on
        log_disconnections = on
        log_statements = 'all'
        listen_address = '10.0.0.34'

Steps to reproduce are possible via the snippets here below.


---%<---------- create a simple table with default and primary key -----
CREATE TABLE IF NOT EXISTS foo (
    id SERIAL PRIMARY KEY,
    ed INTEGER,
    t TIMESTAMP DEFAULT now(),
    bar VARCHAR(32),
    listint integer[]
) WITH OIDS ;
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
--->%---------------


---%<---------- create a simple Makefile (Makefile)-----
all: sendQuery sendQueryParams


sendQuery: test.c
    gcc -o sendQuery test.c -I /usr/include/postgresql -L /usr/lib -lpq


sendQueryParams: test.c
    gcc -o sendQueryParams test.c -I /usr/include/postgresql -L /usr/lib -lpq
-D PARAMS=2


.PHONEY:     clean


clean:
    rm -rf sendQuery sendQueryParams


--->%---------------


---%<---------- create a simple test case (test.c) -----
/*
 * Test RETURNING CLAUSE WITH PQsendQueryParams and PQsendQuery
 * Code based upon examples out "PostgreSQL: A Comprehensive Guide to
Building, Programming, and ..; By Korry Douglas, Susan Douglas*"
 *
 * I am having troubles with a the RETURNING clause on a INSERT when using
PQsendQueryParams.
 * PQsendQuery is returning the record as it has been written to to
database. PQsendQueryParams appears just to be returning the records, before
these have been written.
 * That means that defaults and serial fields are ('') empty.
 *
 *
 *  A simple insert, via PQSendQuery.
 *  This is returning the record with the data as it has been written to the
database.
 * --> It is working as expected. YAY!
 *
 * $./sendQuery "hostaddr=10.0.0.25 dbname=apedevdb user=apedev
password=vedepa port=5432"
 * INSERT INTO foo (ed, bar) VALUES (6, 'cool') RETURNING *; submit: 1
 *
 * Record: 0
 *     id:    40
 *     ed:    6
 *     t:    2013-10-13 11:09:37.069289
 *     bar:    cool
 *     listint:
 *
 *
 * A insert with parameters, via PQsendQueryParams.
 * The id and the t colums are filled in the database, but these "written"
values are not in the returning record.
 * --> It is NOT working as expected. :-(i
 *
 * $ ./sendQueryParams "hostaddr=10.0.0.25 dbname=apedevdb user=apedev
password=vedepa port=5432"
 * INSERT INTO foo (ed, bar) VALUES ($1, $2) RETURNING *; submit: 1
 *
 * Record: 0
 *    id:
 *     ed:
 *     t:
 *     bar:    Beatnuts`, no escapin' this!
 *     listint:
 *
 *
 * The following table must be created, as this is used for the insert
statements.
    CREATE TABLE IF NOT EXISTS foo (
        id        SERIAL         PRIMARY KEY,
        ed        INTEGER,
        t        TIMESTAMP     DEFAULT now(),
        bar        VARCHAR(32),
        listint    integer[]
    ) WITH OIDS ;'
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sys/types.h>
#include <libpq-fe.h>


void print_result_set(PGresult *result) {
    int row, col, fields, records;


    records = PQntuples(result);
    for (row =0; row < records; row++) {
        fields = PQnfields(result);
        printf("Record: %d\n" , row);
        for (col = 0; col < fields; col++ ) {
            printf("\t%s:\t%s\n", PQfname(result, col), PQgetvalue(result, row,
col));
        }
    }
    printf("\n");
}


int is_result_ready(PGconn * connection) {
    int my_socket;
    struct timeval timer;
    fd_set read_mask;


    if (PQisBusy(connection) == 0) {
        return 1;
    }
    my_socket = PQsocket (connection);
    timer.tv_sec = (time_t) 1;
    timer.tv_usec =0;
    FD_ZERO (&read_mask);
    FD_SET(my_socket, &read_mask);
    if (select(my_socket + 1, &read_mask, NULL, NULL, &timer) == 0 ) {
        return 0;
    } else if (FD_ISSET(my_socket, &read_mask)) {
        PQconsumeInput(connection);
        if (PQisBusy (connection) ==0 ) {
            return 1;
        } else {
            return 0;
        }
    } else {
        return 0;
    }
}


int process_query(PGconn * connection) {
    int submitted;
    PGresult * result;
#ifdef PARAMS
    const char *query_text = "INSERT INTO foo (ed, bar) VALUES ($1, $2)
RETURNING *;";
    const char *paramValues[PARAMS];
    int         paramLengths[PARAMS], i;

    paramValues[0] = "6";
    paramValues[1] = "Beatnuts`, no escapin' this!";
    for (i = 0; i <PARAMS; i++) {
        paramLengths[i] = strlen(paramValues[i]);
    }
    submitted = PQsendQueryParams( connection, query_text, PARAMS, NULL,
paramValues, paramLengths, NULL, 1);
#else
    const char *query_text = "INSERT INTO foo (ed, bar) VALUES (6, \'cool\')
RETURNING *;";

    submitted = PQsendQuery( connection, query_text);
#endif
    printf("%s submit: %d\n", query_text, submitted);
    if (submitted == 0 ) {
        printf ("%d\n", PQerrorMessage(connection));
        return;
    }
    do {
        while (is_result_ready( connection) == 0 ) {
            putchar ('.');
            fflush (stdout);
        }
        printf("\n");
        if (( result = PQgetResult(connection)) != NULL) {
            if (PQresultStatus (result)  == PGRES_TUPLES_OK) {
                print_result_set(result);
            } else if (PQresultStatus (result ) == PGRES_COMMAND_OK) {
                printf ("%s", PQcmdStatus(result));
                if (strlen(PQcmdTuples(result))) {
                    printf(" - %s rows\t", PQcmdTuples (result));
                } else {
                    printf ("\n");
                }
            } else {
                printf ("%s\n", PQresultErrorMessage(result));
            }
            PQclear(result);
        }
    } while (result != NULL);
}


void usage(char **argv) {
    printf("Usage: %s \"connection string\n\"", argv[0]);
    printf("       e.g.: %s \"hostaddr=10.0.0.25 dbname=apedevdb user=apedev
password=vedepa port=5432\"\n", argv[0]);
    exit(1);
}


int main(int argc, char **argv) {
    PGconn *connection;


    if (argc != 2 ) {
        usage(argv);
    }
    if (( connection =  PQconnectdb(argv[1])) == NULL)  {
        printf("Unable to allocate connection\n");
        exit(1);
    }
    if (PQstatus(connection) != CONNECTION_OK) {
        printf("%s\n", PQerrorMessage(connection));
        exit(1);
    }
    process_query(connection);
    PQfinish(connection);


    return 0;
}


--->%---------------


---%<---------- shell -----
peter@P2201:~/Development/src/libpq$ make
gcc -o sendQuery test.c -I /usr/include/postgresql -L /usr/lib -lpq
gcc -o sendQueryParams test.c -I /usr/include/postgresql -L /usr/lib -lpq -D
PARAMS=2
peter@P2201:~/Development/src/libpq$ ./sendQueryParams "hostaddr=10.0.0.25
dbname=apedevdb user=apedev password=vedepa port=5432"
INSERT INTO foo (ed, bar) VALUES ($1, $2) RETURNING *; submit: 1


Record: 0
    id:
    ed:
    t:
    bar:    Beatnuts`, no escapin' this!
    listint:




peter@P2201:~/Development/src/libpq$ ./sendQuery "hostaddr=10.0.0.25
dbname=apedevdb user=apedev password=vedepa port=5432"INSERT INTO foo (ed,
bar) VALUES (6, 'cool') RETURNING *; submit: 1


Record: 0
    id:    45
    ed:    6
    t:    2013-10-13 11:14:54.029993
    bar:    cool
    listint:


tail -f /var/log/postgresql/postgresql-9.1-main.log
2013-10-13 11:14:42 CEST LOG:  connection received:
host=statusclick.bieosthoes.net port=58441
2013-10-13 11:14:42 CEST LOG:  connection authorized: user=apedev
database=apedevdb
2013-10-13 11:14:42 CEST LOG:  execute <unnamed>: INSERT INTO foo (ed, bar)
VALUES ($1, $2) RETURNING *;
2013-10-13 11:14:42 CEST DETAIL:  parameters: $1 = '6', $2 = 'Beatnuts`, no
escapin'' this!'
2013-10-13 11:14:42 CEST LOG:  disconnection: session time: 0:00:00.075
user=apedev database=apedevdb host=statusclick.bieosthoes.net port=58441






2013-10-13 11:14:53 CEST LOG:  connection received:
host=statusclick.bieosthoes.net port=58442
2013-10-13 11:14:54 CEST LOG:  connection authorized: user=apedev
database=apedevdb
2013-10-13 11:14:54 CEST LOG:  statement: INSERT INTO foo (ed, bar) VALUES
(6, 'cool') RETURNING *;
2013-10-13 11:14:54 CEST LOG:  disconnection: session time: 0:00:00.077
user=apedev database=apedevdb host=statusclick.bieosthoes.net port=58442


--->%---------------