6.5 cvs: can't drop table

I had to destroy test database to continue my tests.
Here is backtrace from postgres crashing on 10 tables joining:

14:10[mira]:~/app/sql>mkjoindata.pl --joins 10 --rows 20 | psql test

...............
select t0.a,t1.a as t1,t2.a as t2,t3.a as t3,t4.a as t4,t5.a as t5,t6.a as t6,t7.a as t7,t8.a as t8,t9.a as t9,t10.a as t10
from t0 ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10
where t1.id = t0.id and t2.id=t0.id and t3.id=t0.id and t4.id=t0.id and t5.id=t0.id and t6.id=t0.id and t7.id=t0.id and t8.id=t0.id and t9.id=t0.id and t10.id=t0.id ;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.

gdb /usr/local/pgsql/bin/postmaster core
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i486-slackware-linux),
Copyright 1996 Free Software Foundation, Inc...
Core was generated by /usr/local/pgsql/bin/postgres localhost megera test idle
'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /lib/libdl.so.1...done.
Reading symbols from /lib/libm.so.5...done.
Reading symbols from /usr/lib/libreadline.so...done.
Reading symbols from /usr/lib/libhistory.so...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /lib/libncurses.so.3.0...done.
Reading symbols from /usr/lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
#0 0x80b6065 in equal ()
(gdb) bt
#0 0x80b6065 in equal ()
#1 0x80c7652 in pathorder_match ()
#2 0x80c7900 in better_path ()
#3 0x80c7863 in add_pathlist ()
#4 0x80bf551 in update_rels_pathlist_for_joins ()
#5 0x80c8b8b in gimme_tree ()
#6 0x80c8ae7 in geqo_eval ()
#7 0x80c99f2 in random_init_pool ()
#8 0x80c8c7b in geqo ()
#9 0x80bd6e6 in make_one_rel_by_joins ()
#10 0x80bd5ee in make_one_rel ()
#11 0x80c1e81 in subplanner ()
#12 0x80c1dff in query_planner ()
#13 0x80c2173 in union_planner ()
#14 0x80c1f55 in planner ()
#15 0x80e22e7 in pg_parse_and_plan ()
#16 0x80e240b in pg_exec_query_dest ()
#17 0x80e23cc in pg_exec_query ()
#18 0x80e3518 in PostgresMain ()
#19 0x80cc72c in DoBackend ()
#20 0x80cc26b in BackendStartup ()
#21 0x80cb9e7 in ServerLoop ()
#22 0x80cb573 in PostmasterMain ()
---Type <return> to continue, or q <return> to quit---
#23 0x80a2999 in main ()
#24 0x806131e in _start ()
(gdb)

Regards,

Oleg

On Tue, 25 May 1999, Oleg Bartunov wrote:

Date: Tue, 25 May 1999 13:53:19 +0400 (MSD)
From: Oleg Bartunov <oleg@sai.msu.su>
To: hackers@postgreSQL.org
Cc: tgl@sss.pgh.pa.us
Subject: [HACKERS] 6.5 cvs: can't drop table

Hi,

today I did some tests with current 6.5 from cvs and multiple joins.
I did unpredictable server crashes, i.e. sometimes query works
sometimes crashes. After about a hour of my experiments I can't drop table in
my test database:

13:55[mira]:~/app/sql>mkjoindata.pl --joins 10 --rows 20 | psql test

mkjoindata.pl - is my test script specially rewritten to get parameters
from command line. It generates test data, sql commands and automatize
process of postgres crashing :-) I attach this new version to my post.

Regards,

Oleg

PS.
Tom (Lane), sometimes I got an old behaivour of postgres on big joins -
all memory (ram+swap) exhausted. I remember a week ago that was fixed
and I certainly did the same tests without any problem.

drop table t0;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.

