6.5 cvs: can't drop table - Mailing list pgsql-hackers

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 itunder certain conditions; type "show copying" to see
theconditions.
 
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

#!/usr/local/bin/perl -w
# Crash Postgres with many joins 
# Oleg Bartunov, 1999

use strict;

$| = 1;
my $njoins = undef;
my $nrows = undef;
my @nitems = ();
my $option = undef;
my $vacuum = 0; 
my $explain = 0; 
my $geqo = 0; 
my $spec = 0; # 1 to create/use additional columns in join
my $bad_option = '';

while ( @ARGV ) {$option = shift @ARGV;if ( defined ($option) ) {   if ( $option =~ /^--with-vacuum$/ ) {       $vacuum
=1;   } elsif ($option =~ /^--explain$/ ) {       $explain = 1;   } elsif ( $option =~ /^--with-geqo$/) {       $geqo =
1;  } elsif ( $option =~ /^--help$/) {       &usage;   } elsif ( $option =~ /^--joins$/) {       $njoins = shift @ARGV;
     $njoins ||= '';       $bad_option .= " \t$option must be followed by number: $njoins \n" unless $njoins =~ /^\d+$/
 } elsif ( $option =~ /^--rows$/) {       $nrows = shift @ARGV;       $nrows ||= '';       $bad_option .=" \t$option
mustbe followed by number: $nrows \n" unless $nrows =~ /^\d+$/;   } else {       $bad_option .=  "\Unrecognized option:
$option!\n";   } }
 
}

&usage ($bad_option) if ( $bad_option );
&usage ($bad_option) unless ( defined ($option) );

die (" Not enough parameters: ") unless ( defined($njoins) && defined($nrows) );
my $tables = $njoins;
my $ntables = $tables+1;
eval  '@nitems = (' . '$nrows,'x$ntables . ')';


my $SQL = '';
for ( my $i = 0; $i <= $tables; $i++ ) { my $table = 't'. $i; $SQL .= qq^drop table $table;\ncreate table $table ( id
int4NOT NULL, a varchar(10)^;   if ( $i == 0 && $spec ) { # main table      for ( my $k = 1; $k <= $tables; $k++ ) {
    $SQL .= ', t'.$k.'_id int4';      }   }  $SQL .= qq^);\n^; $SQL .= qq^COPY $table FROM STDIN USING DELIMITERS
'|';\n^;for ( my $j = 1; $j <= $nitems[$i]; $j++ ) {     my @cols = ();   push @cols, ($j,'a'.$table);   if ( $i == 0
&&$spec ) { # main table - add cols      for ( my $k = 1; $k <= $tables; $k++ ) {        push @cols, $j;      }   }
$SQL.= join ('|',@cols)."\n";    } $SQL .= qq^\\.\n^;
 

# create indices  $SQL .= qq^create index id_$table on $table (id);\n^;  if ( $i == 0 && $spec) { # main table - create
add.indices      for ( my $k = 1; $k <= $tables; $k++ ) {         my $table = 't'.$k;         $SQL .= 'create index
'.$table.'_id_t0on t0 ('.$table.'_id);';         $SQL .= "\n";      }   }
 
}

# vacuum analyze
$SQL .= qq^vacuum analyze;\n^ if ( $vacuum );
print "$SQL\n";

# Query with joins
my $SELECT = '';
# Try GEQO
$SELECT .= qq^set GEQO to 'ON=$njoins';^ if ( $geqo );
$SELECT .= 'explain ' if ( $explain );
$SELECT .= 'select t0.a';
for ( my $i = 1; $i <= $njoins; $i++ ) { $SELECT .= ',t'.$i.'.a as t'.$i;
}
$SELECT .= "\n".' from t0  ';
for ( my $i = 1; $i <= $njoins; $i++ ) { $SELECT .= ',t'.$i;
}
$SELECT .= ( $spec ) ? "\n".' where t1.id = t0.t1_id ' : "\n".' where t1.id = t0.id ';
for ( my $i = 2; $i <= $njoins; $i++ ) { $SELECT .= ( $spec ) ? 'and t'.$i.'.id=t0.t'.$i.'_id ' : 'and t'.$i.'.id=t0.id
';
}
$SELECT .= ';';

print $SELECT,"\n";

sub usage { my $msg = shift; print " ERROR:\n$msg\n" if $msg;
print qq^Usage:
$0  --joins Njoins --rows Nrows [ options ]
Parameters:  Njoins    - the number of joins (Njoins < Ntables)  Nrows     - the number of rows
 Options:  --help           - print usage  --explain        - do not send query but explain  --with-vacuum    - vacuum
analyzebefore query  --with-geqo      - use GEQO for query
 
 Example:   $0  --joins 14 --rows 200 --explain | psql test
^;
print "\n";
exit;
}