Thread
-
7.0.3 - Backend crash on simple SELECT query
Ivan Baldo <ivan@pilasnet.com> — 2001-03-05T16:22:53Z
Severity: Devastating Short description: Simple SELECT query with simple REGEXP comparison with simple ORDER and WITHOUT JOINS CRASHES BACKEND (seems to die in an infinite loop that consumes CPU and uses the hard disk). Long description: The query: SELECT t.* FROM t WHERE UPPER(sa) ~ 'SOME STRING' ORDER BY sa ASC LIMIT 25, 0; executed by the psql command line utility never finishes and cannot be cancelled neither. To create the table structure: CREATE TABLE "t" ( "c1" character(6), "d1" date, "i1" int4, "c2" character(74), "c3" character(74), "i2" int4, "sa" character(50), "c4" character, "c5" character(50), "c6" character(2), "c7" character, "d2" date, "c8" character(4) ); CREATE INDEX "t_pkey" on "t" using btree ( "c1" "bpchar_ops" ); CREATE INDEX "t_d1" on "t" using btree ( "d1" "date_ops" ); CREATE INDEX "t_i2" on "t" using btree ( "i2" "int4_ops" ); CREATE INDEX "t_sa" on "t" using btree ( "sa" "bpchar_ops" ); The table has 121422 tuples (counted with "select count(*) from t;"). EXPLAIN VERBOSE of the query says: QUERY DUMP: { INDEXSCAN :startup_cost 0.00 :total_cost 66398.74 :rows 1214 :width 124 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042 :restypmod 10 :resname c1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 10 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1082 :restypmod -1 :resname d1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1082 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23 :restypmod -1 :resname i1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 1042 :restypmod 78 :resname c2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 1042 :vartypmod 78 :varlevelsup 0 :varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 1042 :restypmod 78 :resname c3 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5 :vartype 1042 :vartypmod 78 :varlevelsup 0 :varnoold 1 :varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 23 :restypmod -1 :resname i2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 1042 :restypmod 54 :resname sa :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 7 :vartype 1042 :vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 1042 :restypmod 5 :resname c4 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1042 :restypmod 54 :resname c5 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 9 :vartype 1042 :vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 10 :restype 1042 :restypmod 6 :resname c6 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 10 :vartype 1042 :vartypmod 6 :varlevelsup 0 :varnoold 1 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 1042 :restypmod 5 :resname c7 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 11 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold 1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 1082 :restypmod -1 :resname d2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 12 :vartype 1082 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom { RESDOM :resno 13 :restype 1042 :restypmod 8 :resname c8 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 13 :vartype 1042 :vartypmod 8 :varlevelsup 0 :varnoold 1 :varoattno 13}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 641 :opid 1254 :opresulttype 16 } :args ({ EXPR :typeOid 25 :opType func :oper { FUNC :funcid 871 :functype 25 :funcisindex false :funcsize 0 :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1 :resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno -1 :varattno 1 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({ RELABELTYPE :arg { VAR :varno 1 :varattno 7 :vartype 1042 :vartypmod 54 :varlevelsup 0 :varnoold 1 :varoattno 7} :resulttype 25 :resulttypmod -1 })} { CONST :consttype 25 :constlen -1 :constisnull false :constvalue 17 [ 17 0 0 0 82 79 68 79 76 70 79 32 80 73 76 65 83 ] :constbyval false })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 11496766) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 } QUERY PLAN: Index Scan using t_sa on t (cost=0.00..66398.74 rows=1214 width=124) Things that avoid or does not trigger the crash: The crash cannot be reproduced with PostgreSQL 7.0.2 as comes from the SuSE 7.0 Professional distribution. Dropping the "t_sa" index solves the problem (creating it again triggers the crash again). Trying the same query on similar (though smaller) tables does not trigger the bug. Ripping the "LIMIT 25, 0" line or changing it to "LIMIT 136, 0" avoids the crash. Ripping the "ORDER BY sa ASC" line (since it doesn't use the index). Not using the "UPPER" function. Things that doesnt affect the bug: Dumping the database, then dropping it, then creating it and re-importing the dumped data has no effect. Changing the "LIMIT 25, 0" line with "LIMIT 135, 0" " or with "LIMIT 25, 1" or with "LIMIT 5, 0". Changing the "~" operator with the "=" or "LIKE" operators has no effect. Changing the "UPPER" with "LOWER". Changing the "t.*" with "t.sa". PostgreSQL information: Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2 Packages installed: pg_devel-7.0.3-1suse7.i386.rpm pg_lib-7.0.3-1suse7.i386.rpm pg_perl-7.0.3-1suse7.i386.rpm pg_serv-7.0.3-1suse7.i386.rpm pg_tcl-7.0.3-1suse7.i386.rpm pg_tk-7.0.3-1suse7.i386.rpm postgres-7.0.3-1suse7.i386.rpm /proc/$(pidof postmaster)/cmdline: /usr/bin/postmaster -i -D/var/lib/pgsql/data /proc/$(pidof postmaster)/environ: PWD=/root PAGER=less HOSTNAME=servidor LS_OPTIONS=-F -h --color ignoreeof=0 POVRAYOPT=-l/usr/lib/povray/include SUSE_DOC_HOST=localhost QTDIR=/usr/lib/qt LESSKEY=/etc/lesskey.bin ORGANIZATION=My organization LESSOPEN=| /usr/local/bin/lesspipe.sh %s MANPATH=/usr/local/man:/usr/share/man:/usr/man:/usr/X11R6/man: /usr/openwin/man:/usr/share/man/allman NNTPSERVER=news KDEDIR=/opt/kde LESS=-M-I-S USER=root LS_COLORS=no=00:fi=00:di=01;34:ln=01:pi=40;33:so=01;35:bd=40;33; 01:cd=40;33;01:ex=01;31:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32: *.bat=01;32:*.tar=00;31:*.tgz=00;31:*.rpm=00;31:*.arj=00;31:*.taz=00;31: *.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31: *.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35: *.png=01;35: HISTCONTROL=ignoredups MACHTYPE=i386-suse-linux XKEYSYMDB=/usr/X11R6/lib/X11/XKeysymDB LC_ALL=es_ES MAIL=/var/spool/mail/root LINES=50 LANG=es_ES GNOMEDIR=/opt/gnome COLORTERM=1 INFOPATH=/usr/local/info:/usr/share/info:/usr/info LOGNAME=root SHLVL=3 TEXINPUTS=:~/.TeX:/usr/doc/.TeX COLUMNS=80 MINICOM=-c on INFODIR=/usr/local/info:/usr/share/info:/usr/info SHELL=/bin/bash PRINTER=lp HOSTTYPE=i386 CDPATH=.:/usr/local/httpd/htdocs:/:/root OSTYPE=linux WINDOWMANAGER=/usr/X11R6/bin/icewm HOME=/root TERM=linux XNLSPATH=/usr/X11R6/lib/X11/nls no_proxy=localhost PATH=/sbin:/bin:/usr/sbin:/usr/bin LESSCHARSET=latin1 FROM_HEADER=pilasnet.com LC_COLLATE=POSIX _=/sbin/startproc PREVLEVEL=N RUNLEVEL=3 DAEMON=/usr/bin/postmaster Distribution/installation information: SuSE Linux 7.0 Professional version. Using 2.2.16 kernel as comes from SuSE (not recompiled). Using a 14gb ReiserFS partition for the backend located on a single IDE hard disk (not using LVM or RAID). LIBC information: GNU C Library stable release version 2.1.3, by Roland McGrath et al. Compiled by GNU CC version 2.95.2 19991024 (release). Compiled on a Linux 2.2.16 system on 2000-09-05. Available extensions: GNU libio by Per Bothner crypt add-on version 2.1 by Michael Glad and others linuxthreads-0.8 by Xavier Leroy NoVersion patch for broken glibc 2.0 binaries BIND-4.9.7-REL NIS(YP)/NIS+ NSS modules 0.19 by Thorsten Kukuk NSS V1 modules 2.0.2 by Thorsten Kukuk libthread_db work sponsored by Alpha Processor Inc Computer information: /proc/cpuinfo: processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 6 model name : Celeron (Mendocino) stepping : 5 cpu MHz : 400.914 cache size : 128 KB fdiv_bug : no hlt_bug : no sep_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 mmx fxsr bogomips : 799.54 /proc/meminfo: MemTotal: 130596 kB MemFree: 7796 kB MemShared: 0 kB Buffers: 12824 kB Cached: 37400 kB BigTotal: 0 kB BigFree: 0 kB SwapTotal: 136544 kB SwapFree: 122596 kB End notes: Well, sorry for this big bug report, I have put a lot of effort in giving the most detailed information I could and tried to follow your guidelines for bug reporting. I couldn't search the bug database, there was an error when I tried to do so. If you need more information or anything else, please contact me at ivan@pilasnet.com. -
Re: 7.0.3 - Backend crash on simple SELECT query
Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-06T16:08:47Z
Ivan Baldo <ivan@pilasnet.com> writes: > Simple SELECT query with simple REGEXP comparison with simple > ORDER > and WITHOUT JOINS CRASHES BACKEND (seems to die in an infinite loop that > consumes CPU and uses the hard disk). Evidently it's data-specific, since I see no problem if I just create an empty table per your CREATE and then try the query. > Dumping the database, then dropping it, then creating it and > re-importing the dumped data has no effect. Could you send me the table dump? Or at least a backtrace from the crashed backend? regards, tom lane