I got some weird results when processing select statements with limit
and offset. I think its some kind of database corruption but I was
wondering what other's think.
Background:
The table I'm having the issue with is described below. The thing to
note is the primary key
ice=# \d nc_host_datum
Table "public.nc_host_datum"
Column | Type | Modifiers
----------------------+---------+-----------
host_id | bigint | not null
host_datum_type_id | integer | not null
host_datum_source_id | integer | not null
data | text | not null
Indexes:
"nc_host_datum_pkey" PRIMARY KEY, btree (host_id,
host_datum_type_id)
Foreign-key constraints:
"foreign_key_01" FOREIGN KEY (host_id) REFERENCES nc_host(host_id)
ON UPDATE CASCADE ON DELETE CASCADE
"foreign_key_02" FOREIGN KEY (host_datum_type_id) REFERENCES
nc_host_datum_type(host_datum_type_id) ON UPDATE RESTRICT ON DELETE
RESTRICT
"foreign_key_03" FOREIGN KEY (host_datum_source_id) REFERENCES
nc_host_datum_source(host_datum_source_id) ON UPDATE RESTRICT ON DELETE
RESTRICT
Problem:
I perform the following select (notice that the group by is by the
primary key).
select host_id, host_datum_type_id, count(*)
from nc_host_datum where host_id in
( select host_id
from nc_host
where audit_id=2041)
group by host_id, host_datum_type_id;
and get the following result (There are many more rows but these are all
the rows for host_id = 963711):
host_id | host_datum_type_id | count
-------------+------------------------------+---------
963711 | 58 | 1
963711 | 54 | 1
963711 | 39 | 1
963711 | 28 | 1
963711 | 27 | 1
Notice that there are 5 rows for host_id 963711 and the
host_datum_type_id's are all unique
Then I perform the following selects
SELECT host_id, host_datum_type_id, host_datum_source_id, data
FROM nc_host_datum INNER JOIN nc_host USING (host_id)
WHERE audit_id=2041
ORDER BY host_id
LIMIT 49 OFFSET 1372;
And
SELECT host_id, host_datum_type_id, host_datum_source_id, data
FROM nc_host_datum INNER JOIN nc_host USING (host_id)
WHERE audit_id=2041
ORDER BY host_id
LIMIT 49 OFFSET 1421;
A portion of the output follows.
host_id | host_datum_type_id | host_datum_source_id | data
---------+--------------------+----------------------+--------------
:
:
963710 | 58 | 17| harrish
963711 | 27 | 3 | 1
963711 | 28 | 3 | 1
(49 rows)
host_id | host_datum_type_id | host_datum_source_id | data
---------+--------------------+----------------------+--------------
963711 | 28 | 3 | 1
963711 | 58 | 17 | lmitchel
963711 | 39 | 3 | us.aegon.com
:
:
(49 rows)
Notice that host_id = 963711 and host_datum_type_id = 28 is repeated
twice. Since the offset is not overlapping, how can this happen? Any
ideas on how to fix this?
Thanks,
Barbara
