pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mr. Dan <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: COPY FROM command v8.1.4
Date: Wed, 13 Sep 2006 12:50:26 -0400
Message-ID: <13194.1158166226@sss.pgh.pa.us> (raw)
In-Reply-To: <BAY116-F350F077F2714231F971BD4D1280@phx.gbl>
References: <BAY116-F350F077F2714231F971BD4D1280@phx.gbl>

"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



view thread (20+ messages)  latest in thread

Message-ID: <13194.1158166226@sss.pgh.pa.us>
Permalink:  ../13194.1158166226@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13194.1158166226@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, bitsandbytes88@hotmail.com
  Subject: Re: COPY FROM command v8.1.4
  In-Reply-To: <13194.1158166226@sss.pgh.pa.us>
  References: <BAY116-F350F077F2714231F971BD4D1280@phx.gbl> <13194.1158166226@sss.pgh.pa.us>

* 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