Обсуждение: special integrity constraints
hi, i have two tables create table a (x integer); create table b (y real); i have the special constraint that the sum of the number of rows in table a plus the number of rows in table b must be even. so there is a posibility of adding one element to a and one element to b, and again the constraint is met. but this of course does not work, since between adding the element to a and adding the element to b, the constraint is not met. so i cannot use a trigger. what i need is the execution of a check procedure at commit time. is that somehow possible? my database has much more tables than just a and b, and most time the other tables are modified and not a or b, so it would be nice to execute the constraint checking procedure only if a or b was modified. yes :) this question i asked in my former mail too, but I think it looked like there was a workaround and my problem was wrong. so i created this new kind of problem here to make things clearer. cu Erik -- Erik Thiele
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : Did you check out the DEFERRABLE option on the constraint? > hi, > > i have two tables > > create table a (x integer); > create table b (y real); > > i have the special constraint that the sum of the number of rows in > table a plus the number of rows in table b must be even. > > so there is a posibility of adding one element to a and one element to > b, and again the constraint is met. > > but this of course does not work, since between adding the element to a > and adding the element to b, the constraint is not met. > > so i cannot use a trigger. > > what i need is the execution of a check procedure at commit time. is > that somehow possible? > > my database has much more tables than just a and b, and most time the other > tables are modified and not a or b, so it would be nice to execute the > constraint checking procedure only if a or b was modified. > > yes :) this question i asked in my former mail too, but I think it > looked like there was a workaround and my problem was wrong. so i > created this new kind of problem here to make things clearer. > > > cu > Erik > > > -- -Achilleus
On Mon, 22 Mar 2004 12:13:29 +0200 (EET) Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote: > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > Did you check out the DEFERRABLE option on the constraint? > DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. !!!! Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. !!!! my constraint is not a foreign key constraint.... cya! erik > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > > > my database has much more tables than just a and b, and most time > > the other tables are modified and not a or b, so it would be nice to > > execute the constraint checking procedure only if a or b was > > modified. > > > > yes :) this question i asked in my former mail too, but I think it > > looked like there was a workaround and my problem was wrong. so i > > created this new kind of problem here to make things clearer. > > > > > > cu > > Erik > > > > > > > > -- > -Achilleus > -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: erik@thiele-hydraulik.de Internet: http://www.thiele-hydraulik.de/
On Mon, Mar 22, 2004 at 10:20:31 +0100, Erik Thiele <erik@thiele-hydraulik.de> wrote: > hi, > > i have two tables > > create table a (x integer); > create table b (y real); > > i have the special constraint that the sum of the number of rows in > table a plus the number of rows in table b must be even. Does the above mean that the number of rows in a is equal to the number of rows in b or that their sum is divisible by 2? > so there is a posibility of adding one element to a and one element to > b, and again the constraint is met. > > but this of course does not work, since between adding the element to a > and adding the element to b, the constraint is not met. > > so i cannot use a trigger. > > what i need is the execution of a check procedure at commit time. is > that somehow possible? Do the real tables have candidate keys? If so you can use foreign key references to pair a row in a to a row b (though I am not sure this is what your real constraint is).
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote: > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > Did you check out the DEFERRABLE option on the constraint? > > > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!!! > Ooops . Then i guess you have to convert your problem to use FK constraints. Add 2 additional 1 row tables "abnum" and "even" and write 2 triggers (on a,b) that keep up to date the value of the single row of abnum. (thatis num(a)+num(b) % 2). Then INSERT INTO even(num) VALUES(0); Then make 1<-->1 relationship between abnum,even using DEFERRABLE FK constraints. At the end of each xaction the num(a)+num(b) % 2 must equal to 0 (i.e. an even number). Whats your results? > my constraint is not a foreign key constraint.... > > cya! > erik > > > > hi, > > > > > > i have two tables > > > > > > create table a (x integer); > > > create table b (y real); > > > > > > i have the special constraint that the sum of the number of rows in > > > table a plus the number of rows in table b must be even. > > > > > > so there is a posibility of adding one element to a and one element > > > to b, and again the constraint is met. > > > > > > but this of course does not work, since between adding the element > > > to a and adding the element to b, the constraint is not met. > > > > > > so i cannot use a trigger. > > > > > > what i need is the execution of a check procedure at commit time. is > > > that somehow possible? > > > > > > my database has much more tables than just a and b, and most time > > > the other tables are modified and not a or b, so it would be nice to > > > execute the constraint checking procedure only if a or b was > > > modified. > > > > > > yes :) this question i asked in my former mail too, but I think it > > > looked like there was a workaround and my problem was wrong. so i > > > created this new kind of problem here to make things clearer. > > > > > > > > > cu > > > Erik > > > > > > > > > > > > > -- > > -Achilleus > > > > > -- -Achilleus
On Mon, 22 Mar 2004 05:26:04 -0600 Bruno Wolff III <bruno@wolff.to> wrote: > On Mon, Mar 22, 2004 at 10:20:31 +0100, > Erik Thiele <erik@thiele-hydraulik.de> wrote: > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > Does the above mean that the number of rows in a is equal to the > number of rows in b or that their sum is divisible by 2? it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > Do the real tables have candidate keys? If so you can use foreign key > references to pair a row in a to a row b (though I am not sure this > is what your real constraint is). no. it's not what i want. the example presented here is just for making it clear why i want to launch a postgresql function on commit time. i am looking for this command: IF TABLE A OR B MODIFIED DO CALL my_checking_function() BEFORE COMMIT; and of course this must be enforced on DB layer. user apps may not work around it. as a dirty hack the following would also work for a first: ON COMMIT CALL my_checking_function(); altough it does lots of unneccessary work if the tables were not accessed. the my_checking_function() throws an error and thus aborts the transaction if the consistency is not ok. (num(a)+num(b) odd) my_checking_function is so complicated that it is not workaroundable with other tools. i wanted to create this situation by introducing the funny constraint that the sum of the number of rows in the two tables is even. of course in my real db the constraint is a more useful one... cu erik -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: erik@thiele-hydraulik.de Internet: http://www.thiele-hydraulik.de/
On Mon, 22 Mar 2004, Erik Thiele wrote: > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote: > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > Did you check out the DEFERRABLE option on the constraint? > > > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!!! > > my constraint is not a foreign key constraint.... However, foreign keys are implemented using "constraint triggers". They're really not documented much (because they're a not really separately supported implementation detail), but you can theoretically make triggers that are deferred to immediately before commit time.
On Mon, Mar 22, 2004 at 14:10:42 +0100, Erik Thiele <erik@thiele-hydraulik.de> wrote: > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) OK, that means my FK suggestion won't help. The other suggestion about putting triggers on "a" and "b" to update a count in another table that has a deferred check constraint on it may be your best bet. This will be a source of contention, but that may or may not be all that important depending on how often you are updating "a" and "b".
On Mon, 22 Mar 2004 06:21:28 -0800 (PST) Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote: > > > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > > > Did you check out the DEFERRABLE option on the constraint? > > > > > > > DEFERRABLE > > NOT DEFERRABLE > > > > This controls whether the constraint can be deferred. A constraint that > > is not deferrable will be checked immediately after every command. > > Checking of constraints that are deferrable may be postponed until the > > end of the transaction (using the SET CONSTRAINTS command). NOT > > DEFERRABLE is the default. > > > > !!!! Only foreign key constraints currently accept > > this clause. All other constraint types are not deferrable. !!!! > > > > my constraint is not a foreign key constraint.... > > However, foreign keys are implemented using "constraint triggers". They're > really not documented much (because they're a not really separately > supported implementation detail), but you can theoretically make > triggers that are deferred to immediately before commit time. could you paste some code? create table a (x integer); create or replace function alwaysfail() returns opaque as ' begin raise exception ''no no i dont want to''; end; ' language 'plpgsql'; MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON MODIFICATION OF TABLE a AT COMMIT TIME; begin transaction; insert into a values(4); (ok) insert into a values(10); (ok) commit; (no no i dont want to) is that possible? cu erik -- Erik Thiele
On Mon, 22 Mar 2004, Erik Thiele wrote: > On Mon, 22 Mar 2004 06:21:28 -0800 (PST) > Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > > > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > > However, foreign keys are implemented using "constraint triggers". They're > > really not documented much (because they're a not really separately > > supported implementation detail), but you can theoretically make > > triggers that are deferred to immediately before commit time. > > could you paste some code? > > > create table a (x integer); > > create or replace function alwaysfail() returns opaque as ' > begin > raise exception ''no no i dont want to''; > end; > ' language 'plpgsql'; > > MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON > MODIFICATION OF TABLE a AT COMMIT TIME; create constraint trigger a_foo1 after insert or update or delete on a initially deferred for each row execute procedure alwaysfail(); Like I said, it's an implementation detail, so it's not 100% guaranteed to exist forever, but it's almost certain to last until we have deferrable non-fk constraints. It does also obey set constraints so, set constraints a_foo1 immediate does the checks immediately just as if it were a deferred constraint.
On Mon, 22 Mar 2004 09:12:11 -0600 Bruno Wolff III <bruno@wolff.to> wrote: > On Mon, Mar 22, 2004 at 14:10:42 +0100, > Erik Thiele <erik@thiele-hydraulik.de> wrote: > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > OK, that means my FK suggestion won't help. The other suggestion about > putting triggers on "a" and "b" to update a count in another table > that has a deferred check constraint on it may be your best bet. > This will be a source of contention, but that may or may not be all > that important depending on how often you are updating "a" and "b". > there is no such thing as a deferred check constraint: DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. !!! Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. !!! cu erik -- Erik Thiele
On Mon, Mar 22, 2004 at 17:21:21 +0100, Erik Thiele <erik@thiele-hydraulik.de> wrote: > > there is no such thing as a deferred check constraint: > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!! Sorry, I checked the syntax for CREATE TABLE in the documentation and it shows DEFERRABLE as allowed on any column constraint. However, that is apparently not correct.
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : Could it be possible to convert *any* CHECK constraint problem into an equivalent DEFERRABLE FK constraint problem? That is for *every* CHECK CONSTRAINT cc create (and maintain) 2 additional 1 row tables (cc1(val),cc2(val)) in such a way that cc is met if and only if (cc1.val = cc2.val). Its kinda awkard, and i think that if your situation is (gets) any more complicated tou should delegate that task to the application. The whole idea was for you too prove that using only supported pgsql features, you can achieve the equivalent effect of deferrable check constraints. > On Mon, 22 Mar 2004 09:12:11 -0600 > Bruno Wolff III <bruno@wolff.to> wrote: > > > On Mon, Mar 22, 2004 at 14:10:42 +0100, > > Erik Thiele <erik@thiele-hydraulik.de> wrote: > > > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > > > OK, that means my FK suggestion won't help. The other suggestion about > > putting triggers on "a" and "b" to update a count in another table > > that has a deferred check constraint on it may be your best bet. > > This will be a source of contention, but that may or may not be all > > that important depending on how often you are updating "a" and "b". > > > > there is no such thing as a deferred check constraint: > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!! > > > cu > erik > > -- -Achilleus