Date: Mon, 22 Mar 2004 14:10:42 +0100
From: Erik Thiele <erik@thiele-hydraulik.de>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: special integrity constraints
Message-Id: <20040322141042.0aeb936d.erik@thiele-hydraulik.de>
In-Reply-To: <20040322112604.GA31222@wolff.to>
References: <20040322102031.6a6f0b77.erik@thiele-hydraulik.de>
	<20040322112604.GA31222@wolff.to>
Organization: Thiele-Hydraulik
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

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,
> >=20
> > i have two tables
> >=20
> > create table a (x integer);
> > create table b (y real);
> >=20
> > 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.
>=20
> 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) =3D=3D 0)

> > so there is a posibility of adding one element to a and one element
> > to b, and again the constraint is met.
> >=20
> > 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.
> >=20
> > so i cannot use a trigger.
> >=20
> > what i need is the execution of a check procedure at commit time. is
> > that somehow possible?
>=20
> 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

--=20
Erik Thiele
Horst Thiele Maschinenbau-Hydraulische Ger=E4te 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/