From: | Philip Warner <pjw(at)rhyme(dot)com(dot)au> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | general-help postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Checking number of entries |
Date: | 2000-09-29 07:18:34 |
Message-ID: | 3.0.5.32.20000929171834.020fe100@mail.rhyme.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
At 01:53 29/09/00 -0400, Tom Lane wrote:
>
>The canonical example is a check like
> CHECK (mycol > (SELECT max(othertab.othercol) FROM othertab))
>declared as a constraint on mytab.
I would argue that a CHECK constraint only applies to the table on which it
is defined - basically a contract to ensure that certain conditions apply
to changes on that table.
Once you allow functions in constraints, you give up all hope of
cross-compatibility, eg.
Create Table tab1 (f1 integer);
Create Function tot_of_tab1() returns integer
as 'select cast(sum(f1) as int4) from tab1' language 'sql';
Alter Table tab1 add check(tot_of_tab1() > 0);
zzz=# insert into tab1 values(1);
INSERT 142380 1
zzz=# insert into tab1 values(-10);
INSERT 142381 1
zzz=# select tot_of_tab1();
tot_of_tab1
-------------
-9
(1 row)
zzz=# insert into tab1 values(-12);
ERROR: ExecAppend: rejected due to CHECK constraint $1
This demonstrates the problem, and, I think, highlights a bug in the
constraint checking code: the constraint should have failed on the second
insert. Maybe the constraint is evaluate before the insert?
>Should this mean that the constraint on mytab can prevent insertions
>into othertab?
I'd say not. Consider:
weird_function() returns boolean as 'select current_time > ''16:00:00''';
This *could* be used to prevent updates occurring before 4pm - it says
nothing about the validity of the data, it is just a rule about how updates
can be performed.
Does DB theory say what sort of contract CHECK constraints imply?
>You can devise related paradoxes within a single table if constraints
>involving aggregates are permitted. The basic problem is that it's
>not clear which data values are the constrainers and which are the
>constrainees.
What's wrong with CHECK constraints only affecting the table on which they
are defined? It seems better than (a) allowing back-door functions, and (b)
preventing more sensible subselects.
Sorry if this ground has already been covered...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew.Mason | 2000-09-29 08:32:28 | WinNT installation problems - help please |
Previous Message | Tatsuo Ishii | 2000-09-29 07:17:16 | Re: "Unrecognized variable client_encoding" |