Message-ID: <4819EDF9.2080000@serioustechnology.com>
Date: Thu, 01 May 2008 12:21:13 -0400
From: Geoffrey <lists@serioustechnology.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: COPY errors when trying to convert from 7.4.19 to 8.3.1
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We are trying to test our process for upgrading our database from 7.4.19 
to 8.3.1.  We are following the instructions for dumping the 7.4.19 
database from:

http://www.postgresql.org/docs/8.3/static/install-upgrading.html

ie:

/usr/local/pg83/bin/pg_dumpall -h master -p 5434 |gzip > dump.gz

Create a new 8.3.1 cluster as follows:

/usr/local/pg83/bin/initdb -D /data/pgsql/master83

Load the data into the new cluster:

gunzip < dump.gz |/usr/local/pg83/bin/psql -d postgres

We know we have some old data in the database that does not meet current 
criteria, thus we are seeing errors such as:

ERROR:  insert or update on table "aaccess" violates foreign key 
constraint "$1"
DETAIL:  Key (code)=(CFSAVAUS000) is not present in table "arates".

What baffles us is, that although we get these errors, the data is still 
being inserted in to the table, in this case, the aaccess table.

Our expectation is that when COPY encounters an error such as this, it 
would stop and not continue to insert any more data into this table. 
Researching this indicates it is not true.  We have the same number of 
records in the 7.4.19 table as we do in our 8.3.1 table.

We are basing our 'expectation' on the documentation of COPY which reads:

COPY stops operation at the first error. This should not lead to 
problems in the event of a COPY TO, but the target table will already 
have received earlier rows in a COPY FROM. These rows will not be 
visible or accessible, but they still occupy disk space. This might 
amount to a considerable amount of wasted disk space if the failure 
happened well into a large copy operation. You might wish to invoke 
VACUUM to recover the wasted space.


What are we missing?

-- 
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
  - Benjamin Franklin