problem with PQsendQuery/PQgetResult and COPY FROM statement

From: max(dot)poletto(at)gmail(dot)com
To: pgsql-hackers(at)postgresql(dot)org
Subject: problem with PQsendQuery/PQgetResult and COPY FROM statement
Date: 2006-05-19 18:51:00
Message-ID: 1148064660.016529.192030@g10g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I attempt to execute a "COPY test FROM STDIN" using PQsendQuery.
Subsequently, after the appropriate poll()/PQconsumeInput/PQisBusy
calls, PQgetResult returns tens of millions of (invalid?) non-null
PGresults. This behavior seems incorrect, and sometimes causes my
application to exhaust memory and crash.

The postgres version is 8.1.3.

I can reproduce the problem in about 50 lines of C. I include below
(1) the code, (2) a psql dump of the table in question, (3) the code's
output.

I'd appreciate any insight or suggestions you may have.

Thanks,
max poletto

======================================================================
(1) code

// compile with: g++ -Wall -O -o pgtest pgtest.cc -lpq

#include <cerrno>
#include <cstdio>
#include <ctime>
#include <vector>
using namespace std;
#include <libpq-fe.h>
#include <sys/poll.h>

void retrieve(PGconn *conn, time_t timeout)
{
vector<PGresult *> res;
while (1) {
int r;
do {
struct pollfd pfds[1];
pfds[0].fd = PQsocket(conn);
pfds[0].events = POLLIN;
pfds[0].revents = 0;
r = poll(pfds, sizeof(pfds) / sizeof(struct pollfd), 1000 *
timeout);
} while (r < 0 && errno == EINTR);
if (r <= 0 || !PQconsumeInput(conn))
return;
int i = 0;
PGresult *oldr = 0;
while (!PQisBusy(conn)) {
PGresult *r = PQgetResult(conn);
if (r) {
res.push_back(r);
if (++i % 5000000 == 0) { printf("%d results\n", i); }
if (r == oldr) { printf("r==oldr (%p)\n", r); }
oldr = r;
} else {
printf("PQgetResult return 0 after %d results\n", i);
return;
}
}
}
}

int main()
{
PGconn *conn = PQconnectdb("dbname=testdb user=postgres");
if (!conn)
return -1;
if (PQsendQuery(conn, "COPY test FROM STDIN") > 0) {
retrieve(conn, 20);
if (PQputCopyEnd(conn, 0) < 0)
return -1;
}
PQfinish(conn);
return 0;
}

======================================================================
(2) psql session

root(at)tm01-5% psql testdb postgres
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

testdb=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |

testdb=# select * from test;
a | b | c
---+---+---
1 | 2 | 3
(1 row)

testdb=#

======================================================================
(3) output

root(at)tm01-5% ./pgtest
5000000 results
10000000 results
15000000 results
20000000 results
25000000 results
PQgetResult return 0 after 25649299 results
4.640u 4.696s 0:09.34 99.8% 0+0k 0+0io 0pf+0w

In this toy example, the process VM size exceeds 2GB before PQgetResult
finally returns 0. The real application, which has already allocated
~1GB prior to the query, runs out of memory (3GB limit on 32-bit linux)
before PQgetResult ever returns 0.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-05-19 18:53:05 Re: Compression and on-disk sorting
Previous Message Joshua D. Drake 2006-05-19 18:48:50 Re: [OT] MySQL is bad, but THIS bad?