Обсуждение: 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