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
