pg_restore problems and suggested resolution

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