Here is a simple test program that takes a SELECT query, reads it and outputs a COPY-formatted stream to standard output, to simulate some activity.
It operates on 3 modes, specified by commant-line switches:
-f Load full resultset at once - old way.
-s Single-Row mode using PQgetResult().
-z Single-Row mode using PQgetRowData().
It is compiled with 2 different libpqs that correspond to
single-row-modeX branches in my github repo:
rowdump1 - libpq with rowBuf + PQgetRowData(). rowBuf is
required for PQgetRowData.
[ https://github.com/markokr/postgres/tree/single-row-mode1 ]
rowdump2 - Plain libpq patched for single-row mode.
No PQgetRowData() here.
[ https://github.com/markokr/postgres/tree/single-row-mode2 ]
Notes:
* Hardest part is picking realistic queries that matter.
It's possible to construct artificial queries that make
results go either way.
* It does not make sense for compare -f with others. But it
does make sense to compare -f from differently patched libpqs
to detect any potential slowdowns.
* The time measured is User Time of client process.
-------------------------------------------------------
QUERY: select 10000,200,300000,rpad('x',30,'z') from
generate_series(1,5000000)
./rowdump1 -f: 3.90 3.90 3.93 avg: 3.91
./rowdump2 -f: 4.03 4.13 4.05 avg: 4.07
./rowdump1 -s: 6.26 6.33 6.49 avg: 6.36
./rowdump2 -s: 7.48 7.46 7.50 avg: 7.48
./rowdump1 -z: 2.88 2.90 2.79 avg: 2.86
QUERY: select
rpad('x',10,'z'),rpad('x',20,'z'),rpad('x',30,'z'),rpad('x',40,'z'),rpad('x',50,'z'),rpad('x',60,'z')
from generate_series(1,3000000)
./rowdump1 -f: 6.29 6.36 6.14 avg: 6.26
./rowdump2 -f: 6.79 6.69 6.72 avg: 6.73
./rowdump1 -s: 7.71 7.72 7.80 avg: 7.74
./rowdump2 -s: 8.14 8.16 8.57 avg: 8.29
./rowdump1 -z: 6.45 5.15 5.16 avg: 5.59
QUERY: select
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100
from generate_series(1,800000)
./rowdump1 -f: 5.68 5.66 5.72 avg: 5.69
./rowdump2 -f: 5.69 5.84 5.67 avg: 5.73
./rowdump1 -s: 7.68 7.76 7.67 avg: 7.70
./rowdump2 -s: 7.57 7.54 7.62 avg: 7.58
./rowdump1 -z: 2.78 2.82 2.72 avg: 2.77
QUERY: select 1000,rpad('x', 400, 'z'),rpad('x', 4000, 'z') from
generate_series(1,100000)
./rowdump1 -f: 2.71 2.66 2.58 avg: 2.65
./rowdump2 -f: 3.11 3.14 3.16 avg: 3.14
./rowdump1 -s: 2.64 2.61 2.64 avg: 2.63
./rowdump2 -s: 3.15 3.11 3.11 avg: 3.12
./rowdump1 -z: 2.53 2.51 2.46 avg: 2.50
-------------------------------------------------------
Test code attached. Please play with it.
By this test, both rowBuf and PQgetRowData() look good.
--
marko
pg1 = /opt/apps/pgsql92mode1
pg2 = /opt/apps/pgsql92mode2
CFLAGS = -O -g -Wall
all: rowdump1 rowdump2
rowdump1: rowdump.c
$(CC) -I$(pg1)/include $(CFLAGS) -o $@ $< -L$(pg1)/lib
-Wl,-rpath=$(pg1)/lib -lpq -DHAVE_ROWDATA
rowdump2: rowdump.c
$(CC) -I$(pg2)/include $(CFLAGS) -o $@ $< -L$(pg2)/lib
-Wl,-rpath=$(pg2)/lib -lpq
clean:
rm -f rowdump1 rowdump2
xtest.sh
Description: Bourne shell script
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <getopt.h>
#include <libpq-fe.h>
struct Context {
PGconn *db;
int count;
char *buf;
int buflen;
int bufpos;
};
static void die(PGconn *db, const char *msg)
{
if (db)
fprintf(stderr, "%s: %s\n", msg, PQerrorMessage(db));
else
fprintf(stderr, "%s\n", msg);
exit(1);
}
static void out_flush(struct Context *ctx)
{
int out;
if (!ctx->buf)
return;
out = write(1, ctx->buf, ctx->bufpos);
if (out != ctx->bufpos)
die(NULL, "failed to write file");
ctx->bufpos = 0;
ctx->buflen = 0;
free(ctx->buf);
ctx->buf = NULL;
}
static void out_char(struct Context *ctx, char c)
{
if (ctx->bufpos + 1 > ctx->buflen) {
if (!ctx->buf) {
ctx->buflen = 16;
ctx->buf = malloc(ctx->buflen);
if (!ctx->buf)
die(NULL, "failed to allocate buffer");
} else {
ctx->buflen *= 2;
ctx->buf = realloc(ctx->buf, ctx->buflen);
if (!ctx->buf)
die(NULL, "failed to resize buffer");
}
}
ctx->buf[ctx->bufpos++] = c;
}
static void proc_value(struct Context *ctx, const char *val, int vlen)
{
int i;
char c;
for (i = 0; i < vlen; i++) {
c = val[i];
switch (c) {
case '\\':
out_char(ctx, '\\');
out_char(ctx, '\\');
break;
case '\t':
out_char(ctx, '\\');
out_char(ctx, 't');
break;
case '\n':
out_char(ctx, '\\');
out_char(ctx, 'n');
break;
case '\r':
out_char(ctx, '\\');
out_char(ctx, 'r');
break;
default:
out_char(ctx, c);
break;
}
}
}
static void proc_row(struct Context *ctx, PGresult *res, int tup)
{
int n = PQnfields(res);
const char *val;
int i, vlen;
ctx->count++;
for (i = 0; i < n; i++) {
if (i > 0)
out_char(ctx, '\t');
if (PQgetisnull(res, tup, i)) {
out_char(ctx, '\\');
out_char(ctx, 'N');
continue;
}
vlen = PQgetlength(res, tup, i);
val = PQgetvalue(res, tup, i);
proc_value(ctx, val, vlen);
}
out_char(ctx, '\n');
out_flush(ctx);
}
static void exec_query_full(struct Context *ctx, const char *q)
{
PGconn *db = ctx->db;
PGresult *r;
ExecStatusType s;
int i;
ctx->count = 0;
if (!PQsendQuery(db, q))
die(db, "PQsendQuery");
/* get next result */
r = PQgetResult(db);
s = PQresultStatus(r);
if (s != PGRES_TUPLES_OK)
die(db, PQresStatus(s));
for (i = 0; i < PQntuples(r); i++) {
proc_row(ctx, r, i);
ctx->count++;
}
PQclear(r);
}
static void exec_query_single_row(struct Context *ctx, const char *q)
{
PGconn *db = ctx->db;
PGresult *r;
ExecStatusType s;
ctx->count = 0;
if (!PQsendQuery(db, q))
die(db, "PQsendQuery");
if (!PQsetSingleRowMode(db))
die(NULL, "PQsetSingleRowMode");
/* loop until all resultset is done */
while (1) {
/* get next result */
r = PQgetResult(db);
if (!r)
break;
s = PQresultStatus(r);
switch (s) {
case PGRES_TUPLES_OK:
//printf("query successful, got %d rows\n", ctx->count);
ctx->count = 0;
break;
case PGRES_SINGLE_TUPLE:
proc_row(ctx, r, 0);
break;
default:
fprintf(stderr, "result: %s\n", PQresStatus(s));
exit(1);
break;
}
PQclear(r);
}
}
#ifdef HAVE_ROWDATA
static void proc_row_zcopy(struct Context *ctx, PGresult *res, PGdataValue *cols)
{
int n = PQnfields(res);
const char *val;
int i, vlen;
ctx->count++;
for (i = 0; i < n; i++) {
if (i > 0)
out_char(ctx, '\t');
if (cols[i].len == -1) {
out_char(ctx, '\\');
out_char(ctx, 'N');
continue;
}
vlen = cols[i].len;
val = cols[i].value;
proc_value(ctx, val, vlen);
}
out_char(ctx, '\n');
out_flush(ctx);
}
static void exec_query_zero_copy(struct Context *ctx, const char *q)
{
PGconn *db = ctx->db;
PGresult *r;
ExecStatusType s;
PGdataValue *cols;
ctx->count = 0;
if (!PQsendQuery(db, q))
die(db, "PQsendQuery");
if (!PQsetSingleRowMode(db))
die(NULL, "PQsetSingleRowMode");
/* loop until all resultset is done */
while (PQgetRowData(db, &r, &cols)) {
proc_row_zcopy(ctx, r, cols);
}
/* get final result */
r = PQgetResult(db);
s = PQresultStatus(r);
switch (s) {
case PGRES_TUPLES_OK:
//printf("query successful, got %d rows\n", ctx->count);
ctx->count = 0;
break;
default:
printf("result: %s\n", PQresStatus(s));
break;
}
PQclear(r);
}
#else
static void exec_query_zero_copy(struct Context *ctx, const char *q)
{
die(NULL, "PQgetRowData() not available");
}
#endif
static const char usage_str[] =
"usage: rowdump [-z|-s|-f] [-d CONNSTR] [-c SQLCOMMAND]\n"
"switches:\n"
" -f Load full resultset at once\n"
" -s Single-row mode\n"
" -z Single-row with direct access (PQgetRowData())\n"
;
static void usage(int err)
{
printf("%s\n", usage_str);
exit(err);
}
int main(int argc, char *argv[])
{
const char *connstr = "dbname=postgres";
const char *q = "show all";
PGconn *db;
struct Context ctx;
char c;
int exec_type = 'f';
while ((c = getopt(argc, argv, "c:d:zsfh")) != -1) {
switch (c) {
case 'c':
q = optarg;
break;
case 'd':
connstr = optarg;
break;
case 'z':
case 's':
case 'f':
exec_type = c;
break;
case 'h':
usage(0);
break;
default:
usage(1);
break;
}
}
db = PQconnectdb(connstr);
if (!db || PQstatus(db) == CONNECTION_BAD)
die(db, "connect");
memset(&ctx, 0, sizeof(ctx));
ctx.db = db;
switch (exec_type) {
case 'z':
exec_query_zero_copy(&ctx, q);
break;
case 'f':
exec_query_full(&ctx, q);
break;
case 's':
exec_query_single_row(&ctx, q);
break;
}
PQfinish(db);
return 0;
}
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
