Обсуждение: Constraint ordering
I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first”is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory. When I was doing my early testing, the parent+basename constraint would fire first if it needed to. Now that I’m doing alonger test run, the second constraint is firing at a time that I wasn’t expecting. I’m debugging but it takes time tohit this again. My assumption was if the error reported back that the “second" constraint failed that the “first” constraint passed. ButI bet that isn’t a valid assumption at all. All that to ask: is there a predictable ordering of constraints? Thank you, Perry
Вложения
On Sat, Apr 9, 2022 at 7:43 AM Perry Smith <pedz@easesoftware.com> wrote:
I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first” is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory.
When I was doing my early testing, the parent+basename constraint would fire first if it needed to. Now that I’m doing a longer test run, the second constraint is firing at a time that I wasn’t expecting. I’m debugging but it takes time to hit this again.
My assumption was if the error reported back that the “second" constraint failed that the “first” constraint passed. But I bet that isn’t a valid assumption at all.
All that to ask: is there a predictable ordering of constraints?
If you cannot find documentation describing constraint ordering rules then there are none. It isn't something a production application should rely upon. And for testing you should just ensure that any given test case only violates one of those constraints at a time when you are testing to see that they fire.
I'm not aware of any documentation describing constraint evaluation order.
David J.
On 4/9/22 09:42, Perry Smith wrote: > I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first”is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory. > > When I was doing my early testing, the parent+basename constraint would fire first if it needed to. Now that I’m doinga longer test run, the second constraint is firing at a time that I wasn’t expecting. I’m debugging but it takes timeto hit this again. > > My assumption was if the error reported back that the “second" constraint failed that the “first” constraint passed. ButI bet that isn’t a valid assumption at all. > > All that to ask: is there a predictable ordering of constraints? If this would be a problem, then make the constraints deferred (until commit). -- Angular momentum makes the world go 'round.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Apr 9, 2022 at 7:43 AM Perry Smith <pedz@easesoftware.com> wrote:
>> All that to ask: is there a predictable ordering of constraints?
> I'm not aware of any documentation describing constraint evaluation order.
IIRC, triggers are specified to fire in name order. Uniqueness
constraints are not enforced by triggers though; I wouldn't care
to rely on the order in which those are checked.
regards, tom lane