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

I don't understand this thread well,sorry.
(I've received no pgsql-general mails for a few days).

IIRC,SQL92 recommends to handle this case
as ASSERTIONS rather than as CONSTRAINTS.
The implementation would be quite different from
that of (current) column constraints.
I've been suspicious if it could be implemented
properly and effectively.
For example,are there any discussions about
locking needed to guarantee the consistency ?

Regards.

Hiroshi Inoue

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