Re: Foreign keys

Chris Mair <chrisnospam(at)1006(dot)org> writes:

> What's the purpose of letting you insert 1000 records, then, at the end
> say: "hah, all is rolled back becauase the 2nd record was invalid".
> PG justly throws the exception immediately to let you know it's futile
> inserting 998 more records.

Well there's plenty of cases where people want that and we support it with
deferred constraints.

However the OP sounds like he wants something else. I think what he wants is
when he inserts a record and it fails due to foreign key constraints to report
all the violated constraints, not just the first one found.

I never run into this problem myself because I think of foreign key
constraints as more akin to C assertions. They're a backstop to make sure the
application is working correctly. I never write code that expects foreign key
constraint errors and tries to handle them.

But there's nothing saying that's the only approach. The feature request seems
pretty reasonable to me. I'm not sure how hard it would be with the ri
triggers as written. I'm not sure there's anywhere for triggers to store their
"return values" so I'm unclear this can even be done using triggers.

But to answer his original question: yes that's the way Postgres works and if
you want to report all the violations together you'll have to check them
yourself.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com