The following bug has been logged online:

Bug reference: 1393
Logged by: Fahad
Email address: Fahad.Gilani@anusf.anu.edu.au
PostgreSQL version: 8.0.0rc2
Operating system: Linux
Description: Adding 'LIMIT 1' to the query halts forever if no data
present
Details:

Hi,

In an empty table, if I do this:

"SELECT some_field WHERE some_field = 45 ORDER BY time";

This returns instantly with no results. However, doing the following takes
forever to return:

"SELECT some_field WHERE some_field = 45 ORDER BY time LIMIT 1";

It's a pain because my application is pretty generic and does not know
beforehand whether 'some_field' with ID 45 exists or not OR whether the
table has been wiped out clean. Why does it take so long for this query to
return?


Fahad

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, Jan 12, 2005 at 11:08:55PM +0000, Fahad wrote:

> PostgreSQL version: 8.0.0rc2

8.0.0rc5 is the latest -- it would be best to test with that to get
the most recent bug fixes.

> In an empty table, if I do this:
>
> "SELECT some_field WHERE some_field = 45 ORDER BY time";

ERROR: column "some_field" does not exist

This isn't a valid query.

> This returns instantly with no results. However, doing the following takes
> forever to return:
>
> "SELECT some_field WHERE some_field = 45 ORDER BY time LIMIT 1";

This isn't a valid query either. Please post an actual test case --
the exact series of steps we can take to reproduce the problem.

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Fri, Jan 14, 2005 at 10:47:52PM +1100, Fahad G. wrote:

> I'm sorry, the query, as you would have thought, was:
>
> SELECT some_field FROM some_table WHERE some_field = 45 ORDER BY time LIMIT 1;

Yes, I assumed the query looked like that, and I couldn't reproduce
the problem with it. My point was that you haven't given us a
self-contained test case that we can use to reproduce the problem,
so we have to guess at what the missing parts are. Solving this
would be a lot easier if you'd just tell us what you're doing so
we don't have to spend unnecessary time guessing.

As I requested before, please provide the exact steps we can take
to reproduce the problem. Show the CREATE TABLE statement and any
other statements that occur before the SELECT statement. Show
everything that we can copy and paste into an empty database to
make the problem happen.

Since you say the query takes forever to return, it might be useful
to see the output of pg_locks. Run the SELECT query that locks up
in one session, then open another session and run the following
query:

SELECT relation::regclass, * FROM pg_locks;

Include the output of that query in your message.

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Fri, Jan 14, 2005 at 11:18:15AM -0700, Michael Fuhr wrote:
>
> Since you say the query takes forever to return, it might be useful
> to see the output of pg_locks.

Reminder to self: "takes forever to return" might mean "eventually
returns, but takes a long time". Should have asked for clarification
and EXPLAIN or EXPLAIN ANALYZE output.

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend