X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4CD2B9FA380 for ; Wed, 13 Sep 2006 13:51:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 58175-02 for ; Wed, 13 Sep 2006 13:51:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DA40E9FA20E for ; Wed, 13 Sep 2006 13:51:44 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8DGoQrr013195; Wed, 13 Sep 2006 12:50:26 -0400 (EDT) To: "Mr. Dan" cc: pgsql-admin@postgresql.org Subject: Re: COPY FROM command v8.1.4 In-reply-to: References: Comments: In-reply-to "Mr. Dan" message dated "Wed, 13 Sep 2006 11:57:14 -0400" Date: Wed, 13 Sep 2006 12:50:26 -0400 Message-ID: <13194.1158166226@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.109 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/112 X-Sequence-Number: 23026 "Mr. Dan" writes: >> How are you doing the copies, exactly? SQL COPY command, psql \copy, >> something else? > We've tried SQL COY and psql \copy and always get random results - 0,1, or 2 > blocks of 25 rows missing. Hmph. If it happens with a SQL COPY command then psql seems to be off the hook, and that also eliminates some theories about dropped TCP packets and such. Would you check back in the source table for the COPY and see what the ctid values are for the missing rows? I'm wondering about a pattern like "the dropped rows of a group are all on the same disk page", ie, what's being missed is one whole page at a time. If that's what's happening, the only very plausible theory I can think of is that your disk drive is sometimes glitching and returning a page of all-zeroes instead of what it should return. Postgres will not complain about this in normal operation (because there are legitimate error-recovery scenarios where a zero page can be in a table); it'll just treat the page as empty. VACUUM will complain though, so the next step would be to set up a test table by copying your large table and then repeatedly run plain VACUUM on the test table. If you get sporadic warnings "relation foo page N is uninitialized --- fixing" then we have the smoking gun. Don't run this test directly on a valuable table, as each such message would mean you just lost another page of data :-( FWIW, I spent several hours yesterday evening copying 6GB tables around to see if I could reproduce any such problem, and I couldn't... regards, tom lane