Re: documenting whether or not the seq_of_parameters to executemany is expected to be run in order given
Marc-Andre Lemburg <[email protected]> Mon, 10 Apr 2023 17:31:16 +0200
| Newsgroups | gmane.comp.python.db |
|---|---|
| Organization | eGenix.com Software GmbH; http://www.egenix.com/ |
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --===============5848710787110831511== Content-Type: multipart/alternative; boundary="------------RtR8Wer0Bu57IrE27YO1YDXP" Content-Language: en-US This is a multi-part message in MIME format. --------------RtR8Wer0Bu57IrE27YO1YDXP Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 09.04.2023 15:13, Mike Bayer wrote: > It's my understanding that a lot of executemany() implementations will > actually run the given statement once per each element in the sequence > of parameters. such as, it creates a prepared statement handle for > the statement, then runs each parameter set. a driver that works this > way can document that executemany() is guaranteed to invoke in that > order, because it's a driver-determined behavior, not the database. > > a driver that packages all the parameter sets into a single > concatenated statement of course has a different story, they can't > guarantee this order. > > but in both cases it can be documented and in the former case it can > be documented as the order being guaranteed also. The DB-API wording is vague in this respect, mostly because the Python driver cannot guarantee whether the statements actually get executed in the same order as the parameters are passed to the method. Some databases optimize such bulk executes to gain performance (e.g. reorder INSERTs to make them match partitions) or seemingly randomize executes (e.g. when running against a cluster to spread load). I agree that we should probably clearly state this in PEP 249 using your text: """ The order in which parameters are processed by .executemany() should not be assumed to be in the same order as the parameters were given, since the underlying database drivers or backends may optimize the way the operations are processed. """ Related to this: databases often also don't necessarily guarantee that INSERT order is stable, i.e. a SELECT will return the rows in the same order they were added or in primary key order. Many do, but esp. cluster ones typically don't. When writing code trying to work with different backends, this can easily create problems, if you're not aware. I was once bitten by this a long time ago, when running tests against a Terraform data warehouse. > > On Sun, Apr 9, 2023, at 5:19 AM, Tony Locke wrote: >> My initial thought is that the driver must pass the executemany >> parameters on to the server without changing the order of the >> parameters. Maybe that should be explicitly stated in the spec. Once >> the server has the parameters then I think the behaviour becomes DBMS >> dependent. I guess my philosophy is that the humble driver should just >> faithfully pass things back and forth while interfering as little as >> possible. >> >> On Fri, 7 Apr 2023 at 19:09, Mike Bayer <[email protected]> wrote: >> > >> > OK that's two "sure, let's absolve ourselves of this problem" >> responses :) >> > >> > any opinion on executemany() being less useful if this requirement >> is not established, and/or encouraging DBAPI authors to at least >> *document* this themselves and maybe *prefer* maintaining ordering ? >> > >> > >> > >> > On Fri, Apr 7, 2023, at 1:54 PM, Erlend Egeberg Aasland wrote: >> > >> > On Fri, 7 Apr 2023 at 19:15, Mike Bayer <[email protected]> >> wrote: >> > >> > >> > […] The scope here is, should pep-249 add some verbiage: "the order >> in which parameters are processed by executemany() should not be >> assumed to be in the order the parameters were given". […] >> > >> > >> > Sounds good to me. >> > >> > Erlend >> > >> > >> > >> > _______________________________________________ >> > DB-SIG maillist - [email protected] >> > https://mail.python.org/mailman/listinfo/db-sig >> > > > _______________________________________________ > DB-SIG maillist [email protected] > https://mail.python.org/mailman/listinfo/db-sig -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Experts (#1, Apr 10 2023) >>> Python Projects, Coaching and Support ... https://www.egenix.com/ >>> Python Product Development ... https://consulting.egenix.com/ ________________________________________________________________________ ::: We implement business ideas - efficiently in both time and costs ::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 https://www.egenix.com/company/contact/ https://www.malemburg.com/ --------------RtR8Wer0Bu57IrE27YO1YDXP Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body> <div class="moz-cite-prefix">On 09.04.2023 15:13, Mike Bayer wrote:<br> </div> <blockquote type="cite" cite="mid:[email protected]"> <title></title> <div>It's my understanding that a lot of executemany() implementations will actually run the given statement once per each element in the sequence of parameters. such as, it creates a prepared statement handle for the statement, then runs each parameter set. a driver that works this way can document that executemany() is guaranteed to invoke in that order, because it's a driver-determined behavior, not the database.<br> </div> <div><br> </div> <div>a driver that packages all the parameter sets into a single concatenated statement of course has a different story, they can't guarantee this order.<br> </div> <div><br> </div> <div>but in both cases it can be documented and in the former case it can be documented as the order being guaranteed also.<br> </div> </blockquote> <p>The DB-API wording is vague in this respect, mostly because the Python driver cannot guarantee whether the statements actually get executed in the same order as the parameters are passed to the method.</p> <p>Some databases optimize such bulk executes to gain performance (e.g. reorder INSERTs to make them match partitions) or seemingly randomize executes (e.g. when running against a cluster to spread load).</p> I agree that we should probably clearly state this in PEP 249 using your text:<br> <p>"""<br> The order in which parameters are processed by .executemany() should not be assumed to be in the same order as the parameters were given, since the underlying database drivers or backends may optimize the way the operations are processed.<br> """<br> </p> <p>Related to this: databases often also don't necessarily guarantee that INSERT order is stable, i.e. a SELECT will return the rows in the same order they were added or in primary key order. Many do, but esp. cluster ones typically don't. When writing code trying to work with different backends, this can easily create problems, if you're not aware. I was once bitten by this a long time ago, when running tests against a Terraform data warehouse.<br> </p> <p><br> </p> <blockquote type="cite" cite="mid:[email protected]"> <div><br> </div> <div>On Sun, Apr 9, 2023, at 5:19 AM, Tony Locke wrote:<br> </div> <blockquote type="cite" id="qt"> <div>My initial thought is that the driver must pass the executemany<br> </div> <div>parameters on to the server without changing the order of the<br> </div> <div>parameters. Maybe that should be explicitly stated in the spec. Once<br> </div> <div>the server has the parameters then I think the behaviour becomes DBMS<br> </div> <div>dependent. I guess my philosophy is that the humble driver should just<br> </div> <div>faithfully pass things back and forth while interfering as little as<br> </div> <div>possible.<br> </div> <div><br> </div> <div>On Fri, 7 Apr 2023 at 19:09, Mike Bayer <<a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>> wrote:<br> </div> <div>><br> </div> <div>> OK that's two "sure, let's absolve ourselves of this problem" responses :)<br> </div> <div>><br> </div> <div>> any opinion on executemany() being less useful if this requirement is not established, and/or encouraging DBAPI authors to at least *document* this themselves and maybe *prefer* maintaining ordering ?<br> </div> <div>><br> </div> <div>><br> </div> <div>><br> </div> <div>> On Fri, Apr 7, 2023, at 1:54 PM, Erlend Egeberg Aasland wrote:<br> </div> <div>><br> </div> <div>> On Fri, 7 Apr 2023 at 19:15, Mike Bayer <<a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a>> wrote:<br> </div> <div>><br> </div> <div>><br> </div> <div>> […] The scope here is, should pep-249 add some verbiage: "the order in which parameters are processed by executemany() should not be assumed to be in the order the parameters were given". […]<br> </div> <div>><br> </div> <div>><br> </div> <div>> Sounds good to me.<br> </div> <div>><br> </div> <div>> Erlend<br> </div> <div>><br> </div> <div>><br> </div> <div>><br> </div> <div>> _______________________________________________<br> </div> <div>> DB-SIG maillist - <a href="mailto:[email protected]" moz-do-not-send="true" class="moz-txt-link-freetext">[email protected]</a><br> </div> <div>> <a href="https://mail.python.org/mailman/listinfo/db-sig" moz-do-not-send="true" class="moz-txt-link-freetext">https://mail.python.org/mailman/listinfo/db-sig</a><br> </div> <div><br> </div> </blockquote> <div><br> </div> <br> <fieldset class="moz-mime-attachment-header"></fieldset> <pre class="moz-quote-pre" wrap="">_______________________________________________ DB-SIG maillist - <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a> <a class="moz-txt-link-freetext" href="https://mail.python.org/mailman/listinfo/db-sig">https://mail.python.org/mailman/listinfo/db-sig</a> </pre> </blockquote> <pre class="moz-signature" cols="72">-- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Experts (#1, Apr 10 2023) >>> Python Projects, Coaching and Support ... <a class="moz-txt-link-freetext" href="https://www.egenix.com/">https://www.egenix.com/</a> >>> Python Product Development ... <a class="moz-txt-link-freetext" href="https://consulting.egenix.com/">https://consulting.egenix.com/</a> ________________________________________________________________________ ::: We implement business ideas - efficiently in both time and costs ::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 <a class="moz-txt-link-freetext" href="https://www.egenix.com/company/contact/">https://www.egenix.com/company/contact/</a> <a class="moz-txt-link-freetext" href="https://www.malemburg.com/">https://www.malemburg.com/</a> </pre> </body> </html> --------------RtR8Wer0Bu57IrE27YO1YDXP-- --===============5848710787110831511== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ DB-SIG maillist - [email protected] https://mail.python.org/mailman/listinfo/db-sig --===============5848710787110831511==--