Re: 7.0RC1: possible query and backend problem

At 5:16 PM -0700 4/19/2000, Michael Blakeley wrote:
>I've just upgraded to 7.0RC1 on a Solaris 2.6+patches system (U5
>with 256MB). I'm having some trouble with a brand-new query, so I
>don't know if this is a new bug or an old one. It has two parts:
>query execution and backend robustness.
>
>First, I'm getting a crash with this query (via Apache mod_perl and
>DBI, or via psql):
>
>Table U: id varchar(128) primary key, stop datetime, start datetime
>Table S1: id varchar(128) primary key, url varchar(512);
>Table S2: id varchar(128) primary key, url varchar(512);
>
>    SELECT id,date_part('epoch',sum(stop-start)),count(*),S1.url,S2.url
>    FROM U WHERE 'now'::datetime-start<'1 month'::interval
>    AND (id=S1.id OR id=S2.id) GROUP BY id,S1.url,S2.url;

For the benefit of anyone trying to work around crashes like this,
the query works fine for me as a UNION, instead.

    SELECT id,date_part('epoch',sum(stop-start)),count(*),S1.url
    FROM U WHERE 'now'::datetime-start<'1 month'::interval
    AND id=S1.id GROUP BY id,S1.url
    UNION
    SELECT id,date_part('epoch',sum(stop-start)),count(*),S2.url
    FROM U WHERE 'now'::datetime-start<'1 month'::interval
    AND id=S2.id GROUP BY id,S2.url;

Here's a backtrace, too:

# gdb ./bin/postgres ./data/base/sonicbox/core
GNU gdb 4.17
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or 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.
This GDB was configured as "sparc-sun-solaris2.6"...
Core was generated by `/usr/local/pgsql/bin/postmaster -i -N 512 -B
1024 -o -F'.
Program terminated with signal 11, Segmentation Fault.
Reading symbols from /usr/lib/libnsl.so.1...done.
Reading symbols from /usr/lib/libsocket.so.1...done.
Reading symbols from /usr/lib/libdl.so.1...done.
Reading symbols from /usr/lib/libm.so.1...done.
Reading symbols from /usr/lib/libc.so.1...done.
Reading symbols from /usr/lib/libmp.so.2...done.
Reading symbols from /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1...done.
Reading symbols from /usr/lib/nss_files.so.1...done.
#0  0x110f6c in make_one_rel_by_joins ()
(gdb) bt
#0  0x110f6c in make_one_rel_by_joins ()
#1  0x110d40 in make_one_rel ()
#2  0x11be74 in subplanner ()
#3  0x11bd98 in query_planner ()
#4  0x11ca40 in union_planner ()
#5  0x11c3fc in subquery_planner ()
#6  0x11c13c in planner ()
#7  0x16314c in pg_plan_query ()
#8  0x163428 in pg_exec_query_dest ()
#9  0x163270 in pg_exec_query ()
#10 0x164f60 in PostgresMain ()
#11 0x131738 in DoBackend ()
#12 0x130f08 in BackendStartup ()
#13 0x12f41c in ServerLoop ()
#14 0x12e978 in PostmasterMain ()
#15 0xd89f8 in main ()

-- Mike