Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Halloween Protection Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 6:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
bitbucket-25253 (10/30/2012)
Hmm caught me on that one with Halloween coming up in the USA tomorrow thought this was one of those joke questions ... OH well so be it


Same here... I tried to think of what statement I'd want to protect against the most... and it was a toss up between update and delete.... sadly I picked the wrong one.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1379286
Posted Wednesday, October 31, 2012 6:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 1,262, Visits: 2,300
Nice interesting question Steve


Sujeet Singh
Post #1379287
Posted Wednesday, October 31, 2012 8:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
Happy Halloween All



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1379345
Posted Wednesday, October 31, 2012 8:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 8,289, Visits: 8,742
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too


No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.



I beg to differ.

You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.

Consider a DELETE statement of the form:

DELETE  TABLE_NAME
WHERE COL < CONST

as opposed to

DELETE  TABLE_NAME
WHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)

You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else. It is of course rather a good thing that the spool just happens to ensure that the Halloween Problem can't arise, so that no Halloween protection is needed in this case. One side effect of that good thing is that that 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only, because the delete issue didn't exist; and the Halloween Problem is the name that they gave to the problem they discussed that day, and that is a problem with update only, and Halloween Protectiopn means protectin from that update only problem, not anything else. At the 1995 System R/SQL reunion surviving members of the team talked about the early development of the language now called SQL, and Pat Selinger (she may still have been Pat Griffiths when she and Morton Astrahan identified this problem, or maybe she was already married by then) gave a description of this particular thing. Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available as an SRC technical note, which is currently accessible at his wesite and (in a Russian version) at CITForum.ru. The relevant passage is here.

I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.


Tom
Post #1379358
Posted Wednesday, October 31, 2012 9:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 4,245, Visits: 3,325
All, have a scary Halloween!

Steve, thanks for the question!
Post #1379373
Posted Wednesday, October 31, 2012 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:14 AM
Points: 41, Visits: 288
L' Eomot Inversé (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too


No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.



I beg to differ.

You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.

You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.

Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME

An average can be calculated and maintained incrementally without spooling data.


... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.

Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."
In a nutshell, the problem boils down to: a data operation is expected to behave in a predetermined way, but is not due to a side effect of its work unexpectedly affecting its own behaviour recursively.


I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.

In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?
Post #1379391
Posted Wednesday, October 31, 2012 12:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 17, 2014 6:21 AM
Points: 1,073, Visits: 6,477
Well,

Happy Halloween!!!


-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."


Post #1379479
Posted Wednesday, October 31, 2012 1:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, February 16, 2014 3:14 PM
Points: 471, Visits: 509
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.

UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644

Further down the doc there is a section that states:
38. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is an insert operator.
39. The method of claim 18 wherein said generating step includes: generating an update plan wherein said update operator is a delete operator

Here is also a KB article that specifically indicates that an INSERT, UPDATE, or DELETE statement may need Halloween protection. http://support.microsoft.com/kb/294860


Aigle de Guerre!
Post #1379501
Posted Wednesday, October 31, 2012 9:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 8,289, Visits: 8,742
This probably a pointless argumnent, because it isn't about anything technical, just about historical and terminological accuracy, and it does appear that quite a few people have adopted historically inaccurate terminology (which is a pretty frequent phenomenon - for example we call Heath's normal form, published in 1971, "Boyce-Codd Normal Form" after a paper published several years later, although the earlier paper was certainly known to the senior of the authors of the later paper). But I'm going to make the argument for historical accuracy (especially since several of the people involved are still alive) anyway (if I were in touch with any of them I would ask them for their views, but unfortunately I'm not).
craig 81366 (10/31/2012)
L' Eomot Inversé (10/31/2012)

Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.

Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME


Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason. If you think you can deliver a result for a query for AVG without first reading all the records, then think again, because you are horribly wrong. You can't deliver the first value of AVG until you have done that. Then, why read the records again? Sure, you could track changes in AVG as the table changed, if you wanted to and had an awful lot of compute power to spare, but poeple neither wanted to nor had that power to spare so in the early versions of SQL (or of SEQUEL, or whatever it was called before it became SEQUEL) AVG was not recomputed. It isn't today, not because some additional spool operation, beyond what is required to compute AVG in the first place, is introduced, but because AVG has to be computed.

An average can be calculated and maintained incrementally without spooling data.
It can't be computed for the first time without a spool (ie reading all the input before going to the next step).

... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.

Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."

So "something that's "a sort of variation on that" is an instance of "that"? YG&LF, or perhaps GHU - what else can I say? Why did she say "a sort of variation on that" rather than just "another instance of that" or "another example of that", I wonder? Perhaps she though the various possible phrases had different interpretations, and chose one which can't reasonably nbe taken to mean what you want it to mean precisely because what you want it to mean is not what she intended to say? I don't know, I wasn't there at that meeting, but I'm not the one trying to attribute to her a meaning which what she actually said cleafrly doesn't imply.
In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?

So all spheroids are spheres, are they? They are all round - any corners would make them not spheroids. Even on a technical, as opposed to historical, basis your argument doesn't fly. Of course if in your sphere example the properties of roundness concerned require both that there be a single focus and that all diameters be the same (either of those is a consequence of the other in some geometries, but not in all) then yes, the new thing would be a sphere. That's rather a big and nasty requirement on the properties, I guess the equivalent condition wrt the Halloween Problem would be that it involved an insert statement, so you are seem to be comparing tautologies with contradictions - not usually a useful passtime.


Tom
Post #1379599
Posted Wednesday, October 31, 2012 10:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 8,289, Visits: 8,742
Meow Now (10/31/2012)
I stumbled across Freedman's explanation in my search on the subject and was thrown by the options as well. Either way, I selected the UPDATE choice as the most likely candidate.

UPDATE: Found the patent and the update is referring to an update plan (any data modification).
http://www.google.com/patents/US6122644

Isn't life wonderful. Here we have a problem identified by IBM back in the 70s, and solved by them back in the 70s, back in the days when the US sopreme Court was still sufficiently sane to assert that software patents were illegal. Then MS gets a patent on solving it in 2000, with a patent that has no non-obvious invention in it (at least nothing non-obvious to anyone familiar with the state of the art in, I think, the late 70s) after the law has been changed (by magic, or perhaps by the appointment of supreme court judges favoured by presidents whose campaign funds came from industrialists who wanted software patents).
We all knew the US patent system had become insane some time ago. What none of us outside the US know is why you pretend that your plutocracy is a democracy, claiming that the people rule when in fact only the money rules, as clearly evidenced by the changes in the last few decades to your copyright system and your patent system (amongst many other indicators).


Tom
Post #1379604
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse