Re: [HACKERS] Disable and enable of table and column constraints
>Martijn van Oosterhout wrote: > FWIW, I find the ability in Slony to configure triggers so they work > or not depending on the replication role to be extremely useful. > Absolutely a major positive feature. Yeah, as a general rule it doesn't make sense to try to enforce constraints on a replication *target*. Check and report, perhaps, but you don't normally want to error out on anything which you know was actually applied to the source database. It's even worse for some classes of triggers which generate derived data; you don't want the replication to generate one value and then a trigger on the replication target to try to do the same. A count, for example, could easily wind up with an "off by one" error much of the time. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
On Thu, Sep 10, 2009 at 04:24:15PM -0400, Jan Wieck wrote: > The feature was originally intended to be a clean way of avoiding > interferences of triggers and/or foreign keys with replication systems > that work on the user level (like Bucardo, Londiste and Slony). The only > way to break foreign keys in that scenario is to replicate a referencing > table without replicating the corresponding PK table. FWIW, I find the ability in Slony to configure triggers so they work or not depending on the replication role to be extremely useful. Absolutely a major positive feature. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Disable and enable of table and column constraints
On 9/10/2009 11:06 AM, Tom Lane wrote: Christopher Browne writes: With the ALTER TABLE DISABLE TRIGGER functionality added in 8.3, we already have the ability to do this with foreign key constraints. That "feature" is a crock that should not be extended, because it leaves it entirely on the user's shoulders whether the constraint is actually true when the system thinks it is. What is being discussed here is ways to incrementally add real, proven-valid constraints. (Indeed, given the thought that's being given to having the planner assume that FK constraints hold, I rather think that we need to reconsider ALTER DISABLE TRIGGER.) The feature was originally intended to be a clean way of avoiding interferences of triggers and/or foreign keys with replication systems that work on the user level (like Bucardo, Londiste and Slony). The only way to break foreign keys in that scenario is to replicate a referencing table without replicating the corresponding PK table. Note that Slony-I currently does apply updates in a fashion that would actually make checking of foreign keys on the replica possible, but does need the ability to disable regular user triggers. But for some future version of Slony, we may need to change that and apply changes within one replication group (SYNC) out of order with respect to multiple tables. Which means that Slony would need at least some mechanism to disable user triggers and force all foreign key constraints to be deferred, whether they are declared deferrable or not. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
Christopher Browne writes: > With the ALTER TABLE DISABLE TRIGGER functionality added in 8.3, we > already have the ability to do this with foreign key constraints. That "feature" is a crock that should not be extended, because it leaves it entirely on the user's shoulders whether the constraint is actually true when the system thinks it is. What is being discussed here is ways to incrementally add real, proven-valid constraints. (Indeed, given the thought that's being given to having the planner assume that FK constraints hold, I rather think that we need to reconsider ALTER DISABLE TRIGGER.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
pete...@gmx.net (Peter Eisentraut) writes: > On Tue, 2009-09-08 at 16:07 -0400, Alvaro Herrera wrote: >> Tom Lane wrote: >> > Michael Gould writes: >> > > It would be nice if we could enable and disable column and table >> > > constraints. I believe that you can do this in Oracle but this is very >> > > handy for testing stored procedures and other external processes. >> > >> > Drop the constraint and re-add it later... >> >> That's not very useful when adding it later means grabbing an exclusive >> lock on the table for the whole duration of the full table scan required >> to check the table. > > It's also useful to define foreign keys for documentation purposes but > not enforce them for some reason. With the ALTER TABLE DISABLE TRIGGER functionality added in 8.3, we already have the ability to do this with foreign key constraints. That suggests a place for syntax to come from, I'd expect. -- let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;; Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
Alvaro Herrera wrote: > Both DB2 and Oracle have an ENFORCE setting for constraints, and a > MySQL blog hinted some time ago that it might be in SQL 201x. If I remember correctly, Sybase never checks the existing data when you add a constraint of any type (except for a unique constraint or primary key). That has occasionally been useful to me when a business rule has been identified which we want to enforce in an existing database, but there hasn't yet been enforcement of that rule. You can "plug the leak" first, then list the legacy problems and get those on a list for cleanup. If you insist that all preexisting bad data must be cleaned up before you can prevent more bad data from going in, you might never *get* clean because of a steady dribble of additional bad data while you are attempting cleanup. (Well, OK, you could always enforce the rule at some other layer and hope to get enough traction to correct the problems, but it is nice to have help from the DBMS in this regard, without having to code triggers to get there.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
On Tue, Sep 8, 2009 at 1:07 PM, Alvaro Herrera wrote: > Tom Lane wrote: >> Michael Gould writes: >> > It would be nice if we could enable and disable column and table >> > constraints. I believe that you can do this in Oracle but this is very >> > handy for testing stored procedures and other external processes. >> >> Drop the constraint and re-add it later... > > That's not very useful when adding it later means grabbing an exclusive > lock on the table for the whole duration of the full table scan required > to check the table. > > Actually something in this area is on my plate too -- a customer of ours > wants to be able to define constraints but not have it checked > immediately. I envision it similar to how concurrent index creation > works: the constraint is created as "not checked" and the transaction is > committed; new insertions are checked against the constraint. Then the > table is scanned to verify that extant tuples pass the constraint, > _without_ the exclusive lock on the table. > > Both DB2 and Oracle have an ENFORCE setting for constraints, and a MySQL > blog hinted some time ago that it might be in SQL 201x. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. The mysql'ism foreign_key_checks would seem to do similar things...? (http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_foreign_key_checks ) -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
On Tue, 2009-09-08 at 16:07 -0400, Alvaro Herrera wrote: > Tom Lane wrote: > > Michael Gould writes: > > > It would be nice if we could enable and disable column and table > > > constraints. I believe that you can do this in Oracle but this is very > > > handy for testing stored procedures and other external processes. > > > > Drop the constraint and re-add it later... > > That's not very useful when adding it later means grabbing an exclusive > lock on the table for the whole duration of the full table scan required > to check the table. It's also useful to define foreign keys for documentation purposes but not enforce them for some reason. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
Tom Lane wrote: > Michael Gould writes: > > It would be nice if we could enable and disable column and table > > constraints. I believe that you can do this in Oracle but this is very > > handy for testing stored procedures and other external processes. > > Drop the constraint and re-add it later... That's not very useful when adding it later means grabbing an exclusive lock on the table for the whole duration of the full table scan required to check the table. Actually something in this area is on my plate too -- a customer of ours wants to be able to define constraints but not have it checked immediately. I envision it similar to how concurrent index creation works: the constraint is created as "not checked" and the transaction is committed; new insertions are checked against the constraint. Then the table is scanned to verify that extant tuples pass the constraint, _without_ the exclusive lock on the table. Both DB2 and Oracle have an ENFORCE setting for constraints, and a MySQL blog hinted some time ago that it might be in SQL 201x. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disable and enable of table and column constraints
Michael Gould writes: > It would be nice if we could enable and disable column and table > constraints. I believe that you can do this in Oracle but this is very > handy for testing stored procedures and other external processes. Drop the constraint and re-add it later... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Disable and enable of table and column constraints
It would be nice if we could enable and disable column and table constraints. I believe that you can do this in Oracle but this is very handy for testing stored procedures and other external processes. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
