Thread: pg_restore problems and suggested resolution
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
Joseph Tate wrote: > 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? > So now that I've looked at the code, I think that this solution is a little too simplistic unfortunately. Now I'm leaning towards --diable-rules. Am I correct in thinking that if I change pg_class.relhasrules to 'f' that the rules will not be processed? Or is there more involved here? Joseph
Joseph Tate <jtate@dragonstrider.com> writes:
> So now that I've looked at the code, I think that this solution is a
> little too simplistic unfortunately. Now I'm leaning towards
> --diable-rules. Am I correct in thinking that if I change
> pg_class.relhasrules to 'f' that the rules will not be processed?
This is a dead end. The --disable-triggers hack is already a time bomb
waiting to happen, because all dump scripts using it will break if we
ever change the catalog representations it is hacking. Disabling rules
by such methods is no better an idea; it'd double our exposure to
compatibility problems. If we're going to do something about this then
it needs to be cleaner.
As an implementation issue, I wonder why these things are hacking
permanent on-disk data structures anyway, when what is wanted is only a
temporary suspension of triggers/rules within a single backend. Some
kind of superuser-only SET variable might be a better idea. It'd not be
hard to implement, and it'd be much safer to use since failures wouldn't
leave you with bogus catalog contents.
regards, tom lane
> As an implementation issue, I wonder why these things are hacking > permanent on-disk data structures anyway, when what is wanted is only a > temporary suspension of triggers/rules within a single backend. Some > kind of superuser-only SET variable might be a better idea. It'd not be > hard to implement, and it'd be much safer to use since failures wouldn't > leave you with bogus catalog contents. I believe oracle and mssql have ALTER TABLE/DISABLE TRIGGER style statements... Chris
Christopher Kings-Lynne wrote: >> As an implementation issue, I wonder why these things are hacking >> permanent on-disk data structures anyway, when what is wanted is only a >> temporary suspension of triggers/rules within a single backend. Some >> kind of superuser-only SET variable might be a better idea. It'd not be >> hard to implement, and it'd be much safer to use since failures wouldn't >> leave you with bogus catalog contents. > > I believe oracle and mssql have ALTER TABLE/DISABLE TRIGGER style > statements... Oracle does for sure, but I can tell you that I have seen people bitten by triggers inadvertantly left disabled before...I think Tom has a good point. Joe
Joe Conway wrote: > Christopher Kings-Lynne wrote: > >>> As an implementation issue, I wonder why these things are hacking >>> permanent on-disk data structures anyway, when what is wanted is only a >>> temporary suspension of triggers/rules within a single backend. Some >>> kind of superuser-only SET variable might be a better idea. It'd >>> not be >>> hard to implement, and it'd be much safer to use since failures >>> wouldn't >>> leave you with bogus catalog contents. >> >> >> I believe oracle and mssql have ALTER TABLE/DISABLE TRIGGER style >> statements... > > > Oracle does for sure, but I can tell you that I have seen people > bitten by triggers inadvertantly left disabled before...I think Tom > has a good point. Might be, but disabled triggers are not only useful when restoring a database. We need this, and supporting this without hacking would be helpful. Regards, Andreas
Andreas Pflug wrote: > Joe Conway wrote: >> Christopher Kings-Lynne wrote: >>>> As an implementation issue, I wonder why these things are >>>> hacking permanent on-disk data structures anyway, when what is >>>> wanted is only a temporary suspension of triggers/rules within >>>> a single backend. Some kind of superuser-only SET variable >>>> might be a better idea. It'd not be hard to implement, and >>>> it'd be much safer to use since failures wouldn't leave you >>>> with bogus catalog contents. >>> >>> I believe oracle and mssql have ALTER TABLE/DISABLE TRIGGER style >>> statements... >> >> Oracle does for sure, but I can tell you that I have seen people >> bitten by triggers inadvertantly left disabled before...I think Tom >> has a good point. > > Might be, but disabled triggers are not only useful when restoring a > database. We need this, and supporting this without hacking would be > helpful. I didn't dispute the fact that disabling triggers (without unsupported hacks) is useful. I did agree with Tom that doing so with "permanent" commands is dangerous. I think the superuser-only SET variable idea is the best one I've heard for a way to support this. Joe
Joe Conway <mail@joeconway.com> writes:
> I didn't dispute the fact that disabling triggers (without unsupported
> hacks) is useful. I did agree with Tom that doing so with "permanent"
> commands is dangerous. I think the superuser-only SET variable idea is
> the best one I've heard for a way to support this.
I guess the questions we should ask are:
(1) Is there an argument for having a mechanism that would defeat
triggers/rules in all backends and not just the invoking one?
I find it hard to envision a good case for this --- in general
you'd not know what other backends are doing, and so it seems really
risky to use such a mechanism. Certainly pg_dump doesn't need it.
(2) Is there a need to defeat triggers/rules on just one table?
A SET variable would likely affect all tables. pg_dump wouldn't
care, but what other use-cases are there?
We should also think about what "defeating rules" means exactly.
Defeating ON SELECT rules would render views broken, without offering
any usefulness that I can think of; and for that matter, defeating other
types of rules on a view would result in undesirable behavior (e.g., the
system would then try to insert into the view itself). So I'm inclined
to think that the switch should only disable rules that are attached
to regular tables. Are there any other special cases to be considered?
regards, tom lane
Tom Lane wrote: > This is a dead end. The --disable-triggers hack is already a time bomb > waiting to happen, because all dump scripts using it will break if we > ever change the catalog representations it is hacking. Disabling rules > by such methods is no better an idea; it'd double our exposure to > compatibility problems. If we're going to do something about this then > it needs to be cleaner. > > As an implementation issue, I wonder why these things are hacking > permanent on-disk data structures anyway, when what is wanted is only a > temporary suspension of triggers/rules within a single backend. Some > kind of superuser-only SET variable might be a better idea. It'd not be > hard to implement, and it'd be much safer to use since failures wouldn't > leave you with bogus catalog contents. > > regards, tom lane I like that idea. I didn't at first, but then I saw the super-user only bit. Where would I start to implement this? Do we want two separate properties for rules and triggers, or one to rule them all? Joseph