Re: BUG #1393: Adding 'LIMIT 1' to the query halts forever

Hi Michael,

Here you go:

This is the create table statement:

CREATE TABLE jobstat_lc_q4_2004 (
jobstatid_q4_2004 serial NOT NULL,
jobid integer,
fetchtime timestamp without time zone NOT NULL,
stime timestamp without time zone,
mtime timestamp without time zone,
status character(1),
cpu_time integer,
req_jobfs bigint,
used_jobfs bigint,
req_walltime integer,
curr_walltime integer,
sys_time time without time zone,
vmemlim bigint,
used_vmem bigint,
used_mem bigint,
nodesdown text,
sys_timeint integer,
curr_cpu bigint,
curr_sys bigint
);

-- Grants
REVOKE ALL ON TABLE jobstat_lc_q3_2004 FROM PUBLIC;
GRANT SELECT ON TABLE jobstat_lc_q3_2004 TO apache;

-- Indexes
CREATE INDEX jobstat_lc_q4_2004_jobid ON jobstat_lc_q4_2004 USING btree
(jobid);
CREATE INDEX jobstat_lc_q4_2004_fetchtime ON jobstat_lc_q4_2004 USING btree
(fetchtime);
CREATE UNIQUE INDEX jobstat_lc_q4_2004_walltime ON
unq_jobstat_lc_q4_2004_jobid_fetch USING btree (jobid, fetchtime);

When I wrote the email, I tried this on a new empty table and I was able to
reproduce it, but I can't seem to reproduce it again (I don't remember the
exact CREATE statement I used + the indexes I created). Any way, since this
is the actual 'production' table in the database, and this is where the
problem happens every time, I'll give you a bit more information about the
table. The table has around 6040854 rows:

hpc=> SELECT COUNT(*) FROM jobstat_lc_q4_2004;
count
---------
6040854
(1 row)

I'm usually doing a search on a particular 'jobid' in my application, which
has no idea whether a jobid actually exists or not (as the id is provided by
the user). Running the following query with a bogus (non-existing) jobid
gets stuck forever (or for a very very very long time):

hpc=> SELECT jobid, curr_walltime, status, fetchtime FROM jobstat_lc_q4_2004
WHERE jobid = 123123 AND curr_walltime != 0 ORDER BY fetchtime DESC LIMIT 1;

If I run the same command *without* 'LIMIT 1', it returns instantly. I've
attached the log from pg_lock, as you requested, as well when I ran the
above query. Hope something can be sorted out.

Thanks for your time.

Regards,
Fahad

On 15/1/05 5:18 AM, "Michael Fuhr" <mike(at)fuhr(dot)org> wrote:

> 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.

--
main(){int j=12345;char t[]=":aAbcdefFgGhijklmnNopqrsStuUvwyz \n",
*i="dUGScUiAbpmwqbmgduAvpmmlzce\nlmGGUbFbzjdb";while(*i){j+=
strchr(t,*i++)-t;j%=sizeof t-1;putchar(t[j]);}return 0;}