Re: repeatable crash generating two column index

I'm working the example in "Postgresql Developer's Handbook", page
218. I can create an index on a single column no problem, but when I
try to create a index over two columns ->

CREATE INDEX idx_id_perftest ON perftest (id, astring);

the backend crashes every time. I see the following in the logs...

2002-07-03 08:33:29 DEBUG: server process (pid 53454) was terminated by signal 11
2002-07-03 08:33:29 DEBUG: terminating any other active server processes
2002-07-03 08:33:29 DEBUG: all server processes terminated; reinitializing shared memory and semaphores

I can create/drop a single column index after the crash/recovery no problem.

I am using FreeBSD 4.6, Postrgresql 7.2.1. I'm not including the code
because it's copyright but can forward to a debugger. The code just
generates 10 million columns with an ID, and a random number and
string...

mvh=> select * from perftest limit 10;
id | astring | anumber
----+----------+----------
1 | pIscM5Kh | 35888787
2 | b8FSfFCU | 7206043
3 | 8lkWXbbj | 48537449
4 | DqLhIiZq | 15229027
5 | ic31Ckk9 | 3910172
6 | Gskx5Fqk | 11816527
7 | saifh1ia | 26180953
8 | mlbmdya2 | 5189701
9 | jfOB6cVe | 34855048
10 | cAUg0yIo | 91032333
(10 rows)

- Mike H.

On Wed, Jul 03, 2002 at 10:58:34AM -0700, Mike Harding wrote:
> CREATE INDEX idx_id_perftest ON perftest (id, astring);
>
> the backend crashes every time. I see the following in the logs...
>
> 2002-07-03 08:33:29 DEBUG: server process (pid 53454) was terminated by signal 11
> 2002-07-03 08:33:29 DEBUG: terminating any other active server processes
> 2002-07-03 08:33:29 DEBUG: all server processes terminated; reinitializing shared memory and semaphores

Can you get a backtrace using gdb? If possible, recompile Postgres with
debugging symbols (-g), and run gdb on the core file that should be
produced when the backend crashes.

If that's not possible, can you forward me the relavant bits of the
schema, and the script you used to produce the data?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Mike Harding <mvh(at)ix(dot)netcom(dot)com> writes:
> CREATE INDEX idx_id_perftest ON perftest (id, astring);
> 2002-07-03 08:33:29 DEBUG: server process (pid 53454) was terminated by signal 11

[ scratches head... ] Seems something awfully broken about your
installation. Are you able to run the regression tests successfully?

Can you provide a debugger backtrace from the corefile left by the
crashing backend?

regards, tom lane

I'm using the stock freebsd port installation.

Interestingly, this morning, I was able to create the index fine.

Then I dropped it and did an index just using id;

Then I dropped that and tried the original index again over both
columns.

I then get errors like

ERROR: MemoryContextAlloc: invalid request size 2353010684
ERROR: MemoryContextAlloc: invalid request size 1885489220

and the third and fourth time I tried it it worked!

I'll try running the regression test later today...

- Mike H.

On Thu, 2002-07-04 at 10:04, Tom Lane wrote:
> Mike Harding <mvh(at)ix(dot)netcom(dot)com> writes:
> > CREATE INDEX idx_id_perftest ON perftest (id, astring);
> > 2002-07-03 08:33:29 DEBUG: server process (pid 53454) was terminated by signal 11
>
> [ scratches head... ] Seems something awfully broken about your
> installation. Are you able to run the regression tests successfully?
>
> Can you provide a debugger backtrace from the corefile left by the
> crashing backend?
>
> regards, tom lane
>

On Thu, Jul 04, 2002 at 01:04:17PM -0400, Tom Lane wrote:
> Mike Harding <mvh(at)ix(dot)netcom(dot)com> writes:
> > CREATE INDEX idx_id_perftest ON perftest (id, astring);
> > 2002-07-03 08:33:29 DEBUG: server process (pid 53454) was terminated by signal 11
>
> [ scratches head... ] Seems something awfully broken about your
> installation. Are you able to run the regression tests successfully?

Another possibility is that there's a hardware problem at work. Can
you check for bad RAM using memtest86? (http://www.memtest86.com/)

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Mike Harding <mvh(at)ix(dot)netcom(dot)com> writes:
> I then get errors like

> ERROR: MemoryContextAlloc: invalid request size 2353010684
> ERROR: MemoryContextAlloc: invalid request size 1885489220

> and the third and fourth time I tried it it worked!

This is sounding like a corrupted-data issue. I'd suggest seeing
if you can dump and reload the table. Very likely you will find
that there are some corrupted rows :-(

regards, tom lane