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