Re: 8.0 -> 8.1 dump duplicate key problem?

> "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)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 null
prl_seq_no | pic_9_3 | not null
prl_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)));