Re: [HACKERS] Problems w/ LO

> 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, "CREATE TEMP 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);
}