Thread: BUG #5093: Prepared query gives different PGresult than exec'd equivalent

The following bug has been logged online:

Bug reference:      5093
Logged by:          Mike Pomraning
Email address:      mjp@pilcrow.madison.wi.us
PostgreSQL version: 8.4.1
Operating system:   Linux i686 2.6.18-128.7.1.el5
Description:        Prepared query gives different PGresult than exec'd
equivalent
Details:

The following short program installs a RULE to SELECT two rows INSTEAD of
INSERTing into a VIEW.

When it PQexec's the insertion, I get a PGresult object with PQntuples == 2.
 However, when it PREPAREs/EXECUTEs the very same SQL, the PGresult has
PQntuples == 0.

My expectation is that the prepared statement would return the same PGresult
as its unprepared equivalent.

Am I using the API incorrectly, is my expectation amiss, is this a bug,
etc.?

/*
  $ ./a.out 'dbname=postgres password="mypass"'
  PGresult of exec() ('INSERT 0 0'): ntuples 2
  PGresult of execPrepd() ('INSERT 0 0'): ntuples 0
*/
#include <stdio.h>
#include <libpq-fe.h>

static char sql[] = "INSERT INTO v VALUES (1)";

int
main(int argc, char **argv) {
  PGconn *pg;
  PGresult *r;

  pg = PQconnectdb(argv[1] ? argv[1] : "");
  if (!pg || PQstatus(pg) == CONNECTION_BAD) {
    printf("connection failed\n");
    return 1;
  }

  PQexec(pg, "CREATE TEMPORARY VIEW v AS SELECT 1 WHERE 1=0");
  PQexec(pg, "CREATE RULE r AS ON INSERT TO v DO INSTEAD (SELECT 1 UNION
SELECT 2)");

  r = PQexec(pg, sql);
  printf("PGresult of exec() ('%s'): ntuples %d\n", PQcmdStatus(r),
PQntuples(r));

  PQprepare(pg, "pstmt", sql, 0, NULL);

  r = PQexecPrepared(pg, "pstmt", 0, NULL, NULL, NULL, 0);
  printf("PGresult of execPrepd() ('%s'): ntuples %d\n", PQcmdStatus(r),
PQntuples(r));

  PQexec(pg, "DROP RULE r");
  return 0;
}

"Mike Pomraning" <mjp@pilcrow.madison.wi.us> writes:
> The following short program installs a RULE to SELECT two rows INSTEAD of
> INSERTing into a VIEW.

> When it PQexec's the insertion, I get a PGresult object with PQntuples == 2.
>  However, when it PREPAREs/EXECUTEs the very same SQL, the PGresult has
> PQntuples == 0.

> My expectation is that the prepared statement would return the same PGresult
> as its unprepared equivalent.

> Am I using the API incorrectly, is my expectation amiss, is this a bug,
> etc.?

This is intentional, though I'm not sure if it's documented in any
user-facing place.  The reason is that prepared queries are done in the
"extended Query" protocol, which is much stricter about what sorts of
results are expected for queries: if the client sends an INSERT it is
not expecting to get back a SELECT result, so the backend suppresses
sending the result of the added-on query.  The traditional "simple
Query" protocol is a lot laxer and so we just spit out whatever happens
(and in fact PQexec silently discards all but the last result, so it's
not like you're getting a full view of reality in that case either).

Per the comment in PortalRunMulti:

    /*
     * If the destination is DestRemoteExecute, change to DestNone.  The
     * reason is that the client won't be expecting any tuples, and indeed has
     * no way to know what they are, since there is no provision for Describe
     * to send a RowDescription message when this portal execution strategy is
     * in effect.  This presently will only affect SELECT commands added to
     * non-SELECT queries by rewrite rules: such commands will be executed,
     * but the results will be discarded unless you use "simple Query"
     * protocol.
     */

            regards, tom lane