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

At 11:09 1/10/00 -0700, Stephan Szabo wrote:
>On Sun, 1 Oct 2000, Philip Warner wrote:
>>
>> 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...)

This sounds great! As you know, there's a whole lot of places that will
profit from this.


>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...


>>
>> 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.

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? ;-}



----------------------------------------------------------------
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   |/