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.
