Problem using LIMIT 1 when only 1 record exists.

From: "Steve Thames" <steve(at)softlife(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Problem using LIMIT 1 when only 1 record exists.
Date: 2003-11-26 23:36:53
Message-ID: AHEHICJPCIJDKAMKKIHBKELICNAA.steve@softlife.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have a table with 14,655,100 rows in it. No unique keys.
I have an index on the fields "symbol", varchar[12], and "qdate", int4 as
format=YYYYMMDD.
I have a field called "expmoyr", char[6] as format=Mmm-YY.
I have a field called "last", float4.

The SQL command:
SELECT last FROM table WHERE symbol='Symbol' AND expmoyr='Mmm-YY' ORDER BY
qdate DESC LIMIT 1;

This query works fine when there is more than one record meeting the
criteria. When there is only 1, the query locks and no result is returned.

I have tried vacuuming and reindexing the entire database several times.
Please let me know if there is a fix to this.

Thanks.
Steve Thames
Softlife Consulting, Inc.
530-672-6908
www.softlife.com

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mark Iszler 2003-11-27 00:11:45 Problem with building PostgreSQL 7.4 from source
Previous Message Tom Lane 2003-11-26 20:55:10 Re: 7.4RC2 PANIC: insufficient room in FSM