Backtrace:
mira:/usr/local/pgsql/data/base/test# gdb /usr/local/pgsql/bin/postmaster core
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i486-slackware-linux),
Copyright 1996 Free Software Foundation, Inc...
Core was generated by /usr/local/pgsql/bin/postgres localhost megera test DROP
'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /lib/libdl.so.1...done.
Reading symbols from /lib/libm.so.5...done.
Reading symbols from /usr/lib/libreadline.so...done.
Reading symbols from /usr/lib/libhistory.so...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /lib/libncurses.so.3.0...done.
Reading symbols from /usr/lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
#0 0x806aa2b in heapgettup ()
(gdb) bt
#0 0x806aa2b in heapgettup ()
#1 0x806b7d1 in heap_getnext ()
#2 0x807ca56 in DeleteTypeTuple ()
#3 0x807cbb5 in heap_destroy_with_catalog ()
#4 0x8083128 in RemoveRelation ()
#5 0x80e41ef in ProcessUtility ()
#6 0x80e2486 in pg_exec_query_dest ()
#7 0x80e23cc in pg_exec_query ()
#8 0x80e3518 in PostgresMain ()
#9 0x80cc72c in DoBackend ()
#10 0x80cc26b in BackendStartup ()
#11 0x80cb9e7 in ServerLoop ()
#12 0x80cb573 in PostmasterMain ()
#13 0x80a2999 in main ()
#14 0x806131e in _start ()
(gdb)

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Oleg Bartunov <oleg@sai.msu.su> writes:

today I did some tests with current 6.5 from cvs and multiple joins.
I did unpredictable server crashes, i.e. sometimes query works
sometimes crashes.

I have a theory about why the results are random: the GEQO planner
deliberately uses random numbers to generate plans, so you don't
always get the same plan out of it. Whatever bug you are seeing
occurs only for a particular plan path. (I haven't had any luck
repeating your crash here, so the bug may be platform-specific.)

It bothers me that the GEQO results are not reliably reproducible
across platforms; that complicates debugging. I have been thinking
about suggesting that we ought to change GEQO to use a fixed random
seed value by default, with the variable random seed being available
only as a *non default* option. Comments anyone?

In the meantime, you could try setting up a pgsql/data/pg_geqo file
with a specific Random_Seed NNN line, and try different NNN values
until you find one that will reliably trigger the failure. That
would help in reproducing the problem elsewhere.

After about a hour of my experiments I can't drop table in
my test database:

If you crash the backend enough times, you shouldn't be too surprised
that your database gets corrupted ... I think this is just collateral
damage.

regards, tom lane

On Tue, 25 May 1999, Tom Lane wrote:

Date: Tue, 25 May 1999 10:15:43 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] 6.5 cvs: can't drop table

Oleg Bartunov <oleg@sai.msu.su> writes:

today I did some tests with current 6.5 from cvs and multiple joins.
I did unpredictable server crashes, i.e. sometimes query works
sometimes crashes.

I have a theory about why the results are random: the GEQO planner
deliberately uses random numbers to generate plans, so you don't
always get the same plan out of it. Whatever bug you are seeing
occurs only for a particular plan path. (I haven't had any luck
repeating your crash here, so the bug may be platform-specific.)

It bothers me that the GEQO results are not reliably reproducible
across platforms; that complicates debugging. I have been thinking
about suggesting that we ought to change GEQO to use a fixed random
seed value by default, with the variable random seed being available
only as a *non default* option. Comments anyone?

In the meantime, you could try setting up a pgsql/data/pg_geqo file
with a specific Random_Seed NNN line, and try different NNN values
until you find one that will reliably trigger the failure. That
would help in reproducing the problem elsewhere.

I have rather stable crash under 2.0.37, see below

After about a hour of my experiments I can't drop table in
my test database:

If you crash the backend enough times, you shouldn't be too surprised
that your database gets corrupted ... I think this is just collateral
damage.

Got cvs update, reinstall pgsql, run my test and after several
success got the same crash :-) You probably right - this could be
connected with OS - Linux 2.0.37, I installed new kernel (old one was 2.0.36)
several days ago. I'll move back to 2.0.36 and will see what happens.
Interesting that I never get a crash on the same test (even 20 tables)
on my home machine which is running 2.2.9 ! I also run test under
FreeBSD 3.1 release (elf) and also no problems.

As usual, here is a backtrace :-)

Regards,
Oleg

PS. btw, it seems Jan fixed the bug with pg_dump and view !

