Re: constraints
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: gints.plivna@xxxxxxxxx
- Date: Thu, 14 Aug 2014 14:38:09 +0200
Another thing to be aware of, is that deferrable constraints are less
useful to the optimizer. If a constraint is deferrable, the optimizer
cannot do optimizations that depend on the constraints being valid, as the
constraints might not be valid until commit.
Of course there are valid usecases for deferrable constraints, but start by
asking the developer if his logic *really* needs to violate the constraints
during the transaction or if he can do the work with a different logic that
would allow the constraints to stay. I think in many cases the developer
can rewrite the logic so deferring constraints are not needed. But if that
is not possible, then yes, deferrable constraints can be useful, though
their presence might cause you to have to hint some queries that the
optimizer otherwise would have optimized perfectly ;-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Thu, Aug 14, 2014 at 2:24 PM, <gints.plivna@xxxxxxxxx> wrote:
> We have used deferred constraints a few times instead of immediate
> constraints. But only because the logic could be violated during the
> transaction. The main thing is to remember that in case of deferred
> constraint violation all transaction will be rolled back, not only last
> statement. Sometimes it matters, especially in case the transaction is big
> or logic complex...
>
> Gints
>
> On 2014. gada 14. aug., at 14:50, "Zelli, Brian" <
> Brian.Zelli@xxxxxxxxxxxxxxx> wrote:
>
> I have a developer who wants me to disable or defer constraints while he
> does updates. I said no to disable since many of the tables have 4 or 5
> constraints.
>
> But what about deferring? Pros/cons? I have never used it. What would
> be the harm? This is in a Dev environment.
>
>
>
>
>
> Brian
>
>
>
>
>
> This email message may contain legally privileged and/or confidential
> information. If you are not the intended recipient(s), or the employee or
> agent responsible for the delivery of this message to the intended
> recipient(s), you are hereby notified that any disclosure, copying,
> distribution, or use of this email message is prohibited. If you have
> received this message in error, please notify the sender immediately by
> e-mail and delete this email message from your computer. Thank you.
>
>
Other related posts:
