Re: 8.2.4 signal 11 with large transaction

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bill Moran <wmoran(at)collaborativefusion(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: 8.2.4 signal 11 with large transaction
Date: 2007-07-20 20:05:39
Message-ID: 14803.1184961939@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Bill Moran <wmoran(at)collaborativefusion(dot)com> writes:
> Oddly, the query succeeds if it's fed into psql.

> I'm now full of mystery and wonder. It would appear as if the
> underlying problem has something to do with PHP, but why should this
> cause a backend process to crash?

Ah, I see it. Your PHP script is sending all 30000 INSERT commands
to the backend *in a single PQexec*, ie, one 37MB command string.
psql won't do that, it splits the input at semicolons.

Unsurprisingly, this runs the backend out of memory. (It's not the
command string that's the problem, so much as the 30000 parse and plan
trees...)

Unfortunately, in trying to prepare the error message, it tries to
attach the command text as the STATEMENT field of the log message.
All 37MB worth. And of course *that* gets an out-of-memory error.
Presto, infinite recursion, broken only by stack overflow (= SIGSEGV).

It looks like 8.1 and older are also vulnerable to this, it's just that
they don't try to log error statement strings at the default logging
level, whereas 8.2 does. If you cranked up log_min_error_statement
I think they'd fail too.

I guess what we need to do is hack the emergency-recovery path for
error-during-error-processing such that it will prevent trying to print
a very long debug_query_string. Maybe we should just not try to print
the command at all in this case, or maybe there's some intermediate
possibility like only printing the first 1K or so. Thoughts?

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-07-20 20:15:35 Re: [GENERAL] Reminder: PostgreSQL PDXPGDay + PostgreSQL Party
Previous Message Bill Moran 2007-07-20 19:39:26 Re: Solved? Re: 8.2.4 signal 11 with large transaction

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-07-20 20:24:01 Re: Memory leak in vac_update_relstats ?
Previous Message Gregory Stark 2007-07-20 19:52:02 Re: Memory leak in vac_update_relstats ?