where t1.id = t0.id and t2.id=t0.id and t3.id=t0.id and t4.id=t0.id and t5.id=t0.id and t6.id=t0.id and t7.id=t0.id and t8.id=t0.id and t9.id=t0.id and t10.id=t0.id and t11.id=t0.id and t12.id=t0.id and t13.id=t0.id and t14.id=t0.id and t15.id=t0.id and
t16.id=t0.id and t17.id=t0.id and t18.id=t0.id and t19.id=t0.id and t20.id=t0.id ;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
mira:/usr/local/pgsql/data/base/test$ l core
-rw------- 1 postgres users 11784192 May 25 19:07 core
mira:/usr/local/pgsql/data/base/test$ gdb /usr/local/pgsql/bin/postmaster core
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.16 (i486-slackware-linux),
Copyright 1996 Free Software Foundation, Inc...
Core was generated by /usr/local/pgsql/bin/postgres localhost megera test idle '.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /lib/libdl.so.1...done.
Reading symbols from /lib/libm.so.5...done.
Reading symbols from /usr/lib/libreadline.so...done.
Reading symbols from /usr/lib/libhistory.so...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /lib/libncurses.so.3.0...done.
Reading symbols from /usr/lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
Reading symbols from /usr/lib/libc.so.5...done.
Reading symbols from /lib/ld-linux.so.1...done.
#0 0x80c76af in pathorder_match ()
(gdb) bt
#0 0x80c76af in pathorder_match ()
#1 0x80c7900 in better_path ()
#2 0x80c7863 in add_pathlist ()
#3 0x80bf515 in update_rels_pathlist_for_joins ()
#4 0x80c8b8b in gimme_tree ()
#5 0x80c8ae7 in geqo_eval ()
#6 0x80c8d12 in geqo ()
#7 0x80bd6e6 in make_one_rel_by_joins ()
#8 0x80bd5ee in make_one_rel ()
#9 0x80c1e81 in subplanner ()
#10 0x80c1dff in query_planner ()
#11 0x80c2173 in union_planner ()
#12 0x80c1f55 in planner ()
#13 0x80e2497 in pg_parse_and_plan ()
#14 0x80e25bb in pg_exec_query_dest ()
#15 0x80e257c in pg_exec_query ()
#16 0x80e36c8 in PostgresMain ()
#17 0x80cc72c in DoBackend ()
#18 0x80cc26b in BackendStartup ()
#19 0x80cb9e7 in ServerLoop ()
#20 0x80cb573 in PostmasterMain ()
#21 0x80a2999 in main ()
#22 0x806131e in _start ()
(gdb)

regards, tom lane

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

I hate to say this, but I think we need postgres compiled with debugging
symbols, -g, so we can see the parameters being passed to the functions.

#0 0x80b6065 in equal ()
#1 0x80c7652 in pathorder_match ()
#2 0x80c7900 in better_path ()
#3 0x80c7863 in add_pathlist ()
#4 0x80bf551 in update_rels_pathlist_for_joins ()
#5 0x80c8b8b in gimme_tree ()
#6 0x80c8ae7 in geqo_eval ()
#7 0x80c99f2 in random_init_pool ()
#8 0x80c8c7b in geqo ()
#9 0x80bd6e6 in make_one_rel_by_joins ()
#10 0x80bd5ee in make_one_rel ()
#11 0x80c1e81 in subplanner ()
#12 0x80c1dff in query_planner ()
#13 0x80c2173 in union_planner ()
#14 0x80c1f55 in planner ()
#15 0x80e22e7 in pg_parse_and_plan ()
#16 0x80e240b in pg_exec_query_dest ()
#17 0x80e23cc in pg_exec_query ()
#18 0x80e3518 in PostgresMain ()
#19 0x80cc72c in DoBackend ()
#20 0x80cc26b in BackendStartup ()
#21 0x80cb9e7 in ServerLoop ()
#22 0x80cb573 in PostmasterMain ()
---Type <return> to continue, or q <return> to quit---
#23 0x80a2999 in main ()
#24 0x806131e in _start ()
(gdb)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Oleg Bartunov <oleg@sai.msu.su> writes:

today I did some tests with current 6.5 from cvs and multiple joins.
I did unpredictable server crashes, i.e. sometimes query works
sometimes crashes.

I have a theory about why the results are random: the GEQO planner
deliberately uses random numbers to generate plans, so you don't
always get the same plan out of it. Whatever bug you are seeing
occurs only for a particular plan path. (I haven't had any luck
repeating your crash here, so the bug may be platform-specific.)

