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

Search Discussions

  • Joseph Tate at Feb 13, 2004 at 10:43 pm

    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
  • Tom Lane at Feb 13, 2004 at 11:28 pm

    Joseph Tate 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
  • Christopher Kings-Lynne at Feb 14, 2004 at 4:16 am

    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
  • Joe Conway at Feb 14, 2004 at 4:53 am

    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
  • Andreas Pflug at Feb 14, 2004 at 10:51 am

    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
  • Joe Conway at Feb 14, 2004 at 3:37 pm

    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
  • Tom Lane at Feb 14, 2004 at 4:10 pm

    Joe Conway 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
  • Joseph Tate at Feb 14, 2004 at 6:39 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 13, '04 at 8:49p
activeFeb 14, '04 at 6:39p
posts9
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase