Re: Foreign keys

On Sun, 10 Sep 2006, Gregory Stark wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > > I think if we were going to do this that all the constraint violations for
> > > unique, not null, check and foreign keys should be handled similarly, so
> > > we'd probably want something more general than just a way for the ri
> > > triggers to do this. I don't have a good idea of the right solution for
> > > that though.
> >
> > It seems pretty unwieldy to me: it's not hard to imagine a long INSERT
> > throwing millions of separate foreign-key errors before it's done, for
> > instance. And then there's the cascading-errors problem, ie, bogus
> > reports that happen because some prior step failed ... not least being
> > your client crashing and failing to tell you anything about what
> > happened because it ran out of memory for the error list.
> >
> > My advice is to rethink the client code that wants such a behavior.
>
> Well you're still talking about the case of multiple queries deferring all
> constraint checks to the end of the transaction.

Well, or insert ... select or update or delete. Most "deferred"
conditions can happen within one statement as well.

> In any case the same logic that leads to it being desirable to report all the
> errors to the user in a UI and not just report them one by one also applies to
> the database. I'm not sure it's the most important issue in the world, but it
> does seem like a "it would be nice" feature if it reported all the errors in
> the statement, not just the first one it finds.

SQL seems to have a notion of setting the size of the diagnostics area for
a transaction to hold a number of conditions. There are a few odd bits,
for example it's mostly unordered, but the sqlcode returned must match to
the first condition and we presumably want to make sure that if there are
any errors that we return an exception sql code not a completion one.