From: Ray Stell <stellr@cns.vt.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Mr. Dan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: COPY FROM command v8.1.4
Date: Wed, 13 Sep 2006 18:54:38 -0400
Message-ID: <20060913225438.GB2690@cns.vt.edu> (raw)
In-Reply-To: <13194.1158166226@sss.pgh.pa.us>
References: <BAY116-F350F077F2714231F971BD4D1280@phx.gbl>
<13194.1158166226@sss.pgh.pa.us>
You said "local Reiser FS." Maybe repeat on one of the others, Ext3/JFS?
Tom asked about hardware issues, is there nothing in syslog
that relates to the timing of the event? I don't recall you
responding in public to this. Maybe I missed it.
Just musing...
On Wed, Sep 13, 2006 at 12:50:26PM -0400, Tom Lane wrote:
> "Mr. Dan" <bitsandbytes88@hotmail.com> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: stellr@cns.vt.edu, tgl@sss.pgh.pa.us, bitsandbytes88@hotmail.com
Subject: Re: COPY FROM command v8.1.4
In-Reply-To: <20060913225438.GB2690@cns.vt.edu>
References: <BAY116-F350F077F2714231F971BD4D1280@phx.gbl> <13194.1158166226@sss.pgh.pa.us> <20060913225438.GB2690@cns.vt.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox