> That's pretty bizarre. What's the datatype of the key column(s)?
>
> Can you reduce it to a smaller test case, or perhaps send me the full
> dump off-list? (270m is a bit much for email, but web or ftp would
> work ... also, presumably only the pkey column is needed to generate
> the error ...)
I just confirmed that there are duplicate p-keys in the source table :(.
Three currently but last week there were six. Just FYI I am not 100%
sure pg rebuilt the p-key with dups in it...I need to double check this.
esp=# select * from
esp-# (
esp(# select prl_combined_key, prl_seq_no, count(*) as c from
parts_order_line_file group by 1,2
esp(# ) q where q.c > 1;prl_combined_key | prl_seq_no | c
------------------+------------+--- 00136860 | 20 | 2 00136860 | 23 | 2 00137050 |
1 | 2
(3 rows)
esp=# \d parts_order_line_file Table "data1.parts_order_line_file" Column | Type
| Modifiers
--------------------------+-------------------------+-----------
[...]
Indexes: "parts_order_line_file_pkey" PRIMARY KEY, btree (prl_combined_key,
prl_seq_no) "parts_order_line_file_prl_exchange_part_key" UNIQUE, btree
(prl_exchange_part, id) "parts_order_line_file_prl_item_no_key" UNIQUE, btree (prl_item_no,
id) "parts_order_line_file_prl_trx_type_2_key" UNIQUE, btree
(prl_combined_key_2, prl_item_no, id)
I keep a timestamp on every row for last modified date:
esp=# select lastmod from parts_order_line_file where prl_combined_key =
' 00136860' and prl_seq_no
in (20, 23); lastmod
-------------------------2005-09-15 11:17:17.0622005-09-15 11:17:17.187
(2 rows)
There have been no schema changes since 9/15...
Merlin