Re: [HACKERS] Problems w/ LO - Mailing list pgsql-hackers

> I am having some problems w/ LO in postgres 6.5.snapshot  (date marked
> 5/27).  Here is the problem:
> 
> I am doing a program that will search through ~250M of text in LO
> format.  The search function seems to be running out of ram as I get a
> 'NOTICE:  ShmemAlloc: out of memory' error after the program runs for a
> bit.  From running 'free',  I can see that I am not using any memory in
> my swap space yet,  so it does not really seem to be running out of
> memory.  Postmaster does constantly grow even though I am not
> generating any information that should make it grow at all.  When I
> have commented out the lo_open and lo_close function calls,  everything
> is ok so I am guessing that there is some kind of a leak in the lo_open
> and lo_close functions if not in the back end in postmaster.  Come take
> a look at the code if you please:
> 
> http://x.cwru.edu/~bap/search_4.c

I have took look at your code. There are some minor errors in it, but
they should not cause 'NOTICE: ShmemAlloc: out of memory' anyway.  I
couldn't run your program since I don't have test data. So I made a
small test program to make sure if the problem caused by LO (It's
stolen from test/examples/testlo.c).  In the program, ~4k LO is read
for 10000 times in a transaction.  The backend process size became a
little bit bigger, but I couldn't see any problem you mentioned.

I have attached my test program and your program (modified so that it
does not use LO calls). Can you try them and report back what happens?
---
Tatsuo Ishii

---------------------------------------------------------------
#include <stdio.h>
#include <stdlib.h>

#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>

#include "libpq-fe.h"
#include "libpq/libpq-fs.h"

#define BUFSIZE            1024

int
main(int argc, char **argv)
{ PGconn       *conn; PGresult   *res; int            lobj_fd; int            nbytes; int i; char buf[BUFSIZE];
 conn = PQsetdb(NULL, NULL, NULL, NULL, "test");
 /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD)   {
fprintf(stderr,"%s", PQerrorMessage(conn));     exit(0);   }
 
 res = PQexec(conn, "begin"); PQclear(res);
 for (i=0;i<10000;i++) {
   lobj_fd = lo_open(conn, 20225, INV_READ);   if (lobj_fd < 0)     {fprintf(stderr, "can't open large
object");exit(0);    }   printf("start read\n");   while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0) {
printf("read%d\n",nbytes);   }   lo_close(conn, lobj_fd); }
 
 res = PQexec(conn, "end"); PQclear(res);
 PQfinish(conn); exit(0);
}
---------------------------------------------------------------
#include <stdio.h>
#include "libpq-fe.h"
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include "libpq/libpq-fs.h"
#include <string.h>

#define BUFSIZE                 1024

int    lobj_fd;
char    *buf;
int    nbytes;

buf = (char *) malloc (1024);

int print_lo(PGconn*, char*, int);

int print_lo(PGconn *conn, char *search_for, int in_oid)
{return(1);
lobj_fd = lo_open(conn, in_oid, INV_READ);
       while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)       {               if(strstr(buf,search_for))
         {                       lo_close(conn, lobj_fd);                       return 1;               }       }
 
       lo_close(conn, lobj_fd);       return 0;
}

int
main(int argc, char **argv)
{char    *search_1,    *search_2;char    *_insert;int     i;int     nFields;
PGconn     *conn;PGresult   *res;
_insert = (char *) malloc (1024);search_1 = (char *) malloc (1024);search_2 = (char *) malloc (1024);
search_1 = argv[1];search_2 = argv[2];
conn = PQsetdb(NULL, NULL, NULL, NULL, "lkb_alpha2");
res = PQexec(conn, "BEGIN");PQclear(res);res = PQexec(conn, "CREATE TEMP TABLE __a (finds INT4)");res = PQexec(conn,
"CREATETEMP TABLE __b (finds INT4)");
 
       res = PQexec(conn, "SELECT did from master_table");
       nFields = PQnfields(res);
       for (i = 0; i < PQntuples(res); i++)       {        if(print_lo(conn, search_1, atoi(PQgetvalue(res, i, 0))))
          {        printf("+");        fflush(stdout);        sprintf(_insert, "INSERT INTO __a VALUES (%i)",
atoi(PQgetvalue(res,i, 0)));        PQexec (conn, _insert);    }    else    {        printf(".");
fflush(stdout);   }       }       printf("\n\n");
 
       res = PQexec(conn, "SELECT finds from __a");
for (i = 0; i < PQntuples(res); i++)       {    if(print_lo(conn, search_2, atoi(PQgetvalue(res, i, 0))))
{       printf("+");        fflush(stdout);        sprintf(_insert, "INSERT INTO __b VALUES (%i)", atoi(PQgetvalue(res,
i,0)));        PQexec (conn, _insert);    }    else    {        printf(".");        fflush(stdout);    }       }
 
res = PQexec(conn, "SELECT finds FROM __b");
       nFields = PQnfields(res);
for(i = 0; i < PQntuples(res); i++){    printf("\n\nMatch: %i", atoi(PQgetvalue(res, i, 0)));}
printf("\n\n");

res = PQexec(conn, "END");PQclear(res);
PQfinish(conn);
exit(0);
}