Re: Checking number of entries

On Wed, 4 Oct 2000, Philip Warner wrote:

> >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.
>
> I'd have thought sending it to something that lets the optimizer deal with
> it; manual row by row would be a disaster, since in 99% of cases is a well
> designed application, no rows would match (ie. no failures).
>
> I know people (Tom?) have complained about SPI in the backend before, I
> think, but it seems like the way to go - unless there is a lower level
> query representation that can be generated when the constraint is defined
> then passed to the optimiser at runtime...

Yeah, if there is a good way to do that, I should probably change the
check constraint stuff for alter table so it does the not (<expr>)
rather than a scan and evaluate.

> >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.
>
> If someone defines a constraint based on random or varying data (eg.
> CURRENT_TIMESTAMP), then they either (a) know what they are doing, and
> would not expect reverse validation, or (b) haven't got a clue what they
> are doing and probably don't expect reverse validation. How does that
> sound? ;-}

Heh. Probably a good assumption at least for a first pass. And it would
at least get us closer to complience. I guess CURRENT_TIMESTAMP,
CURRENT_USER (or whatever its real name is) and functions that might
provide inconsistant results or access other tables are danger signs.