ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite which isn’t currently en

ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite which isn’t currently enabled on D1 - https://github.com/cloudflare/workerd/pull/608

GitHub

d1: enable UPDATE_DELETE and STAT4 at build by elithrar · Pull Requ...

Enables two SQLite options:

The STAT4 query planner, which is enabled in the alpha: https://sqlite.org/draft/compile.html#enable_stat4

the "downside" is that the query planner becomes ...

Kkian ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite whic...

Matt Silverlock•5/26/23, 2:36 PM

To add: it’s complicated. It’s not part of the standard amalgamation. It’s not likely we’ll support this soon.

senojsitruc•5/26/23, 2:37 PM

I used it for sanity reasons, but it definitely isn't strictly necessary for me. Thanks for the info.

Ggeelen ah yeah the `.dump` statement includes transactions... we should automatically r...

Max (@rozenmd)•5/26/23, 2:57 PM

the next wrangler release will trim the first BEGIN TRANSACTION/COMMITBEGIN TRANSACTION/COMMIT it sees in an sqlite dump

Jason Hostetter•5/26/23, 3:31 PM

Getting D1 Error: Expression tree is too large (maximum depth 20)Expression tree is too large (maximum depth 20)

Jason Hostetter•5/26/23, 3:31 PM

Same query that was working on the old backend

Jason Hostetter•5/26/23, 3:32 PM

can post the query if needed, it's relatively complex (for me), but not super deeply nested. Though it's not totally clear to me what the expression tree depth is reflecting

kianOP•5/26/23, 4:06 PM

That limit is SQLITE_LIMIT_EXPR_DEPTHSQLITE_LIMIT_EXPR_DEPTH which is 20 on the experimental backend so you can probably find more information by searching for that

Jason Hostetter•5/26/23, 4:13 PM

Yeah all I could find is that it limits the expression depth to limit stack size when parsing the statement, but I can't find anything detailing how to figure out what your statement's depth is (am I at 21? 500?) or strategies to reduce it. The default sqlite depth limit seems to be 1000, are there any plans to increase going forward? I don't know if it's even possible to get under that limit with the type of query I need.

Unsmart•5/26/23, 4:15 PM

Limits are subject to change especially when going from alpha -> ga but I would assume that is there to reduce query complexity and thus keep performance reliable so I probably wouldnt bank on it increasing a lot but

Jason Hostetter•5/26/23, 4:26 PM

eeuuugh sounds like i'm gonna have to completely change my schema then, or else maybe do three separate queries instead of one with CTEs, which will really slow things down as i'll get 3x the network latency for db queries

JJason Hostetter Yeah all I could find is that it limits the expression depth to limit stack size...

Matt Silverlock•5/26/23, 5:28 PM

Do you get clarity on the sub-query depth from

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

Matt Silverlock•5/26/23, 5:30 PM

These limits aren't fixed in stone, but we need to be mindful about how and where we increase them. Understanding what practical applications are blocked on a lower limit -> helps me understand the trade-offs (vs. just blindly increasing to 1000)

Jason Hostetter•5/26/23, 5:58 PM

I’ll run that when I get a chance and report back

MMatt Silverlock Do you get clarity on the sub-query depth from `EXPLAIN QUERY PLAN` - ?

Jason Hostetter•5/26/23, 7:46 PM

Here is the output of

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

: https://pastebin.com/cXxQtVhe

Pastebin

3 0 0 MATERIALIZE subQuery16 3 0 SCAN im0 USING COVERING INDEX sqli...

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

Jason Hostetter•5/26/23, 7:47 PM

There are 30 rows, does that correspond 1:1 to expression depth?

Ian•5/26/23, 11:32 PM

How plausible is it to get one db per user ? currently limited to 10

Ian•5/26/23, 11:32 PM

Ian•5/26/23, 11:32 PM

or at least one table per user, but then there is the issue of db size limit

IIan How plausible is it to get one db per user ? currently limited to 10

Max (@rozenmd)•5/27/23, 6:14 AM

I reckon it's plausible, but tell @elithrar what you're building

PPrisal When you use wrangler migrate apply, with sql files, how does cloudflare workers...

Max (@rozenmd)•5/27/23, 6:15 AM

there's a migration table we add to your db to track it: https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/migrations/helpers.ts#L83

Original message was deleted

Matt Silverlock•5/27/23, 1:29 PM

The example here is in TS: https://developers.cloudflare.com/d1/platform/client-api/#errors

you can also use e?.cause?.messagee?.cause?.message

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

JJason Hostetter There are 30 rows, does that correspond 1:1 to expression depth?

Matt Silverlock•5/27/23, 1:30 PM

No, rows != depth. Depth correlates to sub-queries - if you have a sub-query that executes over every row, then it will correlate to depth. But that’s not a given.

MMax (@rozenmd)I reckon it's plausible, but tell @elithrar what you're building

Ian•5/27/23, 1:37 PM

I was thinking that every entity/user has their own "partition" or "account" and all the data and actions will be running against that partition, I reckon this is not SQL-like structure, was just curious if it was possible at all because of the good price for reads even at table sizes of 10M rows.

Original message was deleted

Max (@rozenmd)•5/27/23, 3:14 PM

problem is it's native, folks just don't know about it: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Error/cause

Error: cause - JavaScript | MDN

The cause data property of an Error instance indicates the specific original cause of the error.

kianOP•5/27/23, 3:16 PM

fwiw it exists in TS's native types too

kianOP•5/27/23, 3:18 PM

notably, it's a ES2022 thing (which is the target you should use for Workers)

kianOP•5/27/23, 3:18 PM

https://github.com/microsoft/TypeScript/commit/363e3a78f4def6084cc7371c8b1104485a7fe5f8

Lettucebowler•5/27/23, 5:22 PM

just switched to the experimental backend and am seeing huge performance gains. Love it, thanks for the hard work and I can't wait to see d1 get even better!

Ashley•5/27/23, 6:05 PM

Simple Q: is there a way to get the row(s) written to the DB when executing an INSERT with run()? (or a way to insert data and get the response without running a secondary query)

Original message was deleted

Ashley•5/27/23, 6:38 PM

Nice, thank you - still two queries, but at least better than doing two sequentialy in the code