Solved? Re: 8.2.4 signal 11 with large transaction

In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

> On 7/20/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
> > In response to Tom Lane <tgl@sss.pgh.pa.us>:
> >
> > > Bill Moran <wmoran@collaborativefusion.com> writes:
> > > > 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?
> > >
> > > I'd bet on PHP submitting the query via extended query protocol
> > > (PQexecParams or equivalent) instead of plain ol PQexec which is what
> > > psql uses.
> >
> > Doesn't appear that way.  The PHP source is somewhat cryptic, but I
> > don't seem much ambiguity here:
> >
> > pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query));
> >
> > There're no conditional blocks around that, so it's the only possible
> > choice when pg_query() gets called in a PHP script.  PHP exposes a
> > seperate pg_query_params() that wraps PQexecParams().
> >
> > > I don't speak PHP or have it installed here, so this example
> > > is hard for me to investigate.  Can someone make a reproducer that uses
> > > PQexecParams?
> >
> > Is there any way that this (or something similar) could still apply?
>
> I just ran your script, and only changed the connect string to reflect
> my environment.
>
> It ran smoothly against my workstations 8.1.8 pgsql install and
> against my reporting server's 8.2.4 installation, inserting 30001 rows
> in each.

Just great.  I love these "hard to pin down" issues ...

> I'm not familiar with the host=/tmp bit in the connect string, is that
> an explicit declaration of using unix local sockets and the directory
> to find it?

Yes.

> Does it work if you go to tcp/ip sockets and use a
> hostname etc... in your pg_connect?

Behaviour is the same.  We first experienced the problem in a development
environment where the PHP server is separate from the PG server, thus
we're using TCP sockets for that connection.

I'm starting to wonder if the OS could be sending the sig 11?

... time warp ...

Yup, that was it.  The OS was limiting the amount of memory a single
process could use via kern.maxdsiz (FreeBSD).  This was evident with
ulimit -d.

So, the fact remains that PG 8.1 returns an out of memory error when
it hits this, and PG 8.2 coredumps.  Is the 8.2 behaviour expected/
planned?  The out of memory error I would expect, the coredump I
would not.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023