Thread: special integrity constraints

special integrity constraints

From
Erik Thiele
Date:
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


Re: special integrity constraints

From
Achilleus Mantzios
Date:
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



Re: special integrity constraints

From
Erik Thiele
Date:
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/


Re: special integrity constraints

From
Bruno Wolff III
Date:
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).


Re: special integrity constraints

From
Achilleus Mantzios
Date:
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




Re: special integrity constraints

From
Erik Thiele
Date:
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/


Re: special integrity constraints

From
Stephan Szabo
Date:
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.


Re: special integrity constraints

From
Bruno Wolff III
Date:
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".


Re: special integrity constraints

From
Erik Thiele
Date:
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


Re: special integrity constraints

From
Stephan Szabo
Date:
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.


Re: special integrity constraints

From
Erik Thiele
Date:
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


Re: special integrity constraints

From
Bruno Wolff III
Date:
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.

Re: special integrity constraints

From
Achilleus Mantzios
Date:
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