Subject: special integrity constraints

From: Erik Thiele <[email-masked]>
Date: Mon, 22 Mar 2004 10:20:31 +0100

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

---

From: Achilleus Mantzios <[email-masked]>
Date: Mon, 22 Mar 2004 12:13:29 +0200 (EET)

O kyrios Erik Thiele egrapse stis Mar 22, 2004 :

Did you check out the DEFERRABLE option on the constraint?

-- 
-Achilleus

---

From: Erik Thiele <[email-masked]>
Date: Mon, 22 Mar 2004 11:26:59 +0100

On Mon, 22 Mar 2004 12:13:29 +0200 (EET)

> 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

> > 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.
> > 
> > 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.
> > 

-- 
Erik Thiele
Horst Thiele Maschinenbau-Hydraulische Geräte GmbH
Im Kampfrad 2 - 74196 Neuenstadt
Tel.: 07139/4801-19
Fax.: 07139/4801-29
email: [email-masked]
Internet: http://www.thiele-hydraulik.de/

---

From: Bruno Wolff III <[email-masked]>
Date: Mon, 22 Mar 2004 05:26:04 -0600

On Mon, Mar 22, 2004 at 10:20:31 +0100,
> 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?

> what i need is the execution of a check procedure at commit time. is

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).

---

From: Achilleus Mantzios <[email-masked]>
Date: Mon, 22 Mar 2004 13:40:11 +0200 (EET)

> 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?

-- 
-Achilleus

---

From: Erik Thiele <[email-masked]>
Date: Mon, 22 Mar 2004 14:10:42 +0100

On Mon, 22 Mar 2004 05:26:04 -0600
Bruno Wolff III <[email-masked]> wrote:

> 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)

> 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

---

From: Stephan Szabo <[email-masked]>
Date: Mon, 22 Mar 2004 06:21:28 -0800 (PST)

> 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.

---

From: Bruno Wolff III <[email-masked]>
Date: Mon, 22 Mar 2004 09:12:11 -0600

On Mon, Mar 22, 2004 at 14:10:42 +0100,
> 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".

---

From: Erik Thiele <[email-masked]>
Date: Mon, 22 Mar 2004 17:21:21 +0100

On Mon, 22 Mar 2004 09:12:11 -0600

> 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

!!! Only foreign key constraints currently accept
this clause. All other constraint types are not deferrable. !!!

cu
erik

-- 
Erik Thiele

---

From: Erik Thiele <[email-masked]>
Date: Mon, 22 Mar 2004 17:25:44 +0100

On Mon, 22 Mar 2004 06:21:28 -0800 (PST)

> triggers that are deferred to immediately before commit time.

could you paste some code?

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

---

From: Stephan Szabo <[email-masked]>
Date: Mon, 22 Mar 2004 08:34:54 -0800 (PST)

> 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.

---

From: Bruno Wolff III <[email-masked]>
Date: Mon, 22 Mar 2004 18:34:56 -0600

On Mon, Mar 22, 2004 at 17:21:21 +0100,
> 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.

---

From: Achilleus Mantzios <[email-masked]>
Date: Tue, 23 Mar 2004 09:26:29 +0200 (EET)

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.

-- 
-Achilleus