Re: Checking number of entries - Mailing list pgsql-general

On Sun, 1 Oct 2000, Philip Warner wrote:

> At 11:25 30/09/00 -0700, Stephan Szabo wrote:
> >
> >On Sat, 30 Sep 2000, Philip Warner wrote:
> >
> >>
> >> alter table zzz add constraint
> >>      check( (select sum(zzzz.f1*zzz1.f1_1) from zzzz,zzz1
> >>                 where zzzz.f2=zzz1.f1_1) + f1 > 0);
> >>
> >> Any updates to the referenced tables cause it to run a query that *seems*
> >> to be:
> >>
> >>      select * from ZZZ where not ( <constraint-condition> )
> >>
> >
> >Well, I'm not sure how easy/hard it is to get all referenced tables right
> >now, since you'd have to decend into subqueries.   But, yeah, that seems
> >like it would probably work since you probably couldn't currently put
> >anything in the check constraint that would work there but not in a
> >subquery context (although i could be wrong about that).
>
> Yes; we'd need to generate a plan for the constraint, and find all the
> tables it references. Is that a hard thing to do?

Probably not, although I've been wrong about that before... :(
Well, if I do end up doing the stuff for holding what objects reference
what other objects, I'm going to have to do this anyway since the
constraint references all of those tables and should either be removed
or restrict the removal of those tables (I think there are wierd special
cases involved, but in general...)

The other part could probably be done by creating after
insert/update/delete triggers on those tables with the oid of
the constraint row as data.  I'm not sure of the best way to do
the actual check... it'd be easy to do in spi, but that has its
own problems. Doing a manual scan looking for rows that fail is also
easy but rather slow if there are alot rows where very few fail.

> >Can you do arbitrary user functions in Dec RDB that access tables and put
> >those in constraints? I'm not sure what to do about the fact that we can't
> >actually get the referenced tables inside functions for some cases.
>
> RDB has two kinds of functions: external & SQL. External functions can't
> make data changes, or  even easily read the database, and SQL functions are
> just pieces of (complex multi-line) SQL, that can be parsed like anything
> else. As a result, when you call a function in a constraint, it plans the
> function, and gets the list of tables.

Unfortunately, we can have cases where the plan depends on other data
outside of stuff that's known at creation time, like data in random
tables.  I'm really not sure how to handle those cases except either
disallowing them or handling them incorrectly.