Re: pg_dump on older version of postgres eating huge

We do have quite a few trigger. Some are RI triggers postgres
automagically creates. The remaining triggers are used for things like
audit tables, or logging who updated a row etc...

I did what you suggested. I ran pg_dump dbname > file.dmp. Immediatly
aftwards, kill -ABRT pid. I ended up w/ a 300meg core file. Just viewing
the file - it has a ton of DROP TRIGGER statements. And a few create
trigger statements.

[root(at)r-and-d /tmp]# strings core | grep "DROP TRIGGER" | wc -l
5450219
[root(at)r-and-d /tmp]# strings core | grep "CREATE TRIGGER" | wc -l
466

We have alot of triggers, but not 5.5 million :) So, I took one table,
and decided to look at the number of triggers for that table.

[root(at)r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"data_owner\"" | sort | uniq | wc -l
107
[root(at)r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"data_owner\"" | wc -l
347550
[root(at)r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"product\"" | sort | uniq | wc -l
151
[root(at)r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"product\"" | wc -l
346696

The back trace of the core looks as you describe below:

(gdb) bt
#0 0x401d7322 in __libc_send () from /lib/i686/libc.so.6
#1 0x40020675 in pqFlush () at eval.c:41
#2 0x4001e127 in PQsendQuery () at eval.c:41
#3 0x4001eb21 in PQexec () at eval.c:41
#4 0x0804d312 in getTables () at eval.c:41
#5 0x0805283e in dumpSchema () at eval.c:41
#6 0x0804adb2 in main () at eval.c:41
#7 0x4010c177 in __libc_start_main (main=0x804a5f4 <main>, argc=2,
ubp_av=0xbfffe204, init=0x804954c <_init>, fini=0x8059c50 <_fini>,
rtld_fini=0x4000e184 <_dl_fini>, stack_end=0xbfffe1fc)
at ../sysdeps/generic/libc-start.c:129
(gdb)

Again, this happens only on some of the servers... But, we downloaded the
entire db directory from one of those servers, and ran postgres against
that directory - and we see the same results... So, it would seems that
something in the data causes pg_dump to freak out... Is there a query
that I can run against the system catalog that would help ?

Steve

On Fri, 19 Mar 2004, Tom Lane wrote:

> Steve Krall <swalker(at)iglou(dot)com> writes:
> > You can get the file here( 20 megs uncompressed, 130K compressed ):
> > http://www.papajohns.com/postgres/postgres.log.bz2
> > While this dump was running, top reported that pg_dump was taking up
> > around 500-550megs. Then the machine stopped responding.
>
> Hmm. The trace looks completely unexceptional --- it's just running
> through your tables collecting index and trigger info (the loop in
> getTables() in pg_dump.c). You do seem to have rather a lot of
> triggers, but not 500 megs worth.
>
> Digging in the 7.1 source code, I notice that there is a small leak in
> this loop: the query results from the two index-related queries are
> never freed. There should be a "PQclear(res2);" at line 2313 and
> another at line 2386. (Each at the end of the scope of the "res2" local
> variables; the line numbers might be a bit different in 7.1.2 than in
> the 7.1.3 code I'm looking at.) However the trace shows that these
> queries are executed a couple hundred times apiece, and the waste from
> the unfreed query results shouldn't exceed a couple K each, so this
> doesn't explain hundreds of megs of bloat either. Still you might try
> fixing it and see if it makes a difference.
>
> The next move I can think of is to "kill -ABRT" the pg_dump run after
> it's gotten to some moderate size (50Meg at most) and then manually poke
> through the resulting core file to get a sense of what it's filling
> memory with.
>
> regards, tom lane
>