The following bug has been logged online:
Bug reference: 3052
Logged by: brian blakey
Email address: bmb4605@yahoo.co.uk
PostgreSQL version: 8.2.3
Operating system: linux (SUSE profesional 9.3 own build)
Description: Inconsistent results from PQexec... with rules
Details:
When I create a rule of the form
ON INSERT TO relation DO INSTEAD SELECT ...
and attempt to insert the same data into the view, the libpq function
PQexec
returns the results I expect from a select whilst PQexecParams and
PQexecPrepared give the results I expect from an insert. The insert results
appear to be consistent with my reading of chapter 35.5 bullet 2.
Shouldn't all three PQexec... functions return the same results for
equivalent
requests.
I also tried using the PQsendQuery... functions with PQgetResult to see if
I
could get the results of the select that way but each gave a single set of
results the same as there PQexec equivalents.
The following can be used to demonstrate these findings:
1. Run the basic tutorial script on a new database called mydb up to where
it
starts deleting tuples and abort the other tests.
2. Add the following view and rule
CREATE VIEW testview AS SELECT temp_lo AS testcolumn FROM weather;
CREATE RULE testrule AS ON INSERT TO testview DO INSTEAD SELECT
NEW.testcolumn;
*** END SQL
3. Compile and run the following C program
NOTE: the PQexecPrepared and PQsendQuery... tests have been commented out.
#include "libpq-fe.h"
#include <netinet/in.h>
void show_results(results)
PGresult *results;
{
if ( results ){
printf(" result status is %d\n", PQresultStatus(results));
printf(" error message is \"%s\"\n",
PQresultErrorMessage(results));
if ( PQresultStatus(results) == PGRES_TUPLES_OK ){
printf(" Number of tuples is %d\n", PQntuples(results));
if ( PQntuples(results) > 0 ){
printf(" tuple 0 field 0 = %s\n", PQgetvalue(results, 0, 0));
};
}else if ( PQresultStatus(results) == PGRES_COMMAND_OK ){
printf(" Command status is \"%s\"\n", PQcmdStatus(results));
};
PQclear(results);
}else{
printf(" no results\n");
};
}
void get_results(connection, sentstate)
PGconn *connection;
int sentstate;
{
PGresult *results;
int resset = 0;
if ( sentstate ){
while ( (results = PQgetResult(connection)) ){
resset++;
printf(" results set %d\n", resset);
show_results(results);
};
}else{
printf(" error sending command\n");
};
}
int main(argc, argv)
int argc;
char **argv;
{
int status;
int sentstate;
PGconn *connection;
PGresult *results;
const char *values[] = {"4"};
const int lengths[] = {0};
const int formats[] = {0};
if ( !(connection = PQconnectdb("dbname=mydb")) ){
printf("connection to database failed\n");
return(4);
};
if ( (status = PQstatus(connection)) != CONNECTION_OK ){
printf("connection to database returned invalid status %d - terminating\n",
status);
return(4);
};
results = PQexec(connection, "SELECT VERSION();");
show_results(results);
printf("Consistency check test 1 - PQexec\n");
results = PQexec(connection, "INSERT INTO testview (testcolumn) VALUES
(4);");
show_results(results);
printf("Consistency check test 2 - PQexecParams\n");
results = PQexecParams(connection, "INSERT INTO testview (testcolumn)
VALUES ($1);",
1, NULL, values, lengths, formats, 0);
show_results(results);
/* printf("Consistency check test 3 - PQexecPrepared\n");
printf(" prepare command\n");
results = PQprepare(connection, "prepcmd", "INSERT INTO testview
(testcolumn) VALUES ($1);", 1, NULL);
show_results(results);
printf(" execute prepared command\n");
results = PQexecPrepared(connection, "prepcmd", 1, values, lengths,
formats, 0);
show_results(results);
*/
/* printf("Consistency check test 4 - PQsendQuery\n");
sentstate = PQsendQuery(connection, "INSERT INTO testview (testcolumn)
VALUES (4);");
get_results(connection, sentstate);
printf("Consistency check test 5 - PQsendQueryParams\n");
sentstate = PQsendQueryParams(connection, "INSERT INTO testview
(testcolumn) VALUES ($1);",
1, NULL, values, lengths, formats, 0);
get_results(connection, sentstate);
printf("Consistency check test 6 - PQsendQueryPrepared\n");
sentstate = PQsendQueryPrepared(connection, "prepcmd", 1, values,
lengths, formats, 0);
get_results(connection, sentstate);
*/
PQfinish(connection);
printf("end tests\n");
return(0);
}
*** END C
4. These are the results I got
result status is 2
error message is ""
Number of tuples is 1
tuple 0 field 0 = PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 3.3.5 20050117 (prerelease) (SUSE Linux)
Consistency check test 1 - PQexec
result status is 2
error message is ""
Number of tuples is 1
tuple 0 field 0 = 4
Consistency check test 2 - PQexecParams
result status is 1
error message is ""
Command status is "INSERT 0 0"
end tests
*** END RESULTS
As you can see the results of consistency check 1 (lines 5 to 9) are the
results I would expect from the select part of the rule whilst those of test
2
(lines 10 to 13) are those from the insert part.
I used version 8.2.3 as backend and psql to run these tests which I built
using all default values from sources downloaded from the latest directory
of the postgres web site. I ran the gmake check which showed no errors and
gave the final lines of
=======================
All 103 tests passed.
=======================
I use the SUSE linux professional 9.3 distribution so postgres was built
using programs from that distribution. I built my own kernel from the same
distribution.
