Details

      Description

      The contents of functional specification attached to DERBY-532 should be included in the documentation.

      1. rrefsqlj13590.html
        26 kB
        Kim Haase
      2. DERBY-6571-upgrade-2.stat
        0.0 kB
        Kim Haase
      3. DERBY-6571-upgrade-2.diff
        0.8 kB
        Kim Haase
      4. DERBY-6571-upgrade.zip
        13 kB
        Kim Haase
      5. DERBY-6571-upgrade.stat
        0.1 kB
        Kim Haase
      6. DERBY-6571-upgrade.diff
        5 kB
        Kim Haase
      7. DERBY-6571-7.zip
        9 kB
        Kim Haase
      8. DERBY-6571-7.stat
        0.1 kB
        Kim Haase
      9. DERBY-6571-7.diff
        4 kB
        Kim Haase
      10. DERBY-6571-6.zip
        9 kB
        Kim Haase
      11. DERBY-6571-6.stat
        0.1 kB
        Kim Haase
      12. DERBY-6571-6.diff
        4 kB
        Kim Haase
      13. DERBY-6571-5.zip
        34 kB
        Kim Haase
      14. DERBY-6571-5.stat
        0.5 kB
        Kim Haase
      15. DERBY-6571-5.diff
        21 kB
        Kim Haase
      16. DERBY-6571-4.zip
        34 kB
        Kim Haase
      17. DERBY-6571-4.stat
        0.5 kB
        Kim Haase
      18. DERBY-6571-4.diff
        20 kB
        Kim Haase
      19. DERBY-6571-3.zip
        32 kB
        Kim Haase
      20. DERBY-6571-3.stat
        0.4 kB
        Kim Haase
      21. DERBY-6571-3.diff
        18 kB
        Kim Haase
      22. DERBY-6571-2.zip
        18 kB
        Kim Haase
      23. DERBY-6571-2.stat
        0.3 kB
        Kim Haase
      24. DERBY-6571-2.diff
        15 kB
        Kim Haase
      25. DERBY-6571.zip
        17 kB
        Kim Haase
      26. DERBY-6571.stat
        0.2 kB
        Kim Haase
      27. DERBY-6571.diff
        14 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          Thanks again, Dag!

          Committed patch DERBY-6571-upgrade-2.diff to documentation trunk at revision 1601972.

          Show
          Kim Haase added a comment - Thanks again, Dag! Committed patch DERBY-6571 -upgrade-2.diff to documentation trunk at revision 1601972.
          Hide
          ASF subversion and git services added a comment -

          Commit 1601972 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1601972 ]

          DERBY-6571 Document deferrable constraints

          Modified 1 Reference Manual topic.

          Patch: DERBY-6571-upgrade-2.diff

          Show
          ASF subversion and git services added a comment - Commit 1601972 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1601972 ] DERBY-6571 Document deferrable constraints Modified 1 Reference Manual topic. Patch: DERBY-6571 -upgrade-2.diff
          Hide
          Dag H. Wanvik added a comment -

          +1

          Show
          Dag H. Wanvik added a comment - +1
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6571-upgrade-2.diff, DERBY-6571-upgrade-2.stat, and rrefsqlj13590.html, with the needed change.

          M src/ref/rrefsqlj13590.dita

          Show
          Kim Haase added a comment - Attaching DERBY-6571 -upgrade-2.diff, DERBY-6571 -upgrade-2.stat, and rrefsqlj13590.html, with the needed change. M src/ref/rrefsqlj13590.dita
          Hide
          ASF subversion and git services added a comment -

          Commit 1601885 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1601885 ]

          DERBY-6571 Document deferrable constraints

          Modified 3 Reference Manual topics to add upgrade information.

          Patch: DERBY-6571-upgrade.diff

          Show
          ASF subversion and git services added a comment - Commit 1601885 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1601885 ] DERBY-6571 Document deferrable constraints Modified 3 Reference Manual topics to add upgrade information. Patch: DERBY-6571 -upgrade.diff
          Hide
          Kim Haase added a comment -

          Thanks for catching that, Dag. I think once again I will commit this patch and then file another one with just that one final change.

          Show
          Kim Haase added a comment - Thanks for catching that, Dag. I think once again I will commit this patch and then file another one with just that one final change.
          Hide
          Dag H. Wanvik added a comment - - edited

          Thanks, Kim! Looks good; just one minor note, otherwise +1.

          • rrefsqlj13590.html
            > Note: Deferred constraints are available only after a database has been fully upgraded to Derby Release 10.11

          That should be "deferrable constraint are available..."

          Show
          Dag H. Wanvik added a comment - - edited Thanks, Kim! Looks good; just one minor note, otherwise +1. rrefsqlj13590.html > Note: Deferred constraints are available only after a database has been fully upgraded to Derby Release 10.11 That should be "deferrable constraint are available..."
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6571-upgrade.diff, DERBY-6571-upgrade.stat, and DERBY-6571-upgrade.zip, with changes to the following topics:

          M src/ref/rrefsqljconstrchar.dita
          M src/ref/rrefsqljsetconstr.dita
          M src/ref/rrefsqlj13590.dita

          The patch should be reviewed in case something here needs restating. Thanks in advance.

          (I also added a shortdesc to the CONSTRAINT clause topic.)

          Show
          Kim Haase added a comment - Attaching DERBY-6571 -upgrade.diff, DERBY-6571 -upgrade.stat, and DERBY-6571 -upgrade.zip, with changes to the following topics: M src/ref/rrefsqljconstrchar.dita M src/ref/rrefsqljsetconstr.dita M src/ref/rrefsqlj13590.dita The patch should be reviewed in case something here needs restating. Thanks in advance. (I also added a shortdesc to the CONSTRAINT clause topic.)
          Hide
          Kim Haase added a comment -

          Reopening to add full-upgrade information.

          Show
          Kim Haase added a comment - Reopening to add full-upgrade information.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for the good work, Kim! Closing this issue

          Show
          Dag H. Wanvik added a comment - Thanks for the good work, Kim! Closing this issue
          Hide
          Kim Haase added a comment -

          Thanks, Dag!

          Committed patch DERBY-6571-7.diff to documentation trunk at revision 1600742.

          I think we're finally done.

          Show
          Kim Haase added a comment - Thanks, Dag! Committed patch DERBY-6571 -7.diff to documentation trunk at revision 1600742. I think we're finally done.
          Hide
          ASF subversion and git services added a comment -

          Commit 1600742 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1600742 ]

          DERBY-6571 Document deferrable constraints

          Modified 2 Reference Manual topics.

          Patches: DERBY-6571-7.diff

          Show
          ASF subversion and git services added a comment - Commit 1600742 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1600742 ] DERBY-6571 Document deferrable constraints Modified 2 Reference Manual topics. Patches: DERBY-6571 -7.diff
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim! This looks good! +1

          Show
          Dag H. Wanvik added a comment - Thanks, Kim! This looks good! +1
          Hide
          Kim Haase added a comment -

          Thanks again, Dag! Here's another try, DERBY-6571-7.diff, DERBY-6571-7.stat, and DERBY-6571-7.zip, with the revisions to the CONSTRAINT clause topic.

          Show
          Kim Haase added a comment - Thanks again, Dag! Here's another try, DERBY-6571 -7.diff, DERBY-6571 -7.stat, and DERBY-6571 -7.zip, with the revisions to the CONSTRAINT clause topic.
          Hide
          Dag H. Wanvik added a comment -

          As for the final sentence: "Derby performs constraint checks at the time the statement is executed, unless the constraint's mode is DEFERRED, see above." you changed that in a way that's not entirely accurate by mentioning only commit as a possible constraint checking time:

          "If the constraint mode is not DEFERRED, Derby performs constraint checks at the time the statement is executed, not when the transaction commits. See..."

          I think I'd like to change this to

          "If the constraint mode is IMMEDIATE (the default), Derby performs constraint checks at the time the statement is executed. If the constraint mode is DEFERRED, the checking is done later, typically at commit time. See.."

          The key difference here is the word "typically": the checking could also happen if we explicitly set the constraint mode to IMMEDIATE explicitly or return from a routine, but we say that elsewhere, so I think we can just say "typically" here..?

          Show
          Dag H. Wanvik added a comment - As for the final sentence: "Derby performs constraint checks at the time the statement is executed, unless the constraint's mode is DEFERRED, see above." you changed that in a way that's not entirely accurate by mentioning only commit as a possible constraint checking time: "If the constraint mode is not DEFERRED, Derby performs constraint checks at the time the statement is executed, not when the transaction commits. See..." I think I'd like to change this to "If the constraint mode is IMMEDIATE (the default), Derby performs constraint checks at the time the statement is executed. If the constraint mode is DEFERRED, the checking is done later, typically at commit time. See.." The key difference here is the word "typically": the checking could also happen if we explicitly set the constraint mode to IMMEDIATE explicitly or return from a routine, but we say that elsewhere, so I think we can just say "typically" here..?
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim! I think the new paragraph should be at least one further out. The reason is that we are describing a condition on the referenced primary or unique constraint, which is introduced in the paragraph starting "If there is a column list in the ReferencesSpecification..". The current second but the last starting with "If there is no column list in the ReferencesSpecification.." should possibly also precede our new paragraph, so let's put it after that one also, i.e. as the (new) second but the last paragraph.

          Show
          Dag H. Wanvik added a comment - Thanks, Kim! I think the new paragraph should be at least one further out. The reason is that we are describing a condition on the referenced primary or unique constraint, which is introduced in the paragraph starting "If there is a column list in the ReferencesSpecification..". The current second but the last starting with "If there is no column list in the ReferencesSpecification.." should possibly also precede our new paragraph, so let's put it after that one also, i.e. as the (new) second but the last paragraph.
          Hide
          Kim Haase added a comment -

          Thanks so much for those clarifications, Dag. I'm attaching DERBY-6571-6.diff, DERBY-6571-6.stat, and DERBY-6571-6.zip, with the changes to those two topics:

          M src/ref/rrefjtadefconstr.dita
          M src/ref/rrefsqlj13590.dita

          Hope I put that new paragraph in the right place.

          Show
          Kim Haase added a comment - Thanks so much for those clarifications, Dag. I'm attaching DERBY-6571 -6.diff, DERBY-6571 -6.stat, and DERBY-6571 -6.zip, with the changes to those two topics: M src/ref/rrefjtadefconstr.dita M src/ref/rrefsqlj13590.dita Hope I put that new paragraph in the right place.
          Hide
          Dag H. Wanvik added a comment -

          Hi Kim, I have added a limitation to the implementation which we should document, cf. version 1.9 of the functional specification.
          It should go under foreign keys (section rrefsqlj13590.html, subsection "Foreign key constraints"), just before the final "Note" paragraph, perhaps - no, make it the third but the last paragraph: "If the references clause contains a CASCADE or SET NULL referential action, the primary or unique key referenced must not be deferrable".

          Now that I look at this section again, I also see the following statement which is no longer accurate:

          (subsection "Foreign key constraints and DML"):

          "Derby performs constraint checks at the time the statement is executed, not when the transaction commits."

          This should be, now that a foreign key constraint can deferred,

          "Derby performs constraint checks at the time the statement is executed, unless the constraint's mode is DEFERRED, see above." ?

          Show
          Dag H. Wanvik added a comment - Hi Kim, I have added a limitation to the implementation which we should document, cf. version 1.9 of the functional specification. It should go under foreign keys (section rrefsqlj13590.html, subsection "Foreign key constraints"), just before the final "Note" paragraph, perhaps - no, make it the third but the last paragraph: "If the references clause contains a CASCADE or SET NULL referential action, the primary or unique key referenced must not be deferrable". Now that I look at this section again, I also see the following statement which is no longer accurate: (subsection "Foreign key constraints and DML"): "Derby performs constraint checks at the time the statement is executed, not when the transaction commits." This should be, now that a foreign key constraint can deferred, "Derby performs constraint checks at the time the statement is executed, unless the constraint's mode is DEFERRED, see above." ?
          Hide
          Dag H. Wanvik added a comment -

          Hi Kim! Thanks, The first sentence looks good. For the XA, that was my fault originally; there was a typo in the specification. The two argument method name should be "commit", not "prepare". I believe it is correct in the latest version I uploaded (version 1.8).

          Show
          Dag H. Wanvik added a comment - Hi Kim! Thanks, The first sentence looks good. For the XA, that was my fault originally; there was a typo in the specification. The two argument method name should be "commit", not "prepare". I believe it is correct in the latest version I uploaded (version 1.8).
          Hide
          Kim Haase added a comment -

          I'm working on making your suggested fixes, Dag.

          For rrefsqlj13590.html, would the following wording be correct?

          "When a deferrable constraint's constraint mode is DEFERRED before execution of a statement starts, the checking of the constraint does not take place at the end of the statement execution as usual, but only when it is explicitly or implicitly requested using one of the following mechanisms:"

          As for the second one (the language appears both in the JTA topic and the SET CONSTRAINTS topic), I am a little perplexed. I saw the second paragraph in the spec under "XA Run-time Behavior." However, the second paragraph appears to refer to a two-argument form of the javax.transaction.xa.XAResource.prepare method, which doesn't seem to exist; the only documented form is with one argument, Xid. And the behavior seemed exactly the same for both forms anyway. I should have questioned this instead of just leaving it out.

          Thanks in advance for your advice!

          Show
          Kim Haase added a comment - I'm working on making your suggested fixes, Dag. For rrefsqlj13590.html, would the following wording be correct? "When a deferrable constraint's constraint mode is DEFERRED before execution of a statement starts, the checking of the constraint does not take place at the end of the statement execution as usual, but only when it is explicitly or implicitly requested using one of the following mechanisms:" As for the second one (the language appears both in the JTA topic and the SET CONSTRAINTS topic), I am a little perplexed. I saw the second paragraph in the spec under "XA Run-time Behavior." However, the second paragraph appears to refer to a two-argument form of the javax.transaction.xa.XAResource.prepare method, which doesn't seem to exist; the only documented form is with one argument, Xid. And the behavior seemed exactly the same for both forms anyway. I should have questioned this instead of just leaving it out. Thanks in advance for your advice!
          Hide
          Kim Haase added a comment -

          Committed patch DERBY-6571-5.diff to documentation trunk at revision 1598372.

          Leaving open for more fixes.

          Show
          Kim Haase added a comment - Committed patch DERBY-6571 -5.diff to documentation trunk at revision 1598372. Leaving open for more fixes.
          Hide
          ASF subversion and git services added a comment -

          Commit 1598372 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1598372 ]

          DERBY-6571 Document deferrable constraints

          Modified one Developer's Guide topic; in the Reference Manual, modified 7 topics and the map file, and added 3 new topics.

          Patches: DERBY-6571-5.diff

          Show
          ASF subversion and git services added a comment - Commit 1598372 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1598372 ] DERBY-6571 Document deferrable constraints Modified one Developer's Guide topic; in the Reference Manual, modified 7 topics and the map file, and added 3 new topics. Patches: DERBY-6571 -5.diff
          Hide
          Dag H. Wanvik added a comment -

          Sounds good to me! Thanks +1

          Show
          Dag H. Wanvik added a comment - Sounds good to me! Thanks +1
          Hide
          Kim Haase added a comment -

          Thanks so much, Dag!

          What I am going to do is check in the last patch, and then make another patch with just the fixes to those two files. (Plus some cross-reference fixes in rrefsqlj13590.html to point to the new Security Guide, since that has been checked in now). Hope that's okay.

          Show
          Kim Haase added a comment - Thanks so much, Dag! What I am going to do is check in the last patch, and then make another patch with just the fixes to those two files. (Plus some cross-reference fixes in rrefsqlj13590.html to point to the new Security Guide, since that has been checked in now). Hope that's okay.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim! Looks good now; but I found this sentence I missed last time (sorry)..

          • rrefsqlj13590.html
            > With deferrable constraints, the checking of one or more constraints does not take place..

          It should more precisely be "When an involved deferrable constraint's constraint mode is DEFERRED before execution of a statement starts, the checking of the constraint does not take place at the end of the statement as usual, but not until......"

          I can see this sentence has become awkward now so feel free to rephrase it...

          • rrefjtadefconstr.html

          I think this new XA section looks good, but it is missing the info on

          XAResource#commit(Xid, true /* one phase commit */)

          isn't it? Cf func spec.

          Show
          Dag H. Wanvik added a comment - Thanks, Kim! Looks good now; but I found this sentence I missed last time (sorry).. rrefsqlj13590.html > With deferrable constraints, the checking of one or more constraints does not take place.. It should more precisely be "When an involved deferrable constraint's constraint mode is DEFERRED before execution of a statement starts, the checking of the constraint does not take place at the end of the statement as usual, but not until......" I can see this sentence has become awkward now so feel free to rephrase it... rrefjtadefconstr.html I think this new XA section looks good, but it is missing the info on XAResource#commit(Xid, true /* one phase commit */) isn't it? Cf func spec.
          Hide
          Mike Matrigali added a comment -

          review of list of issues fixed in 10.11 but not in 10.10 for possible compatibility issues in upcoming release.

          Documentation for new feature in 10.11, marked not for backport.

          Show
          Mike Matrigali added a comment - review of list of issues fixed in 10.11 but not in 10.10 for possible compatibility issues in upcoming release. Documentation for new feature in 10.11, marked not for backport.
          Hide
          Kim Haase added a comment -

          Thanks, Dag, for the very clear comments.

          You're right, it definitely makes sense to create a new topic for the "XA transactions and deferred constraints" material, so I did that (it is rrefjtadefconstr.dita). (That whole section is kind of a mess. I am going to file an issue to remove that empty "Notes on Product Behavior" topic and put the three subtopics under "The JTA API." Later.)

          I'm attaching DERBY-6571-5.diff, DERBY-6571-5.stat, and DERBY-6571-5.zip, with the needed changes (I hope).

          M src/devguide/cdevconcepts838850.dita
          M src/ref/rrefsistabs23241.dita
          M src/ref/rrefsqlj13590.dita
          A src/ref/rrefsqljsetconstr.dita
          M src/ref/rrefsqlj16095.dita
          M src/ref/crefsqlj35312.dita
          M src/ref/refderby.ditamap
          M src/ref/rrefcreatefunctionstatement.dita
          M src/ref/rrefsqlj42154.dita
          M src/ref/rrefcreateprocedurestatement.dita
          A src/ref/rrefjtadefconstr.dita
          A src/ref/rrefsqljconstrchar.dita

          You can see that rrefjta1003415.dita is back in its previous state and that I added the new topic for the new content.

          Thanks!

          Show
          Kim Haase added a comment - Thanks, Dag, for the very clear comments. You're right, it definitely makes sense to create a new topic for the "XA transactions and deferred constraints" material, so I did that (it is rrefjtadefconstr.dita). (That whole section is kind of a mess. I am going to file an issue to remove that empty "Notes on Product Behavior" topic and put the three subtopics under "The JTA API." Later.) I'm attaching DERBY-6571 -5.diff, DERBY-6571 -5.stat, and DERBY-6571 -5.zip, with the needed changes (I hope). M src/devguide/cdevconcepts838850.dita M src/ref/rrefsistabs23241.dita M src/ref/rrefsqlj13590.dita A src/ref/rrefsqljsetconstr.dita M src/ref/rrefsqlj16095.dita M src/ref/crefsqlj35312.dita M src/ref/refderby.ditamap M src/ref/rrefcreatefunctionstatement.dita M src/ref/rrefsqlj42154.dita M src/ref/rrefcreateprocedurestatement.dita A src/ref/rrefjtadefconstr.dita A src/ref/rrefsqljconstrchar.dita You can see that rrefjta1003415.dita is back in its previous state and that I added the new topic for the new content. Thanks!
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim. Here are the remaining comment

          • devguide/cdevconcepts838850: OK
          • ref/crefsqlj35312: OK
          • ref/rrefcreatefunctionstatement: OK
          • ref/rrefcreateprocedurestatement: OK
          • rrefjta1003415:

          The section title is somewhat at odds with what we are now putting
          in there, but apparently there isn't any other place where we
          discuss behavior for the XA API methods. Perhaps we'd better make
          another section, e.g. "XA transactions and deferred constraints".

          • ref/rrefsqlj13590:
            Paragraph starting with "If a foreign key is deferrable, ..."
            The sentence isn't precise enough, I suggest:
            "If a foreign key's constraint mode is deferred, ..."

          "ON DELETE or ON UPDATE action specification" -> "ON DELETE or ON
          UPDATE referential action specification"

          "Only if NO ACTION has been specified is the checking deferred." ->
          "Only if NO ACTION has been specified is the checking ever deferred."

          "If the primary table's referenced primary or unique key constraint is also deferrable," ->
          "If the primary table's referenced primary or unique key constraint is also deferred,"

          ", if the foreign key is also deferrable," ->
          ", if the foreign key is also deferred,"

          • ref/rrefsqlj16095: OK
          • ref/rrefsqlj42154: OK
          • ref/rrefsqljconstrchar:

          It may be good to add a caveat in this section on performance:

          "Deferred constraints sometimes impose extra performance overhead to
          allow for the deferred checking. If your application does not
          require deferred checking, we recommend declaring constraints as NOT
          DEFERRABLE (the default)."

          • ref/rrefsqljsetconstr:

          "When you change the constraint mode explicitly using SET
          CONSTRAINTS, the constraint is checked, but slightly differently
          from the way it is checked at commit time: if a violation is found,
          a statement-level exception is thrown."

          ->

          "When you change the constraint mode explicitly to IMMEDIATE using
          SET CONSTRAINTS, the constraint is checked, but slightly
          differently from the way it is checked at commit time: if a
          violation is found, a statement-level exception is thrown."

          Show
          Dag H. Wanvik added a comment - Thanks, Kim. Here are the remaining comment devguide/cdevconcepts838850: OK ref/crefsqlj35312: OK ref/rrefcreatefunctionstatement: OK ref/rrefcreateprocedurestatement: OK rrefjta1003415: The section title is somewhat at odds with what we are now putting in there, but apparently there isn't any other place where we discuss behavior for the XA API methods. Perhaps we'd better make another section, e.g. "XA transactions and deferred constraints". ref/rrefsqlj13590: Paragraph starting with "If a foreign key is deferrable, ..." The sentence isn't precise enough, I suggest: "If a foreign key's constraint mode is deferred, ..." "ON DELETE or ON UPDATE action specification" -> "ON DELETE or ON UPDATE referential action specification" "Only if NO ACTION has been specified is the checking deferred." -> "Only if NO ACTION has been specified is the checking ever deferred." "If the primary table's referenced primary or unique key constraint is also deferrable," -> "If the primary table's referenced primary or unique key constraint is also deferred," ", if the foreign key is also deferrable," -> ", if the foreign key is also deferred," ref/rrefsqlj16095: OK ref/rrefsqlj42154: OK ref/rrefsqljconstrchar: It may be good to add a caveat in this section on performance: "Deferred constraints sometimes impose extra performance overhead to allow for the deferred checking. If your application does not require deferred checking, we recommend declaring constraints as NOT DEFERRABLE (the default)." ref/rrefsqljsetconstr: "When you change the constraint mode explicitly using SET CONSTRAINTS, the constraint is checked, but slightly differently from the way it is checked at commit time: if a violation is found, a statement-level exception is thrown." -> "When you change the constraint mode explicitly to IMMEDIATE using SET CONSTRAINTS, the constraint is checked, but slightly differently from the way it is checked at commit time: if a violation is found, a statement-level exception is thrown."
          Hide
          Kim Haase added a comment -

          Thanks for all those answers, Dag! I will modify the XA information as needed. I don't think we refer to the commit method anywhere (except for a brief mention at the end of DECLARE GLOBAL TEMPORARY TABLE, which I don't think is relevant).

          There is a big table of all the SQL states that is generated for each release.

          And thanks for the advice on where to put the info on runtime behavior. I will move it into the main section on deferrable constraints under the CONSTRAINT clause, and fix cross-references as needed.

          But not till Tuesday, so please take your time with the rest of the review!

          Show
          Kim Haase added a comment - Thanks for all those answers, Dag! I will modify the XA information as needed. I don't think we refer to the commit method anywhere (except for a brief mention at the end of DECLARE GLOBAL TEMPORARY TABLE, which I don't think is relevant). There is a big table of all the SQL states that is generated for each release. And thanks for the advice on where to put the info on runtime behavior. I will move it into the main section on deferrable constraints under the CONSTRAINT clause, and fix cross-references as needed. But not till Tuesday, so please take your time with the rest of the review!
          Hide
          Dag H. Wanvik added a comment -

          Not sure all the run-time behavior should be under the SET CONSTRAINTS section; new behavior happens at insert, update, delete, commit, XA commit and routine return as well... so better centralize it in the main section on deferrable constraints, I think, and cross reference others there and keep those other explanations shorter, or omit them in favor of only the cross reference?

          Show
          Dag H. Wanvik added a comment - Not sure all the run-time behavior should be under the SET CONSTRAINTS section; new behavior happens at insert, update, delete, commit, XA commit and routine return as well... so better centralize it in the main section on deferrable constraints, I think, and cross reference others there and keep those other explanations shorter, or omit them in favor of only the cross reference?
          Hide
          Dag H. Wanvik added a comment -

          Thanks Kim,

          I updated the functional specification with a fix to the second XA paragraph: it had en error, the method should be "commit", not "prepare" there. It also has an extra argument, even in the present (albeit wrong) version.

          I think it would be good to include the XA info both under the run-time behavior of Deferrable constraints as well as next to the XA prepare mention, and possibly XA commit(d call(Xid, true) method if that is ever mentioned anywhere... We did mention the possibility of errors next to ordinary commit too.

          As for SQL states, you can omit those - it seems our practice is not to mention them... although didn't we have a (generated) list somewhere?

          I'll review the latest patch for the usage of "deferrable".

          Show
          Dag H. Wanvik added a comment - Thanks Kim, I updated the functional specification with a fix to the second XA paragraph: it had en error, the method should be "commit", not "prepare" there. It also has an extra argument, even in the present (albeit wrong) version. I think it would be good to include the XA info both under the run-time behavior of Deferrable constraints as well as next to the XA prepare mention, and possibly XA commit(d call(Xid, true) method if that is ever mentioned anywhere... We did mention the possibility of errors next to ordinary commit too. As for SQL states, you can omit those - it seems our practice is not to mention them... although didn't we have a (generated) list somewhere? I'll review the latest patch for the usage of "deferrable".
          Hide
          Kim Haase added a comment -

          Thanks for all the advice, Dag. Here's another patch: DERBY-6571-4.diff, DERBY-6571-4.stat, and DERBY-6571-4.zip, with these changes:

          M src/devguide/cdevconcepts838850.dita
          A src/ref/rrefsqljconstrchar.dita
          M src/ref/refderby.ditamap
          M src/ref/rrefcreatefunctionstatement.dita
          M src/ref/rrefsqlj13590.dita
          M src/ref/rrefsistabs23241.dita
          M src/ref/rrefjta1003415.dita
          M src/ref/rrefcreateprocedurestatement.dita
          M src/ref/rrefsqlj16095.dita
          M src/ref/crefsqlj35312.dita
          M src/ref/rrefsqlj42154.dita
          A src/ref/rrefsqljsetconstr.dita

          I'm not at all sure I used "deferred" and "deferrable" correctly everywhere, but I hope it's an improvement.

          Show
          Kim Haase added a comment - Thanks for all the advice, Dag. Here's another patch: DERBY-6571 -4.diff, DERBY-6571 -4.stat, and DERBY-6571 -4.zip, with these changes: M src/devguide/cdevconcepts838850.dita A src/ref/rrefsqljconstrchar.dita M src/ref/refderby.ditamap M src/ref/rrefcreatefunctionstatement.dita M src/ref/rrefsqlj13590.dita M src/ref/rrefsistabs23241.dita M src/ref/rrefjta1003415.dita M src/ref/rrefcreateprocedurestatement.dita M src/ref/rrefsqlj16095.dita M src/ref/crefsqlj35312.dita M src/ref/rrefsqlj42154.dita A src/ref/rrefsqljsetconstr.dita I'm not at all sure I used "deferred" and "deferrable" correctly everywhere, but I hope it's an improvement.
          Hide
          Kim Haase added a comment - - edited

          Changed my mind. The XA discussion should be under "Runtime behavior", since it's about runtime behavior. However, the "Runtime behavior" section is all about the constraint mode, not the initial constraint characteristics – so I think I need to move it to the SET CONSTRAINTS topic.

          Show
          Kim Haase added a comment - - edited Changed my mind. The XA discussion should be under "Runtime behavior", since it's about runtime behavior. However, the "Runtime behavior" section is all about the constraint mode, not the initial constraint characteristics – so I think I need to move it to the SET CONSTRAINTS topic.
          Hide
          Kim Haase added a comment -

          Thanks for these, Dag. Especially the reminder about XA behavior. I was having trouble figuring out where to put it and forgot. I think it should be in the main discussion in the CONSTRAINT clause topic, and also maybe in http://db.apache.org/derby/docs/10.10/ref/rrefjta1003415.html (in the section on JTA near the end), almost the only place in the docs where the XAResource.prepare method is mentioned. BTW, I don't quite see the difference between the two paragraphs in the spec under "XA Run-time Behavior". They both seem to say the same thing – what am I missing?

          I have not been providing the exact SQLException code in topics that mention that exceptions are thrown. It seems like an implementation detail. Would you prefer that these be stated?

          I'll work on another patch.

          Show
          Kim Haase added a comment - Thanks for these, Dag. Especially the reminder about XA behavior. I was having trouble figuring out where to put it and forgot. I think it should be in the main discussion in the CONSTRAINT clause topic, and also maybe in http://db.apache.org/derby/docs/10.10/ref/rrefjta1003415.html (in the section on JTA near the end), almost the only place in the docs where the XAResource.prepare method is mentioned. BTW, I don't quite see the difference between the two paragraphs in the spec under "XA Run-time Behavior". They both seem to say the same thing – what am I missing? I have not been providing the exact SQLException code in topics that mention that exceptions are thrown. It seems like an implementation detail. Would you prefer that these be stated? I'll work on another patch.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim! Comments:

          • rrefsqljconstrchar.html

          > The deferrability or checking time of a constraint cannot be altered.

          The checking time can be changed by using SET CONSTRAINTS. It is the default checking time that cannot be
          altered. So maybe if we say "the ConstraintCheckTime" cannot be altered, we'd be safe?
          It is the default check time since at transaction end (commit or rollback) the constraint mode reverts to ConstraintCheckTime.

          • rrefsqljsetconstr.html

          > The SET CONSTRAINTS statement allows you to set the constraint characteristic for one or more constraints either to DEFERRED or to IMMEDIATE.

          This is slightly wrong (we changed the design). It should read: "The SET CONSTRAINTS statement allows you to set the constraint mode for one or more constraints either to DEFERRED or to IMMEDIATE." (i.e. "mode" instead of "characteristics")

          Last item, the change from "deferred" to "deferrable" (better use that always and also specify "constraint mode deferred" or "constraint mode immediate" depending on the case), as you already suggested.

          Btw, did you include the material on XA and deferrable constraints anywhere?

          Show
          Dag H. Wanvik added a comment - Thanks, Kim! Comments: rrefsqljconstrchar.html > The deferrability or checking time of a constraint cannot be altered. The checking time can be changed by using SET CONSTRAINTS. It is the default checking time that cannot be altered. So maybe if we say "the ConstraintCheckTime" cannot be altered, we'd be safe? It is the default check time since at transaction end (commit or rollback) the constraint mode reverts to ConstraintCheckTime. rrefsqljsetconstr.html > The SET CONSTRAINTS statement allows you to set the constraint characteristic for one or more constraints either to DEFERRED or to IMMEDIATE. This is slightly wrong (we changed the design). It should read: "The SET CONSTRAINTS statement allows you to set the constraint mode for one or more constraints either to DEFERRED or to IMMEDIATE." (i.e. "mode" instead of "characteristics") Last item, the change from "deferred" to "deferrable" (better use that always and also specify "constraint mode deferred" or "constraint mode immediate" depending on the case), as you already suggested. Btw, did you include the material on XA and deferrable constraints anywhere?
          Hide
          Dag H. Wanvik added a comment -

          So, the answer to the question is "yes, it would be useful"

          Show
          Dag H. Wanvik added a comment - So, the answer to the question is "yes, it would be useful"
          Hide
          Dag H. Wanvik added a comment -

          I believe the right term is "deferrable constraints". The short hand "deferred constraints" really means a "deferrable constraint" that has constraint mode "deferred". C.f. this quote from the standard:

          "Every constraint is either deferrable or non-deferrable. Within an SQL-transaction, every constraint has a
          constraint mode; if a constraint is non-deferrable, then its constraint mode is always immediate; otherwise, it
          is either immediate or deferred."

          Sorry that lack of precision sneaked into the functional specification

          Show
          Dag H. Wanvik added a comment - I believe the right term is "deferrable constraints". The short hand "deferred constraints" really means a "deferrable constraint" that has constraint mode "deferred". C.f. this quote from the standard: "Every constraint is either deferrable or non-deferrable. Within an SQL-transaction, every constraint has a constraint mode; if a constraint is non-deferrable, then its constraint mode is always immediate; otherwise, it is either immediate or deferred." Sorry that lack of precision sneaked into the functional specification
          Hide
          Kim Haase added a comment -

          A thought about terminology while you review these, Dag – the functional spec says "deferred constraints" while the issue title says "deferrable constraints". Is one more correct than the other? Would it be helpful to change "deferred" to "deferrable" in the next patch where appropriate?

          Show
          Kim Haase added a comment - A thought about terminology while you review these, Dag – the functional spec says "deferred constraints" while the issue title says "deferrable constraints". Is one more correct than the other? Would it be helpful to change "deferred" to "deferrable" in the next patch where appropriate?
          Hide
          Kim Haase added a comment -

          Thanks so much for all the comments and clarifications, Dag.

          Attaching DERBY-6571-3.diff, DERBY-6571-3.stat, and DERBY-6571-3.zip, with additional changes:

          A src/ref/rrefsqljsetconstr.dita
          M src/ref/rrefsqlj16095.dita
          M src/ref/crefsqlj35312.dita
          M src/ref/refderby.ditamap
          M src/ref/rrefsqlj42154.dita
          M src/ref/rrefcreatefunctionstatement.dita
          A src/ref/rrefsqljconstrchar.dita
          M src/ref/rrefsqlj13590.dita
          M src/ref/rrefsistabs23241.dita
          M src/ref/rrefcreateprocedurestatement.dita
          M src/devguide/cdevconcepts838850.dita

          Thanks for your careful reviews.

          Show
          Kim Haase added a comment - Thanks so much for all the comments and clarifications, Dag. Attaching DERBY-6571 -3.diff, DERBY-6571 -3.stat, and DERBY-6571 -3.zip, with additional changes: A src/ref/rrefsqljsetconstr.dita M src/ref/rrefsqlj16095.dita M src/ref/crefsqlj35312.dita M src/ref/refderby.ditamap M src/ref/rrefsqlj42154.dita M src/ref/rrefcreatefunctionstatement.dita A src/ref/rrefsqljconstrchar.dita M src/ref/rrefsqlj13590.dita M src/ref/rrefsistabs23241.dita M src/ref/rrefcreateprocedurestatement.dita M src/devguide/cdevconcepts838850.dita Thanks for your careful reviews.
          Hide
          Dag H. Wanvik added a comment -

          Probably at the end of section http://db.apache.org/derby/docs/10.10/devguide/cdevconcepts838850.html would be a good place to mention that any outstanding violations of deferred constraints will be checked at commit time, so the Connection#commit may throw an error (SQL state "23506" for unique/primary key, "23514" for check , or "23516" for foreign key).

          Show
          Dag H. Wanvik added a comment - Probably at the end of section http://db.apache.org/derby/docs/10.10/devguide/cdevconcepts838850.html would be a good place to mention that any outstanding violations of deferred constraints will be checked at commit time, so the Connection#commit may throw an error (SQL state "23506" for unique/primary key, "23514" for check , or "23516" for foreign key).
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim,

          • missing sentence part: "before attempting to commit the transaction"
          • store procedures -> stored routines (procedure and functions). Both can use SQL so they can both
            set the constraint mode. Thanks for catching that!
          • referred -> deferred, yup
          • autocommit: deferred constraint are useless with auto-commit, since one wouldn't notice any different behavior:
            the end of the statement equals the end of the transaction.

          I'll have a look in the developer guide so I can suggest a placement.

          Show
          Dag H. Wanvik added a comment - Thanks, Kim, missing sentence part: "before attempting to commit the transaction" store procedures -> stored routines (procedure and functions). Both can use SQL so they can both set the constraint mode. Thanks for catching that! referred -> deferred, yup autocommit: deferred constraint are useless with auto-commit, since one wouldn't notice any different behavior: the end of the statement equals the end of the transaction. I'll have a look in the developer guide so I can suggest a placement.
          Hide
          Kim Haase added a comment -

          Thanks so much, Dag! I'm working on incorporating these.

          There seems to be an unfinished sentence in this comment:

          E.g. "If the check fails, the transaction is not rolled back; an
          error here constitutes a statement level error only, so one can use
          this statement to check if all constraints are fulfilled before

          Before what? I hesitate to guess.

          The information to be added about stored procedures – I gather this is specific to procedures and does not apply to functions? Or does it apply to both?

          I'm thinking the info probably belongs in the CONSTRAINT clause topic, but there should be links to it from the topics on CREATE PROCEDURE (and maybe on CREATE FUNCTION if applicable), as you say at the end.

          In the suggested rewording below, should "referred" be "deferred"?

          "any delete of a parent row can lead to a foreign key violation
          immediately (or at deferred checking time, if the foreign key is also
          referred, as the case may be) when the last of possibly several
          key duplicates of the referenced key is deleted or updated"

          I don't think we do discuss commits in the Reference Manual, but we do in the Developer's Guide – there's a whole section (multiple topics) on transactions. There's a topic "Statement versus transaction runtime rollback" that might benefit from a mention of constraints. Possibly others too. Is the behavior different with auto-commit on vs. off? Let me know where you think the best place to mention constraints might be.

          Thanks again.

          Show
          Kim Haase added a comment - Thanks so much, Dag! I'm working on incorporating these. There seems to be an unfinished sentence in this comment: E.g. "If the check fails, the transaction is not rolled back; an error here constitutes a statement level error only, so one can use this statement to check if all constraints are fulfilled before Before what? I hesitate to guess. The information to be added about stored procedures – I gather this is specific to procedures and does not apply to functions? Or does it apply to both? I'm thinking the info probably belongs in the CONSTRAINT clause topic, but there should be links to it from the topics on CREATE PROCEDURE (and maybe on CREATE FUNCTION if applicable), as you say at the end. In the suggested rewording below, should "referred" be "deferred"? "any delete of a parent row can lead to a foreign key violation immediately (or at deferred checking time, if the foreign key is also referred, as the case may be) when the last of possibly several key duplicates of the referenced key is deleted or updated" I don't think we do discuss commits in the Reference Manual, but we do in the Developer's Guide – there's a whole section (multiple topics) on transactions. There's a topic "Statement versus transaction runtime rollback" that might benefit from a mention of constraints. Possibly others too. Is the behavior different with auto-commit on vs. off? Let me know where you think the best place to mention constraints might be. Thanks again.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim! Some comments:

          • SET CONSTRAINTS section: We should add a note after this sentence:

          > When you use the statement to change a constraint from DEFERRED to
          > IMMEDIATE, the constraint is checked > as soon as the statement is
          > executed.

          E.g. "If the check fails, the transaction is not rolled back; an
          error here constitutes a statement level error only, so one can use
          this statement to check if all constraints are fulfilled before

          > Derby must find the name of the corresponding index by inspecting
          > the system tables, which can impede performance. attempting a
          > commit. (A commit with remaining violations would roll back the
          > transaction, see the section on commit.)"

          Sorry I wasn't clear here: it is the user (application) that would
          need to find the index name by performing queries against the system
          tables. The main worry here isn't performance, probably, but that it's
          awkward and requires extra (not portable) SQL.

          • rrefsqlj13590.html

          > - A SET CONSTRAINTS statement is executed

          This should be "A SET CONSTRAINTS statement which sets the constraint
          mode to IMMEDIATE is executed".

          > - A return from a stored procedure reverts the constraint mode to immediate

          We ought perhaps to explain this item in a little more detail
          somewhere. We could do it here and/or in a section on calls of stored
          procedures, your call:

          "If the constraint mode of a constraint is immediate before a call to
          a store procedure, and the stored procedure sets the constraint mode
          of that constraint to deferred, the constraint mode is implicitly
          re-set to immediate on return from the stored procedure. This is
          because the constraint mode is pushed on a stack when we enter the
          stored procedure, cf other session state variables, like the current
          role. If a constraint violation happens as a result of this, the
          transaction is rolled back and an exception is thrown."

          > any delete of a parent row can only lead to a violation (or deferred
          > checking as the case may be) when the last of possibly several
          > duplicates is deleted.

          This reads a bit weird: Let me try a rephrasing:

          "any delete of a parent row can lead to a foreign key violation
          immediately (or at deferred checking time, if the foreign key is also
          referred, as the case may be) when the last of possibly several
          key duplicates of the referenced key is deleted or updated"

          • rrefsqljconstrchar.html

          > When you change the constraint mode from deferred to immediate, the
          > constraint is checked.

          -> "When you change the constraint mode explicitly using SET
          CONSTRAINTS, the constraint is checked, but slightly differently
          that at commit time: if a violation ...."

          It is probably best to move this paragraph after the next one: "If a violation is seen at commit time..."

          We should probably look at the section that explains commit (if there is one!), and add the info (or a cross reference) on constraint checking there too. Likewise we should add info (or a cross reference) in a section on stored procedure calls

          Show
          Dag H. Wanvik added a comment - Thanks, Kim! Some comments: SET CONSTRAINTS section: We should add a note after this sentence: > When you use the statement to change a constraint from DEFERRED to > IMMEDIATE, the constraint is checked > as soon as the statement is > executed. E.g. "If the check fails, the transaction is not rolled back; an error here constitutes a statement level error only, so one can use this statement to check if all constraints are fulfilled before > Derby must find the name of the corresponding index by inspecting > the system tables, which can impede performance. attempting a > commit. (A commit with remaining violations would roll back the > transaction, see the section on commit.)" Sorry I wasn't clear here: it is the user (application) that would need to find the index name by performing queries against the system tables. The main worry here isn't performance, probably, but that it's awkward and requires extra (not portable) SQL. rrefsqlj13590.html > - A SET CONSTRAINTS statement is executed This should be "A SET CONSTRAINTS statement which sets the constraint mode to IMMEDIATE is executed". > - A return from a stored procedure reverts the constraint mode to immediate We ought perhaps to explain this item in a little more detail somewhere. We could do it here and/or in a section on calls of stored procedures, your call: "If the constraint mode of a constraint is immediate before a call to a store procedure, and the stored procedure sets the constraint mode of that constraint to deferred, the constraint mode is implicitly re-set to immediate on return from the stored procedure. This is because the constraint mode is pushed on a stack when we enter the stored procedure, cf other session state variables, like the current role. If a constraint violation happens as a result of this, the transaction is rolled back and an exception is thrown." > any delete of a parent row can only lead to a violation (or deferred > checking as the case may be) when the last of possibly several > duplicates is deleted. This reads a bit weird: Let me try a rephrasing: "any delete of a parent row can lead to a foreign key violation immediately (or at deferred checking time, if the foreign key is also referred, as the case may be) when the last of possibly several key duplicates of the referenced key is deleted or updated" rrefsqljconstrchar.html > When you change the constraint mode from deferred to immediate, the > constraint is checked. -> "When you change the constraint mode explicitly using SET CONSTRAINTS, the constraint is checked, but slightly differently that at commit time: if a violation ...." It is probably best to move this paragraph after the next one: "If a violation is seen at commit time..." We should probably look at the section that explains commit (if there is one!), and add the info (or a cross reference) on constraint checking there too. Likewise we should add info (or a cross reference) in a section on stored procedure calls
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6571-2.diff, DERBY-6571-2.stat, and DERBY-6571-2.zip. The only change is to add the modified "SET statements" topic to the patch.

          M src/ref/crefsqlj35312.dita

          Show
          Kim Haase added a comment - Attaching DERBY-6571 -2.diff, DERBY-6571 -2.stat, and DERBY-6571 -2.zip. The only change is to add the modified "SET statements" topic to the patch. M src/ref/crefsqlj35312.dita
          Hide
          Dag H. Wanvik added a comment -

          Thanks for the quick work here, Kim! The first question:

          > "If a row in the parent table is deleted (or update to change to
          > refrenced key)...", which seems a little garbled. > Should it be
          > something like "or updated so as to modify the referenced key"?

          Yes, your understanding is correct. I'll review the patch tomorrow.

          Show
          Dag H. Wanvik added a comment - Thanks for the quick work here, Kim! The first question: > "If a row in the parent table is deleted (or update to change to > refrenced key)...", which seems a little garbled. > Should it be > something like "or updated so as to modify the referenced key"? Yes, your understanding is correct. I'll review the patch tomorrow.
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6571.diff, DERBY-6571.stat, and DERBY-6571.zip, with these changes:

          A src/ref/rrefsqljsetconstr.dita
          M src/ref/rrefsqlj16095.dita
          M src/ref/refderby.ditamap
          M src/ref/rrefsqlj42154.dita
          A src/ref/rrefsqljconstrchar.dita
          M src/ref/rrefsqlj13590.dita
          M src/ref/rrefsistabs23241.dita

          This is just an initial patch for this work. I'm not sure that I've included everything that should be included or put it all in the most logical place, or that the wording is correct, or that the example makes any sense (it would probably be good to have an example of a table-level constraint instead of or in addition to the column-level one).

          So I will be very grateful for a review. Thanks for the excellent spec.

          Show
          Kim Haase added a comment - Attaching DERBY-6571 .diff, DERBY-6571 .stat, and DERBY-6571 .zip, with these changes: A src/ref/rrefsqljsetconstr.dita M src/ref/rrefsqlj16095.dita M src/ref/refderby.ditamap M src/ref/rrefsqlj42154.dita A src/ref/rrefsqljconstrchar.dita M src/ref/rrefsqlj13590.dita M src/ref/rrefsistabs23241.dita This is just an initial patch for this work. I'm not sure that I've included everything that should be included or put it all in the most logical place, or that the wording is correct, or that the example makes any sense (it would probably be good to have an example of a table-level constraint instead of or in addition to the column-level one). So I will be very grateful for a review. Thanks for the excellent spec.
          Hide
          Kim Haase added a comment -

          I'm working away at this and finding the spec quite clear, thanks!

          There is one confusing parenthesis in the "Foreign keys" section. The second sentence has "If a row in the parent table is deleted (or update to change to refrenced key)...", which seems a little garbled. Should it be something like "or updated so as to modify the referenced key"?

          Thanks!

          Show
          Kim Haase added a comment - I'm working away at this and finding the spec quite clear, thanks! There is one confusing parenthesis in the "Foreign keys" section. The second sentence has "If a row in the parent table is deleted (or update to change to refrenced key)...", which seems a little garbled. Should it be something like "or updated so as to modify the referenced key"? Thanks!
          Hide
          Kim Haase added a comment -

          Thanks for filing this, Dag. I only just saw it because of the Apache mail backlog!

          Show
          Kim Haase added a comment - Thanks for filing this, Dag. I only just saw it because of the Apache mail backlog!

            People

            • Assignee:
              Kim Haase
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development