PostgreSQL 8.0.2beta1 (REL8_0_STABLE updated this morning)
FreeBSD 4.11-STABLE
gcc 2.95.4

While investigating somebody's question I ran the following query,
which caused the backend to segfault:

SELECT * FROM foo WHERE first_date <= now() AND last_date >= now();

I have to run so I can't put together a test case right now, but
here's the stack trace:

#0 convert_timevalue_to_scalar (value=1913, typid=1184) at selfuncs.c:2816
#1 0x81aa3e5 in convert_to_scalar (value=138003712, valuetypid=1184, scaledvalue=0xbfbfea50,
lobound=1913, hibound=1951, boundstypid=1082, scaledlobound=0xbfbfea58,
scaledhibound=0xbfbfea60) at selfuncs.c:2393
#2 0x81a8774 in scalarineqsel (root=0x8348228, operator=2361, isgt=1 '\001', vardata=0xbfbfeb00,
constval=138003712, consttype=1184) at selfuncs.c:547
#3 0x81a8ab1 in scalargtsel (fcinfo=0xbfbfeb50) at selfuncs.c:794
#4 0x81d4fbb in OidFunctionCall4 (functionId=104, arg1=137658920, arg2=2361, arg3=138001936,
arg4=0) at fmgr.c:1514
#5 0x81472af in restriction_selectivity (root=0x8348228, operator=2361, args=0x839be10,
varRelid=0) at plancat.c:427
#6 0x81344d1 in clause_selectivity (root=0x8348228, clause=0x8348958, varRelid=0,
jointype=JOIN_INNER) at clausesel.c:601
#7 0x8133eb0 in clauselist_selectivity (root=0x8348228, clauses=0x839c178, varRelid=0,
jointype=JOIN_INNER) at clausesel.c:116
#8 0x81355f3 in set_baserel_size_estimates (root=0x8348228, rel=0x8348800) at costsize.c:1491
#9 0x8133514 in set_plain_rel_pathlist (root=0x8348228, rel=0x8348800, rte=0x83487a0)
at allpaths.c:152
#10 0x81334de in set_base_rel_pathlists (root=0x8348228) at allpaths.c:135
#11 0x813341f in make_one_rel (root=0x8348228) at allpaths.c:79
#12 0x813d491 in query_planner (root=0x8348228, tlist=0x839b988, tuple_fraction=0,
cheapest_path=0xbfbfeeb8, sorted_path=0xbfbfeebc) at planmain.c:154
#13 0x813e176 in grouping_planner (parse=0x8348228, tuple_fraction=0) at planner.c:940
#14 0x813d8ca in subquery_planner (parse=0x8348228, tuple_fraction=0) at planner.c:327
#15 0x813d616 in planner (parse=0x8348228, isCursor=0, cursorOptions=0, boundParams=0x0)
at planner.c:130
#16 0x816f33f in pg_plan_query (querytree=0x8348228, boundParams=0x0) at postgres.c:647
#17 0x816f3e1 in pg_plan_queries (querytrees=0x839b8f8, boundParams=0x0, needSnapshot=0 '\000')
at postgres.c:715
#18 0x816f5ef in exec_simple_query (
query_string=0x8348018 "select * from foo where first_date <= now() and last_date >= now();")
at postgres.c:874
#19 0x8171b87 in PostgresMain (argc=4, argv=0x82c8258, username=0x82c8238 "mfuhr")
at postgres.c:3007
#20 0x814d429 in BackendRun (port=0x82de600) at postmaster.c:2839
#21 0x814cc6a in BackendStartup (port=0x82de600) at postmaster.c:2475
#22 0x814b1f6 in ServerLoop () at postmaster.c:1222
#23 0x814abce in PostmasterMain (argc=3, argv=0xbfbffc88) at postmaster.c:918
#24 0x811ea8d in main (argc=3, argv=0xbfbffc88) at main.c:268

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Mumble ... that patch I put in last week doesn't work at all, does it?
Back to the drawing board.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, Apr 01, 2005 at 02:46:10PM -0500, Tom Lane wrote:
I don't get the crash any more with the change you applied a little
while ago. Here's a test case that was crashing:

CREATE TABLE foo (
id serial PRIMARY KEY,
first_date date NOT NULL,
last_date date NOT NULL
);

CREATE INDEX foo_dates_idx ON foo (first_date, last_date);

INSERT INTO foo (first_date, last_date) VALUES ('2005-04-01', '2005-04-02');
INSERT INTO foo (first_date, last_date) VALUES ('2005-04-02', '2005-04-03');
INSERT INTO foo (first_date, last_date) VALUES ('2005-04-03', '2005-04-04');
INSERT INTO foo (first_date, last_date) VALUES ('2005-04-04', '2005-04-05');

ANALYZE foo;

SELECT * FROM foo WHERE first_date <= now() AND last_date >= now();
id | first_date | last_date
----+------------+------------
1 | 2005-04-01 | 2005-04-02
(1 row)

And now back to the "date - range" thread in pgsql-performance,
which is why I was looking at this in the first place....

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster