I've got a custom (-Fc) pg_dump output from a fairly complex 7.2.x db
schema. It has such things as user defined functions, OIDs, rules and
triggers, etc. When I try to restore it to a 7.4 database, it fails
because of some differences in the CREATE TABLE commands (I've got a
column of type TIMESTAMP WITH TIMEZONE and the DEFAULT's default type is
TEXT).
Also, when the data is restored, and the OIDs are "fixed", the rules and
triggers aren't disabled on the columns/tables that are UPDATEd during
the restore process, so those rules and triggers fire. Since these
rules and triggers are designed to be executed within a transaction that
includes a call to a setup type function which creates a temporary
table, the rules fail.
I've filed a bug on the OID problem before, but have so far been able to
work around the problem. However, when tied to the CREATE TABLE error
above, I can't get this data restored. Usually what I do is grep -v the
rules and triggers from the DB schema restore, restore the data, then
restore the rules and triggers. However now I can't restore the schema
at all. I could rebuild the db with my ddl without the rules and
triggers, and then restore the data, but I'd rather fix this at the source.
I propose pg_restore --disable-triggers be modified so that triggers are
disabled on the tables that OID fixing is going to UPDATE. I'll
hopefully have a patch against REL7_4_STABLE for this soon, but I
haven't started it yet. Does anyone have any suggestions? Has someone
already done this in HEAD so that it can be backported to 7.4?
Joseph