Re: 8.0 -> 8.1 dump duplicate key problem?

On 11/14/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The pairing seems very odd though: judging from the proximity of xmin
> and lastmod, the first and third rows were inserted at almost the same
> time, and they do *not* have equal keys; the rows they should have
> conflicted with were inserted some time earlier.

> Can you think of anything special about the client-side logic that might
> have created unusual behavior in this situation? Not that it's not
> clearly a backend-side bug, I'm just looking for a handle to attack it
> with. (If you can determine the exact SQL commands that were issued to
> create these records, it'd be great.)

> One question that seems interesting is whether there might have been a
> VACUUM working on the pkey index concurrently with the later insertions.
> If you can either positively confirm or positively deny that, it'd be
> useful information.

VACUUM is scheuled to run weekly, so we can rul that out. Can rule
out exotic transaction behavor (savepoint) and long running
transactions generally, save for pg_dump. In my app, all I/O save for
rare pgadmin edit is with PQExecParams/PQExecPrepared through libpq.
Inserts and updates are currently not ever prepared. Following is an
example of the insert logged from the ISAM drver logging utility.
Transaction load on the table is moderate to heavy but is 10:1
read/write ratio at least.

This is of course running windows...reading Rod's mail it seems we
might have similar problem (running 8.0.2). The update would be
virtually the same and matching on the p-key.

0.0181023 sec: insert into parts_order_line_file (prl_combined_key,
prl_seq_no, prl_combined_key_2, prl_item_no, prl_comment_desc,
prl_location, prl_workstation, prl_stock_loc, prl_qty, prl_adj_price,
prl_cost, prl_weight, prl_uom, prl_vendor_no, prl_vendor_part_no,
prl_track_this_part, prl_warranty_period, prl_comments_1,
prl_comments_2, prl_qty_shipped, prl_qty_still_on_bo,
prl_qty_credited, prl_credit_reason, prl_credit_reason_type,
prl_cancel_ship, prl_exchange_part, prl_authorization_code,
prl_item_status, prl_item_status_alpha, prl_cancel_flag,
prl_charge_type_flag, prl_ct_taxable_flag, prl_account_cat_code,
prl_retail_price, prl_line_needs_serials, prl_chrg_type_ship_indx,
prl_claim_type_flag, prl_attached_wc_seq_no, prl_attached_claim_type,
prl_already_issued, prl_returned_part_flag, prl_prev_qty_shipped,
prl_prev_qty_still_on_bo, prl_prev_qty_credited) values ($1, $2, $3,
$4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
$19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32,
$33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44) params:
$1= 00001126 $2=001 $3= 00001126 $4=BAR-000006 $5=BAR $6=PL-1 $7= $8=
$9= 0001.000 $10= 000016.50 $11= 000015.00000 $12=00050.00 $13=EA
$14=000006 $15=BAR-000006 $16=N $17= $18= $19= $20={" 0001.00","
0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00","
0000.00"," 0000.00"} $21={" 0000.00"," 0000.00"," 0000.00","
0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"}
$22={" 0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00","
0000.00"," 0000.00"," 0000.00"," 0000.00"}
$23={"","","","","","","","",""} $24={"","","","","","","","",""}
$25={"","","","","","","","",""} $26=N $27= $28=2 $29=A $30= $31= $32=
$33= $34= 000019.80 $35= $36=0 $37= $38=000 $39= $40= $41= $42={"
0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00","
0000.00"," 0000.00"," 0000.00"} $43={" 0000.00"," 0000.00","
0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00","
0000.00"} $44={" 0000.00"," 0000.00"," 0000.00"," 0000.00","
0000.00"," 0000.00"," 0000.00"," 0000.00"," 0000.00"}