Equality search on timestamp value returns no rows

From: Keaton Adams <kadams(at)mxlogic(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Equality search on timestamp value returns no rows
Date: 2008-07-23 19:24:26
Message-ID: C4ACE18A.4DDA%kadams@mxlogic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


PostgreSQL 8.1.4
RHEL 4.x

So we have run into an interesting problem I want to know if anyone else has encountered before. We have a scheduler process that 'hangs' on occasion and we have isolated the issue to Postgres not returning any records when there are actual records to return. Here is the table definition:

mxl=# \d mxl_scheduler_queue;
Table "public.mxl_scheduler_queue"
Column | Type | Modifiers
-----------------+--------------------------+----------------------------------------------------------
job_id | integer | not null default nextval(('mxl_id_seq'::text)::regclass)
type | integer | not null
id | integer | not null
scope | smallint | not null
status | smallint | not null default 0
start_time | timestamp with time zone |
created | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
status_modified | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
priority | smallint | not null
parent_id | integer |
parent_scope | smallint |

We run into a problem when an equality search on a timestamp column returns no records, even when there are records to return, as in this example:

mxl=# select * from mxl_scheduler_queue where status_modified = '2008-07-03 16:55:06.44695-06';
job_id | type | id | scope | status | start_time | created | status_modified | priority | parent_id | parent_scope
--------+------+----+-------+--------+------------+---------+-----------------+----------+-----------+--------------
(0 rows)

We have found that when we qualify the value with the timestamp clause, the records do return successfully:

mxl=# select * from mxl_scheduler_queue where status_modified = timestamp(5) with time zone '2008-07-03 16:55:06.44695-06';
job_id | type | id | scope | status | start_time | created | status_modified | priority | parent_id | parent_scope
--------+------+-------+-------+--------+------------------------+-------------------------------+------------------------------+----------+-----------+--------------
60589 | 2 | 59145 | 3 | 0 | 2008-07-02 15:00:00-06 | 2008-06-25 15:55:10.456385-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60565 | 2 | 58976 | 3 | 0 | 2008-06-30 09:00:00-06 | 2008-06-23 09:55:11.604895-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60087 | 3 | 59143 | 2 | 0 | 2008-07-01 10:00:00-06 | 2008-06-03 10:55:09.488425-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60567 | 2 | 59143 | 2 | 0 | 2008-07-01 10:00:00-06 | 2008-06-24 10:55:15.021056-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60094 | 3 | 58824 | 3 | 0 | 2008-07-01 13:00:00-06 | 2008-06-03 13:55:08.711156-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60096 | 3 | 59145 | 3 | 0 | 2008-07-02 15:00:00-06 | 2008-06-04 15:55:11.212787-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
(6 rows)

And if we dump the data, drop the table, and reload the records, the equality search as shown here works just fine:

mxl=# select * from mxl_scheduler_queue where status_modified = '2008-07-03 16:55:06.44695-06';
job_id | type | id | scope | status | start_time | created | status_modified | priority | parent_id | parent_scope
--------+------+-------+-------+--------+------------------------+-------------------------------+------------------------------+----------+-----------+--------------
60589 | 2 | 59145 | 3 | 0 | 2008-07-02 15:00:00-06 | 2008-06-25 15:55:10.456385-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60565 | 2 | 58976 | 3 | 0 | 2008-06-30 09:00:00-06 | 2008-06-23 09:55:11.604895-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60087 | 3 | 59143 | 2 | 0 | 2008-07-01 10:00:00-06 | 2008-06-03 10:55:09.488425-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60567 | 2 | 59143 | 2 | 0 | 2008-07-01 10:00:00-06 | 2008-06-24 10:55:15.021056-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60094 | 3 | 58824 | 3 | 0 | 2008-07-01 13:00:00-06 | 2008-06-03 13:55:08.711156-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
60096 | 3 | 59145 | 3 | 0 | 2008-07-02 15:00:00-06 | 2008-06-04 15:55:11.212787-06 | 2008-07-03 16:55:06.44695-06 | 200 | |
(6 rows)

Any ideas why the equality search wouldn't work in the first case, but after reloading the table data it works just fine?

Thanks,

Keaton

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Reyes 2008-07-23 19:26:31 Re: Any way to favor index scans, but not bitmap index scans?
Previous Message Tino Wildenhain 2008-07-23 19:10:46 Re: A couple of newbie questions ...