Re: 8.0 -> 8.1 dump duplicate key problem? - Mailing list pgsql-hackers

> "Merlin Moncure" <merlin.moncure@rcsonline.com> 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)));