pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

-- 



view thread (20+ messages)  latest in thread

Message-ID: <20060913225438.GB2690@cns.vt.edu>
Permalink:  ../20060913225438.GB2690@cns.vt.edu/
Also on:    postgresql.org/message-id/20060913225438.GB2690@cns.vt.edu

 · 

reply

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