Re: [BUGS] BUG #1736: endless loop in PQconnectdb
* Bruce Momjian wrote:
> I think what you are seeing is that the getaddrinfo memory is placed in
> the PGconn structure that isn't freed until PQclear is called. Does
> your test call PQclear()?
s/PQclear/PQfinish/
It does call PQclear on the result, and PQfinish on the connection.
The code is attached.
With postgres doing the dns lookup:
fubar:~# while true; do ps aux|grep -v grep|grep test; sleep 30; done
root 3245 3.6 0.2 4056 1352 pts/3S+ 10:37 0:01 ./test
root 3245 3.6 0.3 4056 1456 pts/3S+ 10:37 0:02 ./test
root 3245 3.7 0.3 4184 1560 pts/3S+ 10:37 0:03 ./test
root 3245 3.7 0.3 4312 1668 pts/3R+ 10:37 0:04 ./test
root 3245 3.6 0.3 4440 1760 pts/3S+ 10:37 0:05 ./test
with an output of:
called PQconnectdb: 0x804a008
called PQexec: 0x80dcbe0
calling PQclear: 0x80dcbe0
calling PQfinish: 0x804a008
...
called PQconnectdb: 0x804a008
called PQexec: 0x80dcea0
calling PQclear: 0x80dcea0
calling PQfinish: 0x804a008
...
called PQconnectdb: 0x804a008
called PQexec: 0x80dd620
calling PQclear: 0x80dd620
calling PQfinish: 0x804a008
...
and valgrind complaining about lost blocks:
==3290== 35224 bytes in 1258 blocks are definitely lost in loss record 8 of 8
==3290==at 0x1B90459D: malloc (vg_replace_malloc.c:130)
==3290==by 0x1BC38E3B: (within /lib/tls/i686/cmov/libresolv-2.3.2.so)
==3290==by 0x1BC37B92: __libc_res_nquery (in
/lib/tls/i686/cmov/libresolv-2.3.2.so)
==3290==by 0x1BC38289: (within /lib/tls/i686/cmov/libresolv-2.3.2.so)
==3290==by 0x1BC37E8F: __libc_res_nsearch (in
/lib/tls/i686/cmov/libresolv-2.3.2.so)
==3290==by 0x1BDA307D: ???
==3290==by 0x1B9EFA65: (within /lib/tls/i686/cmov/libc-2.3.2.so)
==3290==by 0x1B9F0673: getaddrinfo (in /lib/tls/i686/cmov/libc-2.3.2.so)
==3290==by 0x1B925701: getaddrinfo_all (in /usr/lib/libpq.so.3.1)
==3290==by 0x1B916F0B: (within /usr/lib/libpq.so.3.1)
==3290==by 0x1B9164B9: PQconnectStart (in /usr/lib/libpq.so.3.1)
==3290==by 0x1B916441: PQconnectdb (in /usr/lib/libpq.so.3.1)
With the IP in the host field:
fubar:~# while true; do ps aux|grep -v grep|grep test; sleep 30; done
root 3312 1.4 0.2 3872 1092 pts/3S+ 10:42 0:00 ./test
root 3312 1.6 0.2 3872 1092 pts/3S+ 10:42 0:00 ./test
root 3312 1.9 0.2 3872 1092 pts/3S+ 10:42 0:01 ./test
root 3312 2.0 0.2 3872 1092 pts/3S+ 10:42 0:01 ./test
root 3312 2.0 0.2 3872 1092 pts/3S+ 10:42 0:02 ./test
output:
called PQconnectdb: 0x804a008
called PQexec: 0x80525b8
calling PQclear: 0x80525b8
calling PQfinish: 0x804a008
...
called PQconnectdb: 0x804a008
called PQexec: 0x80525b8
calling PQclear: 0x80525b8
calling PQfinish: 0x804a008
...
called PQconnectdb: 0x804a008
called PQexec: 0x80525b8
calling PQclear: 0x80525b8
calling PQfinish: 0x804a008
...
and no leaking output from valgrind.
Best regards,
Karsten Desler
#include
#include
// gcc -lpq -o test test.c
static const char *conninfo = "host=xxx.xxx.xx.xx port=5432 dbname=xxx user=xxx password=xxx connect_timeout=30";
// static const char *conninfo = "host=db.xxx.de port=5432 dbname=xxx user=xxx password=xxx connect_timeout=30";
static int fetch_from_database(void)
{
PGconn *conn;
PGresult *res;
char sql[32768];
int c_username, c_ip;
int ret = -1;
unsigned int max, i;
conn = PQconnectdb(conninfo);
printf("called PQconnectdb: %p\n", conn);
if (!conn) {
printf("connection failed\n");
goto out_finish;
}
snprintf(sql, sizeof(sql), "SELECT username,ip FROM extras WHERE server=(SELECT id FROM servers WHERE IP='xxx.xxx.xx.xx') ORDER BY username");
res = PQexec(conn, sql);
printf("called PQexec: %p\n", res);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
printf("couldn't get data\n");
goto out;
}
max = (unsigned int)PQntuples(res);
if (max == 0) {
ret = 0;
goto out;
}
if (max > 64) {
printf("too many results\n");
goto out;
}
c_username = PQfnumber(res, "username");
c_ip = PQfnumber(res, "ip");
if (c_username == -1 || c_ip == -1) {
printf("weird table structure found\n");
goto out;
}
for (i = 0; i < max; i++) {
(void)PQgetvalue(res, i, c_username);
(void)PQgetvalue(res, i, c_ip);
}
ret = 0;
out:
printf("calling PQclear: %p\n", res);
PQclear(res);
out_finish:
printf("calling PQfinish: %p\n", conn);
PQfinish(conn);
res = NULL;
conn = NULL;
return ret;
}
int main(void)
{
while(fetch_from_database() == 0);
return 0;
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #1736: endless loop in PQconnectdb
Karsten Desler <[EMAIL PROTECTED]> writes: > * Bruce Momjian wrote: >> I think what you are seeing is that the getaddrinfo memory is placed in >> the PGconn structure that isn't freed until PQclear is called. Does >> your test call PQclear()? > s/PQclear/PQfinish/ > It does call PQclear on the result, and PQfinish on the connection. In that case I think there is no doubt that you've found a bug in getaddrinfo/freeaddrinfo, and you ought to be reporting it to your libc provider. We do call freeaddrinfo on the result of getaddrinfo, so if not everything is cleaned up, that's a library bug not ours. You could check this by reducing the test case to getaddrinfo() then freeaddrinfo() using the same parameters that fe-connect.c passes. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1748: Unique contraints cannot be added to long text fields
On Sat, Jul 02, 2005 at 05:20:29PM +0100, Greg Steffensen wrote: > I'm storing SVG files, which can be many kilobytes long, in a text field. > When I try to add a unique contraint to that field, or try to add a row to a > table that already has had the unique contraint applied, I get errors like > the following: > > ERROR: index row requires 15528 bytes, maximum size is 8191 > > Evidently, the unique constraint creates an index, but the index can't > handle large field sizes. Yes, that's a known limitation -- you cannot store arbitrarily large fields in an index. Even if it was possible I don't think it'd be a good idea from an efficiency POV. You may want to create an index in a digested version of the file instead (SHA-1, say, or MD5). -- Alvaro Herrera () "El conflicto es el camino real hacia la unión" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1739: memory leak in pl/perl with spi_exec_query
Jean-Max Reymond a écrit : Jean-Max Reymond a écrit : The following bug has been logged online: Bug reference: 1739 Logged by: Jean-Max Reymond Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: Linux Ubuntu Description:memory leak in pl/perl with spi_exec_query I have run again my real plperl procedure and attach a gdb to the process. a call MemoryContextStats(TopMemoryContext) gives me the results as above. is it possible for a guru to check that all is ok ? A very interesting fact will be to prove that memory is not used by spi_exec but by perl and confirm that memory allocation and garbage collector in perl is pitifully. I have very carefully read the thread "plperl doesn't release memory" and I am not sure I am in the same context. TopMemoryContext: 32768 total in 3 blocks; 4264 free (7 chunks); 28504 used TopTransactionContext: 8192 total in 1 blocks; 6992 free (0 chunks); 1200 used CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Exec: 57344 total in 3 blocks; 7520 free (4 chunks); 49824 used ExecutorState: 8192 total in 1 blocks; 3368 free (4 chunks); 4824 used ExecutorState: 8192 total in 1 blocks; 2048 free (0 chunks); 6144 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI Proc: 1015013376 total in 130 blocks; 1893336 free (0 chunks); 1013120040 used SPI TupTable: 8192 total in 1 blocks; 6584 free (0 chunks); 1608 used MessageContext: 8192 total in 1 blocks; 5400 free (1 chunks); 2792 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used ExecutorState: 8192 total in 1 blocks; 6864 free (1 chunks); 1328 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used CacheMemoryContext: 4186112 total in 9 blocks; 1214808 free (1 chunks); 2971304 used index_xdb_pi: 1024 total in 1 blocks; 568 free (0 chunks); 456 used index_xdb_str: 1024 total in 1 blocks; 712 free (0 chunks); 312 used index_xdb_ele: 1024 total in 1 blocks; 696 free (0 chunks); 328 used index_xdb_child: 1024 total in 1 blocks; 568 free (0 chunks); 456 used index_xdb_attr: 1024 total in 1 blocks; 696 free (0 chunks); 328 used pg_toast_1255_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_index_indrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_user: 7168 total in 3 blocks; 3736 free (0 chunks); 3432 used pg_namespace_nspname_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_cast_source_target_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_type_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_language_name_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_class_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_operator_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_proc_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_amop_opc_strat_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_index_indexrelid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_conversion_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_amproc_opc_proc_index: 1024 total in 1 blocks; 776 free (0 chunks); 248 used pg_language_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_conversion_default_index: 1024 total in 1 blocks; 712 free (0 chunks); 312 used pg_shadow_usename_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 848 free (0 chunks); 176 used pg_namespace_oid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used pg_group_sysid_index: 1024 total in 1 blocks; 912 free (0 chunks); 112 used p
Re: [BUGS] BUG #1739: memory leak in pl/perl with spi_exec_query
Jean-Max Reymond <[EMAIL PROTECTED]> writes: > I have run again my real plperl procedure and attach a gdb to the process. > a call MemoryContextStats(TopMemoryContext) gives me the results as above. > SPI Proc: 1015013376 total in 130 blocks; 1893336 free (0 chunks); > 1013120040 used Well, that says it's definitely a bug in plperl, not some weird Perl/malloc interaction as I thought previously. Probably plperl is failing to release SPI queries when done with them. Any plperl people want to take a look? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1739: memory leak in pl/perl with spi_exec_query
"Jean-Max Reymond" <[EMAIL PROTECTED]> writes: > So, spi_exec_query allocates memory but this memory is never released until > the end of the stored procedure. Ah, found it. regards, tom lane Index: plperl.c === RCS file: /cvsroot/pgsql/src/pl/plperl/plperl.c,v retrieving revision 1.67.4.1 diff -c -r1.67.4.1 plperl.c *** plperl.c23 May 2005 02:02:52 - 1.67.4.1 --- plperl.c3 Jul 2005 21:55:03 - *** *** 1419,1424 --- 1419,1426 Int32GetDatum(tupdesc->attrs[i]->atttypmod))); hv_store(hv, attname, namelen, newSVpv(outputstr, 0), 0); + + pfree(outputstr); } return newRV_noinc((SV *) hv); ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] a bug that might be related to BUG #1739
I am writing C functions and I use SPI extensively. Till now I used cursors and there was no memory leaks. But now I do thousands of inserts using SPI_execp and I also give it parameters. Now, when I execute the insert about 50-100 times I loose around 100kb memory and SPI_finish() does not release this memory (although I really can't do SPI finish in normal executions all the time since I have open cursors). I tried spipush connect query finish pop. but the leak stays. I really need this resolved since I can reach a million runs in my functions and thus my whole memory can be depleted. I use 8.0.3 Another clue. The relation I am adding to have a unique index. If I do about 100 inserts and the relation have the index I loose around 100kb. without the index on the relation I loose around 40kb. Btw, execp with selects also looses memory. Only cursors seems immune to this. Please help, 10x. Regards, tzahi. WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] a bug that might be related to BUG #1739
Tzahi Fadida <[EMAIL PROTECTED]> writes: > I am writing C functions and I use SPI extensively. > Till now I used cursors and there was no memory leaks. > But now I do thousands of inserts using SPI_execp > and I also give it parameters. If SPI_execp leaked memory then so would almost any plpgsql function, so I'm inclined to think the leak is in your own code. Post a complete example if you want it investigated. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] a bug that might be related to BUG #1739
Here it is. It could very well be in my code or a lack of understanding.
I started from scratch and have written a small example.
If it will also appear on your machine you should notice
that you are loosing a few megabites in memory
in a few secs. Maybe I am not freeing something or something like
that or it could be some kind of caching mechnism.
All I know is that when the function finishes the memory is not
released.
And when I exit psql, the memory is released.
p.s: I don't know if its normal but if I don't do spi_freetuptable after
the inserts,
I loose 10 times more memory.
http://rafb.net/paste/results/t2arbb22.html
#include "executor/spi.h"
PG_FUNCTION_INFO_V1(nis);
Datum
nis(PG_FUNCTION_ARGS)
{
SPI_connect();
void *plan;
int ret;
int i;
Datum vals[2];
vals[0]=-1;
vals[1]=-1;
char nuls[2];
nuls[0]=' ';
nuls[1]=' ';
Oid oids[2];
oids[0]=INT4OID;
oids[1]=INT4OID;
if ((plan = SPI_prepare("CREATE TEMPORARY TABLE NIS (a int, b int)",
0, NULL)) == NULL)
elog(ERROR, "SPI_prepare() returns NULL");
if ((ret = SPI_execute_plan(plan, NULL
, NULL, false, 1)) != SPI_OK_UTILITY)
elog(ERROR, "SPI_execute_plan() was no successfull(create)");
if ((plan = SPI_prepare("insert into nis values ($1,$2)", 2,oids ))
== NULL)
elog(ERROR, "SPI_prepare() returns NULL");
for (i=0;i<100*1024;i++){
if ((ret = SPI_execute_plan(plan, vals
, nuls, false, 1)) != SPI_OK_INSERT)
elog(ERROR, "SPI_execute_plan() was no successfull(insert)");
SPI_freetuptable(SPI_tuptable);
}
SPI_finish();
for (i=0;i<2000*1024*1024;i++);
PG_RETURN_INT32(1);
}
Regards,
tzahi.
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 04, 2005 12:54 AM
> To: Tzahi Fadida
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] a bug that might be related to BUG #1739
>
>
> Tzahi Fadida <[EMAIL PROTECTED]> writes:
> > I am writing C functions and I use SPI extensively.
> > Till now I used cursors and there was no memory leaks.
> > But now I do thousands of inserts using SPI_execp
> > and I also give it parameters.
>
> If SPI_execp leaked memory then so would almost any plpgsql
> function, so I'm inclined to think the leak is in your own
> code. Post a complete example if you want it investigated.
>
> regards, tom lane
>
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #1739: memory leak in pl/perl with spi_exec_query
Tom Lane a écrit : "Jean-Max Reymond" <[EMAIL PROTECTED]> writes: So, spi_exec_query allocates memory but this memory is never released until the end of the stored procedure. Ah, found it. OK, it solves my problem. great job :-) thanks a lot Tom begin:vcard fn:Jean-Max Reymond n:Reymond;Jean-Max org:CKR Solutions Open Source adr;dom:;;67 bvd des roses;Mandelieu;;06210 email;internet:[EMAIL PROTECTED] title:responsable technique tel;work:08 71 03 25 11 tel;cell:06 16 94 25 11 x-mozilla-html:FALSE url:http://www.ckr-solutions.com version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
