MySQL Forums
Forum List  »  Stored Procedures

Re: When is an INSERT DETERMINISTIC?
Posted by: Jon Stephens
Date: August 09, 2017 03:45AM

Quote

It fails entirely to explain what this means; if it is strictly non-deterministic but also happens to be replication safe,...

There's nothing to "explain"; it's a simple statement of fact; whether a routine is nondeterministic does not establish its replicability (or nonreplicability). And we've not been talking about replication in any case; we've been discussing deterministic vs nondeterministic routines.

Quote

...then that suggests that marking the procedure DETERMINISTIC anyway shouldn't produce inconsistencies.

I fail to see any basis for this conclusion. The issues are not related, as I've said.

For purposes of the discussion so far, it's not necessary to elaborate on what the optimisations in question are--the information given is already sufficient to make the point that it is a bad idea to mark a nondeterministic routine as DETERMINISTIC, just as you know it's a bad idea to set your hand on a stove eye without being sure beforehand that it's turned off--if it's not turned off, the exact temperature setting is irrelevant to the fact you're very likely going to get burnt.

(This is not to say that the question of which optimisations get suppressed in such cases is not of some interest; if it's not covered somewhere in the Optimisation chapter, file a bug against the documentation and we'll try to do something about it. IIRC, the chapter also contains information about tools provided in MySQL that you can use to help you test queries, etc., to see how the optimiser handles them.)

Quote

For example, if my use of NOW() is only minor,...

There's no "minor" here; if your routine calls NOW(), it's not deterministic.

Quote

...and in my application it wouldn't matter if a few touch-only inserts are ignored, so long as one goes through every now and then (e.g- once an hour), then does it matter if the procedure is "incorrectly" optimised?

Why make this an issue by insisting on labelling the routine in a fashion which the documentation advises you not to do?


cheers,

Jon Stephens
MySQL Documentation Team @ Oracle
Stockholm, Sweden

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: When is an INSERT DETERMINISTIC?
21
August 09, 2017 03:45AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.