SEO

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/s...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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Geoffrey <lists@serioustechnology.com> writes:
> 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/s...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.

Well, the insert has already happened: the error is actually coming out
when the dump script tries to do ALTER TABLE ADD CONSTRAINT FOREIGN KEY.
(The message is perhaps a bit misleading if you don't notice which
command caused it.)

What this looks like to me is a misordering of the dump operations such
that we're trying to do the ALTER on aaccess after loading its data,
but before loading arates's data. In theory 8.3's pg_dump should
avoid such mistakes, but it's possible you've got a case that fools it.

Anyway, the first thing you should do to investigate is to check exactly
what's the order of operations in the script and which command is
causing the failure. If the data involved is too large to make it
convenient to eyeball the dump script with an editor, you might try
running the dump script with log_statement = all so you can see just the
SQL commands in the postmaster log.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Geoffrey wrote:
> 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/s...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:

Are you modifying the dump before the import with the new criteria
adjustments? Or is this criteria from changes between 7.4 and 8.3?

> 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.
>

>
>
> What are we missing?
>

I am guessing here - I believe that the message is misleading.

If you look at the dumpall output you may notice the order in which some
things are done.

....
....
create table....
copy from stdin....
alter table add constraint....
create index....
....
....

My guess is that the error is coming from the add constraint which is
invoked after the data has been inserted.

Check your table defs in 8.3 and see if the fk constraints are in place.

You would have two choices - clean up the data in 7.4 before the export
or clean up after the import and before the fk constraints are added.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Tom Lane wrote:
> Geoffrey <lists@serioustechnology.com> writes:
>> 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/s...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.
>
> Well, the insert has already happened: the error is actually coming out
> when the dump script tries to do ALTER TABLE ADD CONSTRAINT FOREIGN KEY.
> (The message is perhaps a bit misleading if you don't notice which
> command caused it.)
>
> What this looks like to me is a misordering of the dump operations such
> that we're trying to do the ALTER on aaccess after loading its data,
> but before loading arates's data. In theory 8.3's pg_dump should
> avoid such mistakes, but it's possible you've got a case that fools it.
>
> Anyway, the first thing you should do to investigate is to check exactly
> what's the order of operations in the script and which command is
> causing the failure. If the data involved is too large to make it
> convenient to eyeball the dump script with an editor, you might try
> running the dump script with log_statement = all so you can see just the
> SQL commands in the postmaster log.

It's manageable, so I'll take an eye at it. As always, thanks for your
insights Tom.

--
Until later, Geoffrey

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Shane Ambler wrote:
> Geoffrey wrote:
>> 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/s...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:
>
> Are you modifying the dump before the import with the new criteria
> adjustments? Or is this criteria from changes between 7.4 and 8.3?

We aren't making any changes to the dump data. It's likely caused by
data that was quite old in the 7.4 database and new constraints were
added since that data was created.

>
>> 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.
>>
>
>>
>>
>> What are we missing?
>>
>
> I am guessing here - I believe that the message is misleading.
>
> If you look at the dumpall output you may notice the order in which some
> things are done.

Yeah, as noted in my response to Tom, that is what I'm going to do.

>
> ...
> ...
> create table....
> copy from stdin....
> alter table add constraint....
> create index....
> ...
> ...
>
> My guess is that the error is coming from the add constraint which is
> invoked after the data has been inserted.
>
> Check your table defs in 8.3 and see if the fk constraints are in place.

Will do.

>
> You would have two choices - clean up the data in 7.4 before the export
> or clean up after the import and before the fk constraints are added.

The latter is likely the plan. The primary concern is that we don't
lose any data.

--
Until later, Geoffrey

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Geoffrey <lists@serioustechnology.com> writes:
> Shane Ambler wrote:
>> You would have two choices - clean up the data in 7.4 before the export
>> or clean up after the import and before the fk constraints are added.

> The latter is likely the plan. The primary concern is that we don't
> lose any data.

There seem to be two possibilities: either the data was actually
inconsistent in the 7.4 database (in which case it would be good to
find out how it got that way), or the data is fine but the pg_dump
script tried to do the ALTER ADD CONSTRAINT too soon (in which case
it would be good to find out why pg_dump got confused about that).
In either case I'm hoping you still have the 7.4 installation available
for inspection ...

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin