| 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: | Whole Thread | Raw Message | 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.
| 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? |