Обсуждение: Warnings after pg_dumpall > restore

Hi List,

Thanks for this great product.

We have a database with many schema's and we are trying to migrate the db from version 8.1.9 to 8.3.8.

So we made a pg_dumpall.

Now, when we try to restore with psql, we get loads of errors and warnings:

PROBLEM 1

psql:db03.ak1.sql:10155: WARNING:  column "plan_status" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "hplan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "stp_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_datum_start" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_datum_eind" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_inspanning" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "real_datum_start" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "real_datum_eind" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "med_uitvoering" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "conflict" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "conflict_text" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "handmatig_actief" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "mijlpaal" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "handmatig_groep" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "mijlpaaldatum" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_pro_noshow_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "doc_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_status" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "hplan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "stp_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_datum_start" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
 

From earlier reports on the mailinglist, I assume it is a view that has no explicit cast.

What are the consequences of these messages for the system?

Can I ignore the warnings and simply correct the views later?

PROBLEM 2

Furthermore the following errors are shown:

psql:db03.ak1.sql:251166: ERROR:  column a.transaction does not exist
LINE 2:     SELECT (a.relation)::regclass AS "table", a."transaction...
                                                      ^
psql:db03.ak1.sql:251169: ERROR:  relation "public.locks_tb_aux" does not exist
psql:db03.ak1.sql:251176: ERROR:  relation "locks_tb_aux" does not exist
psql:db03.ak1.sql:251179: ERROR:  relation "public.locks_tb" does not exist
psql:db03.ak1.sql:251186: ERROR:  column a.transaction does not exist
LINE 2:     SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi...
                   ^
psql:db03.ak1.sql:251189: ERROR:  relation "public.locks_tr_aux" does not exist
psql:db03.ak1.sql:251196: ERROR:  relation "locks_tr_aux" does not exist
psql:db03.ak1.sql:251199: ERROR:  relation "public.locks_tr" does not exist
psql:db03.ak1.sql:251206: ERROR:  column a.transaction does not exist
LINE 2:     SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi...
                   ^
psql:db03.ak1.sql:251209: ERROR:  relation "public.locks_trid_aux" does not exist
psql:db03.ak1.sql:251216: ERROR:  relation "locks_trid_aux" does not exist
psql:db03.ak1.sql:251219: ERROR:  relation "public.locks_trid" does not exist
 

These look to me as system tables and are certainly not defined by us.

Can I ignore these messages?

PROBLEM 3

We get some messages that referential integrity rules (foreign keys) are violated. How can that be? This undermines my confidence in the system!! This would imply that the foreign key at one time did not exist, BUT WHEN IT WAS CREATED IT DID NOT COMPLAIN THAT THE RULES WERE VIOLATED.......

D. Kniep