Re: 8.0 -> 8.1 dump duplicate key problem?

> "Merlin Moncure"  writes:
> > When the dump gets to the point where the indexes/keys are built,
the
> > primary key fails to build due to duplicate key constraint failure.
> > However, after dump is complete, I can create the p-key without any
> > modification to the table and everything is fine.
> 
> 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 am working on pairing down a test case.  As you can imagine this is a
nasty business.  My best shot is to go to the source server and trying
to dump just that file, but that may mask the problem.

Come to think of it, I did make one configuration change: I bumped
work_mem after the dump was loaded after noticing a lot of activity in
pg_dump.

Table schema follows.  If the schema seems a bit odd, it is because it
was converted from an ISAM file. 

[Dennis: I am not the guy from IRC]

esp=# \d data1.parts_order_line_file             Table "data1.parts_order_line_file"         Column          |          Type           | Modifiers
--------------------------+-------------------------+-----------id                       | cuid                    |lastmod                  | ts                      |prl_combined_key         | character(9)            | not nullprl_seq_no               | pic_9_3                 | not nullprl_combined_key_2       | character(9)            |prl_item_no              | character varying(15)   |prl_comment_desc         | character varying(2500) |prl_location             | character(4)            |prl_workstation          | character(4)            |prl_stock_loc            | character(4)            |prl_qty                  | numeric(7,3)            |prl_adj_price            | numeric(8,2)            |prl_cost                 | numeric(11,5)           |prl_weight               | numeric(7,2)            |prl_uom                  | character(2)            |prl_vendor_no            | character(6)            |prl_vendor_part_no       | character varying(15)   |prl_track_this_part      | character(1)            |prl_warranty_period      | character varying(10)   |prl_comments_1           | character varying(30)   |prl_comments_2           | character varying(30)   |prl_qty_shipped          | numeric(6,2)[]          |prl_qty_still_on_bo      | numeric(6,2)[]          |prl_qty_credited         | numeric(6,2)[]          |prl_credit_reason        | character(2)[]          |prl_credit_reason_type   | character(1)[]          |prl_cancel_ship          | character(1)[]          |prl_exchange_part        | character(1)            |prl_authorization_code   | character varying(10)   |prl_item_status          | character(1)            |prl_item_status_alpha    | character(1)            |prl_cancel_flag          | character(1)            |prl_charge_type_flag     | character(1)            |prl_ct_taxable_flag      | character(1)            |prl_account_cat_code     | character(6)            |prl_retail_price         | numeric(8,2)            |prl_line_needs_serials   | character(1)            |prl_chrg_type_ship_indx  | pic_9_1                 |prl_claim_type_flag      | character(1)            |prl_attached_wc_seq_no   | pic_9_3                 |prl_attached_claim_type  | character varying(10)   |prl_already_issued       | character(1)            |prl_returned_part_flag   | character(1)            |prl_prev_qty_shipped     | numeric(6,2)[]          |prl_prev_qty_still_on_bo | numeric(6,2)[]          |prl_prev_qty_credited    | numeric(6,2)[]          |
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)

CREATE DOMAIN public.pic_9_3 AS int2 DEFAULT 0  CONSTRAINT valid_range CHECK (((VALUE >= 0) AND (VALUE <= 999)));