Re: [HACKERS] pg_restore problems and suggested resolution
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_restore problems and suggested resolution
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_restore problems and suggested resolution
Joe Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_restore problems and suggested resolution
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_restore problems and suggested resolution
Joseph Tate [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_restore problems and suggested resolution
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_restore problems and suggested resolution
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
