Is that limit specific to table scans?

Ssenojsitruc Is that limit specific to table scans?

It’s not, but run

EXPLAIN QUERY PLAN <your query>

EXPLAIN QUERY PLAN <your query>

I suspect the query planner is being smart. Would love to see the output.

senojsitrucOP•5/24/23, 12:46 PM

I did. The query plan changed from a table scan to using the index ("SEARCH user USING INDEX idx_user_key_asc (key>? AND key<?)"), and the 50 character limit went away.

MMatt Silverlock Sorry, missed documenting this. Limit is currently 50. What is the length you’re...

Matt Silverlock•5/24/23, 12:47 PM

Doc PR: https://github.com/cloudflare/cloudflare-docs/pull/9072

GitHub

d1: Update limits.md by elithrar · Pull Request #9072 · cloudflare/...

Table wrap breaks mobile entirely.

Ssenojsitruc I did. The query plan changed from a table scan to using the index ("SEARCH user...

Matt Silverlock•5/24/23, 12:50 PM

Intended - see “The LIKE Optimization” here: https://www.sqlite.org/optoverview.html (on mobile so can’t deep link)

senojsitrucOP•5/24/23, 12:53 PM

That's awesome. So far that LIKE/GLOB thing was the only limitation I had run into and adding the missing indexes fixed that. Now I just have to hope that CF Workers doesn't freak out at a bunch of concurrent connections from a single IP.

Thanks for your help here (and on Twitter).

Jason Hostetter•5/24/23, 4:22 PM

Suspected bug in the dashboard table viewer:
I have a virtual generated column like

type TEXT AS ('patient')

type TEXT AS ('patient')

in the dashboard, this column doesn't get a
```
type
```
```
type
```
column header, so the column headers all get shifted one to the left and the headers don't line up with the correct data column.

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

Screen_Shot_2023-05-24_at_12.22.51_PM.png

Matt Silverlock•5/24/23, 4:35 PM

New documentation we've just published:

Local development w/ D1 (using the new
```
wrangler 3.0
```
```
wrangler 3.0
```
) - https://developers.cloudflare.com/d1/learning/local-development/
Debugging D1 (just getting started here, but surfacing how to tail logs, capture errors, etc) - https://developers.cloudflare.com/d1/learning/debug-d1/
D1 is now in the Bindings documentation - https://developers.cloudflare.com/workers/wrangler/configuration/#d1-databases

JJason Hostetter Click to see attachment

Matt Silverlock•5/24/23, 8:07 PM

Thanks: we'll take a look.

Ggreg_Hello, I have a D1 DB that I'm trying to use for a Pages project. The bindings a...

Matt Silverlock•5/24/23, 8:36 PM

Related: d1-database

MMatt Silverlock Related: https://discord.com/channels/595317990191398933/992060581832032316/1085...

Matt Silverlock•5/24/23, 8:37 PM

(They had the inverse problem but see their notes about the app config)

AAnna | DevMiner Having D1 put it's error cause into `Error.cause` is super unfortunate because i...

James•5/24/23, 10:10 PM

I completely agree that the DX here isn't great. Nothing else really follows this pattern and it's not super intuitive yet.

Error.cause

Error.cause

is an awesome thing slowly surfacing within JS, but it feels a little out of place right now.
Filing an issue at https://github.com/cloudflare/workers-sdk might be the best place to further discuss this?

JJames I completely agree that the DX here isn't great. Nothing else really follows thi...

Matt Silverlock•5/24/23, 10:12 PM

We're already planning to address this, but have a couple of other higher priority items to address first. Hold tight.

James•5/24/23, 10:12 PM

Awesome to hear, ty for the update

Original message was deleted

Max (@rozenmd)•5/25/23, 2:55 PM

the fix for this should be rolled out tomorrow evening, thanks for reporting it

Sameer Ali•5/25/23, 6:54 PM

any rough ETA on storage limit increase?

SSameer Ali any rough ETA on storage limit increase?

DaniFoldi•5/25/23, 7:00 PM

I don't think there was a timeline shared, but I wouldn't be surprised if it was coming very soontm

44x4notfound Would those performance improvements be part of that?

Matt Silverlock•5/25/23, 10:01 PM

If you use the new backend (per the blog post) you can already get the performance improvements: https://blog.cloudflare.com/d1-turning-it-up-to-11/ (caveat: no backups on the new backend yet).

Existing non-new-backend databases will not get a storage increase. The original, prototype backend just doesn’t scale for it. Wasn’t the intent.

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

44x4notfound Can we migrate?

Matt Silverlock•5/25/23, 10:48 PM

Yes: per the blog, you can export (https://developers.cloudflare.com/d1/learning/backups/#downloading-a-backup-locally) and then import (https://developers.cloudflare.com/d1/learning/importing-data/#import-an-existing-database) - we’ll be publishing a single guide once we get to beta and make the new backend the default.

We don’t have near term plans for an automatic migration (such is the trade-off for an alpha).

Backups · Cloudflare D1 docs

D1 has built-in support for creating and restoring backups of your databases, including support for scheduled automatic backups and manual backup …

Importing data · Cloudflare D1 docs

D1 allows you to import existing SQLite tables and their data directly, enabling you to migrate existing data into D1 quickly and easily. This can be …

Cyb3r-Jak3•5/25/23, 11:59 PM

Anyone have tips for debugging

not authorized [code: 7500]

not authorized [code: 7500]

when trying to execute an sql file? I can manually run the commands on the same database and table using

--command

--command

but

--file

--file

always fails.

Ggeelen can you post your command/file somewhere? You can DM me if you don't want to sha...

Cyb3r-Jak3•5/26/23, 1:23 AM

If you break it down into smaller files it works

charge.sql4.83KB

Cyb3r-Jak3•5/26/23, 1:36 AM

I'm using wrangler 2 but using the experimental backend

Cyb3r-Jak3•5/26/23, 1:38 AM

Oh that worked

Cyb3r-Jak3•5/26/23, 1:43 AM

I was following a migration guide

Zzhendalf Is it possible to use Prisma with D1?

Max (@rozenmd)•5/26/23, 7:20 AM

not yet: https://github.com/prisma/prisma/issues/13310

senojsitrucOP•5/26/23, 2:32 PM

Does D1 specifically not support "LIMIT" on "DELETE" queries?

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

Produces the error:

near "LIMIT": syntax error

near "LIMIT": syntax error

Dropping the "LIMIT" part of the query allows it to execute successfully.

kian•5/26/23, 2:34 PM

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.

senojsitrucOP•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/COMMIT

BEGIN 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

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

That limit is

SQLITE_LIMIT_EXPR_DEPTH

SQLITE_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

Is that limit specific to table scans?

Similar Threads

Similar Threads

Similar Threads