It bothers me that the GEQO results are not reliably reproducible
across platforms; that complicates debugging. I have been thinking
about suggesting that we ought to change GEQO to use a fixed random
seed value by default, with the variable random seed being available
only as a *non default* option. Comments anyone?

I would leave the random alone. There may be some advantages to having
it random.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

It bothers me that the GEQO results are not reliably reproducible
across platforms; that complicates debugging. I have been thinking
about suggesting that we ought to change GEQO to use a fixed random
seed value by default, with the variable random seed being available
only as a *non default* option. Comments anyone?

A few platforms (e.g. AIX) have their own random implementation, so even
with
a fixed seed they produce different randoms than others :-(
It probably still helps iff behavior is predictable on the local machine.

But: I think we use rand for some security issue. We would'nt want to make
that predictable.

Andreas

On Tue, 25 May 1999, Bruce Momjian wrote:

Date: Tue, 25 May 1999 11:06:32 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: hackers@postgreSQL.org, tgl@sss.pgh.pa.us
Subject: Re: [HACKERS] 6.5 cvs: can't drop table

I hate to say this, but I think we need postgres compiled with debugging
symbols, -g, so we can see the parameters being passed to the functions.

No crashes with -O0 -g :-) I have egcs 1.12 release installed at this
machine as well as at home. I used the same optimization -O2 -mpentium
but at work postgres crashes while at home works like a charm.

btw,

just got new updates from cvs - a bunch of files !

Two problems:
1. at work:

make[3]: Entering directory /home/postgres/cvs/pgsql/src/backend/utils/mb'
gcc -I../../../include -I../../../backend -O2 -mpentium -Wall -Wmissing-prototypes -I../.. -DMULTIBYTE=KOI8 -c conv.c -o conv.o
conv.c: In function ig52mic':
conv.c:387: parse error before '
conv.c:392: parse error before lse'
conv.c:378: warning: 1' might be used uninitialized in this function
conv.c: At top level:
conv.c:422: parse error before }'
conv.c:423: warning: type defaults to nt' in declaration of '
conv.c:423: warning: data definition has no type or storage class
conv.c:424: parse error before }'
make[3]: *** [conv.o] Error 1
make[3]: Leaving directory /home/postgres/cvs/pgsql/src/backend/utils/mb'
make[2]: *** [submake] Error 2

2. at home:

postmaster.c: In function erverLoop':
postmaster.c:668: too few arguments to function ettimeofday'
postmaster.c:707: too few arguments to function ettimeofday'
postmaster.c:666: warning: unused variable z'
postmaster.c: In function oBackend':
postmaster.c:1512: too few arguments to function ettimeofday'
postmaster.c:1463: warning: unused variable z'
make[2]: *** [postmaster.o] Error 1
make[2]: Leaving directory /u/postgres/cvs/pgsql/src/backend/postmaster'

This problem I've seen already !

#0 0x80b6065 in equal ()
#1 0x80c7652 in pathorder_match ()
#2 0x80c7900 in better_path ()
#3 0x80c7863 in add_pathlist ()
#4 0x80bf551 in update_rels_pathlist_for_joins ()
#5 0x80c8b8b in gimme_tree ()
#6 0x80c8ae7 in geqo_eval ()
#7 0x80c99f2 in random_init_pool ()
#8 0x80c8c7b in geqo ()
#9 0x80bd6e6 in make_one_rel_by_joins ()
#10 0x80bd5ee in make_one_rel ()
#11 0x80c1e81 in subplanner ()
#12 0x80c1dff in query_planner ()
#13 0x80c2173 in union_planner ()
#14 0x80c1f55 in planner ()
#15 0x80e22e7 in pg_parse_and_plan ()
#16 0x80e240b in pg_exec_query_dest ()
#17 0x80e23cc in pg_exec_query ()
#18 0x80e3518 in PostgresMain ()
#19 0x80cc72c in DoBackend ()
#20 0x80cc26b in BackendStartup ()
#21 0x80cb9e7 in ServerLoop ()
#22 0x80cb573 in PostmasterMain ()
---Type <return> to continue, or q <return> to quit---
#23 0x80a2999 in main ()
#24 0x806131e in _start ()
(gdb)

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@candle.pha.pa.us            |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83