7.0.3 - Backend crash on simple SELECT query - Mailing list pgsql-bugs

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.