Re: Debugging guidance

"Rich Garabedian" <richg@mav-mail.com> writes:
> I've inherited a fairly complex database that makes heavy use of plpgsql.
> Recently, this database has been crashing at random times when it tries to
> execute a specific plpgsql function.

Ugh.

> I'm very new to postgreSQL, and I'm not much of database guru either. Can
> anyone suggest further debugging avenues?

1. Update to PG 7.2.3.  We don't put out dot-releases without good
reason.  (It's entirely possible that your problem is just the
plpgsql_dstring_append bug fixed in 7.2.2.)  If you still see the
problem, then:

2. Build the new installation with --enable-debug, and maybe also
--enable-cassert.  (--enable-debug is harmless to leave on in
production, except for wasting a couple of megabytes of disk space.
--enable-cassert will slow things down a trifle, so you'd only want
to use it when chasing a problem, IMHO.)  I am not sure whether any
RPMs come built this way; you may need to build from source.
(Anyone know if an SRPM can easily be built with --enable-debug added?)

3. Make sure the postmaster is started with "ulimit -c unlimited";
this is not the default on a lot of Linux distros.  This way, you'll
get a core-dump file in the database subdirectory when a backend
crash occurs.

4. Use gdb to get a stack backtrace from the core file, and send it
to pgsql-hackers for more help.

            regards, tom lane