Re: pg_dump on older version of postgres eating huge - Mailing list pgsql-general

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@r-and-d /tmp]# strings core | grep "DROP TRIGGER" | wc -l
5450219
[root@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@r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"data_owner\"" | sort | uniq | wc -l
    107
[root@r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"data_owner\"" | wc -l
 347550
[root@r-and-d /tmp]# strings core | grep "DROP TRIGGER .* \"product\"" | sort | uniq | wc -l
    151
[root@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@iglou.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
>