SQLite Forum

Enabling SQLITE_ENABLE_UPDATE_DELETE_LIMIT by default
Login

Enabling SQLITE_ENABLE_UPDATE_DELETE_LIMIT by default

(1) By anonymous on 2021-04-01 08:21:03 [link] [source]

Hello,

With SQLite 3.35 and the advent of RETURNING, I can transform a lot of code that I have that was using this pattern:

let rows = query("SELECT id FROM foo WHERE ...");
execute_many("DELETE FROM foo WHERE id = ?", rows);
do_something_else(rows);

into this:

let rows = query("DELETE FROM foo WHERE ... RETURNING id");
do_something_else(rows);

This is already a nice simplification, and more declaratively expresses my intent. However, some of the code that I want to perform this transformation on has a LIMIT clause on the SELECT statement (mostly to bound the amount of work that any one transaction can do). SQLite can support LIMIT on DELETE and UPDATE - which is great, as it really synergises with the new RETURNING feature! - but only if it's enabled when the amalgamation is generated, which is not the default, so if you, say, download the amalgamation from the website, LIMIT-on-DELETE is unavailable.

This is extra troublesome because some language bindings to SQLite come with their own bundled amalgamations and don't offer a way to swap in your own amalgamation with custom options. This isn't a show-stopper (usually), because there's also often an escape hatch to supply your own compiled SQLite libraries somehow, but it is still a chore and adds a lot of complexity and deviation from the simple, obvious path.

Further, I don't understand why SQLITE_ENABLE_UPDATE_DELETE_LIMIT exists as an option at all. Is there a downside to enabling it, besides the obvious of needing slightly more code? That's not mentioned in the documentation, and I wonder about why other SQL extensions (like, say, RETURNING) are always-on, without even a toggle to turn them off if code size is the most important thing.

Would it be possible to enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT by default in the distributed amalgamations, so that consumers get a fully-featured SQLite without having to think any further about it?

(2) By Larry Brasfield (larrybr) on 2021-04-01 15:12:55 in reply to 1 [link] [source]

... However, some of the code that I want to perform this transformation on has a LIMIT clause on the SELECT statement (mostly to bound the amount of work that any one transaction can do). SQLite can support LIMIT on DELETE and UPDATE - which is great, as it really synergises with the new RETURNING feature! - but only if it's enabled when the amalgamation is generated, which is not the default, so if you, say, download the amalgamation from the website, LIMIT-on-DELETE is unavailable.

It appears in the 3.35 releases. What amalgamation are you looking at?

Further, I don't understand why SQLITE_ENABLE_UPDATE_DELETE_LIMIT exists as an option at all. Is there a downside to enabling it, besides the obvious of needing slightly more code?

There are often execution time effects also, due to additional execution branches. But code reduction is enough. SQLite is still intended for very resource-limited machines.

Would it be possible to enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT by default in the distributed amalgamations, so that consumers get a fully-featured SQLite without having to think any further about it?

What about consumers who want to minimize code size? Do they need to add options to their builds whenever a new SQLite build option is added?

Maybe there should be a simple option, SQLITE_EVERYTHING, which enables every single option and adapts as the option set grows.

(3) By anonymous on 2021-04-01 15:54:13 in reply to 2 [link] [source]

It appears in the 3.35 releases. What amalgamation are you looking at?

I downloaded the 3.35.2 amalgamation tarball from the website a few days ago, built it following the path of least resistance, and found LIMIT-on-DELETE isn't enabled:

~/tmp/sqlite-autoconf-3350200$ ./sqlite3 
SQLite version 3.35.2 2021-03-17 19:07:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo (id);
sqlite> explain query plan delete from foo limit 1;
Error: near "limit": syntax error

There are often execution time effects also, due to additional execution branches. But code reduction is enough. SQLite is still intended for very resource-limited machines.

Sure. But my confusion remains: surely the same argument goes for RETURNING, or any of the many useful-but-not-necessarily-essential features, which aren't even optional, let alone disabled by default. Is LIMIT-on-DELETE exceptionally heavy? Or, put differently, if LIMIT-on-DELETE was enabled by default, would this be a convincing enough argument to turn it off, considering that code size-sensitive consumers can still manually disable it?

What about consumers who want to minimize code size? Do they need to add options to their builds whenever a new SQLite build option is added? Maybe there should be a simple option, SQLITE_EVERYTHING, which enables every single option and adapts as the option set grows.

I entirely recognise that allowing code size minimisation is a worthy goal. But I also think that the consumer having code size minimisation as the overriding consideration is an exceptional case, not the more common one; I think the vast majority of consumers are best served by the default options being more featureful instead of slimmer. Code size-sensitive consumers are more likely to be sophisticated and can deal with reading release notes, twiddling options and building the amalgamation themselves (it's not even hard, just a bit toilsome - most of the work is figuring out how to get other components to link against the custom SQLite). So I put forwards that, instead of a putative SQLITE_EVERYTHING being the option, enabling everything should be the default and a new SQLITE_NOTHING toggle should be the option.

(4) By Larry Brasfield (larrybr) on 2021-04-01 18:51:05 in reply to 3 [source]

It appears in the 3.35 releases. What amalgamation are you looking at?

I downloaded the 3.35.2 amalgamation tarball from the website a few days ago, built it following the path of least resistance, and found LIMIT-on-DELETE isn't enabled: [Proof omitted for brevity.]

There are at least two issues confounded here: (1) Whether the amalgamation "can support LIMIT on DELETE and UPDATE", as determined when it is built from sources; and (2) whether the SQLITE_ENABLE_UPDATE_DELETE_LIMIT preprocessor variable is #define'd by default within the amalgamation. As I have found while looking into these issues, there is a third: When the amalgamation is built without "#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT", and then compiled with that variable #define'd, the build succeeds and "pragma compile_options" indicates that the option is present, but it is not; it fails as you have shown (and I confirm.) That is clearly an improvement-worthy situation.

I am sympathetic to the contention that the amalgamation should be fully featured, and that its features should be easily enabled en masse. Whether the default should be one way or the other is harder to see. I think those who build the shell are quite used to selecting the options they see as important. And I do not discount the impact upon those "consumers" who wish to use a later version without much thought or trouble.

(5) By anonymous on 2021-04-02 00:12:59 in reply to 3 [link] [source]

I do not agree that enabling everything should be the default.

However, because SQLITE_ENABLE_UPDATE_DELETE_LIMIT must be known by the code generators, and because it seems about as generally useful as window functions and RETURNING clauses and upsert, it seems like maybe it should be enabled by default, unless that makes it too big and/or slow compared with excluding it. The other thing that maybe should be enabled by default is SQLITE_ENABLE_MATH_FUNCTIONS. I am unsure about if either of them really should be enabled by default, though.

It may also be useful to have another macro which enables additional stuff which may be useful in the command shell (both the official one and unofficial ones). These additional things to enable when this is used would be: SQLITE_ENABLE_BYTECODE_VTAB, SQLITE_ENABLE_DBPAGE_VTAB, SQLITE_ENABLE_DBSTAT_VTAB, SQLITE_ENABLE_EXPLAIN_COMMENTS, SQLITE_ENABLE_FTS5 (and possibly other FTS versions), SQLITE_ENABLE_IOTRACE, SQLITE_ENABLE_MATH_FUNCTIONS, SQLITE_ENABLE_JSON1, SQLITE_ENABLE_OFFSET_SQL_FUNC, SQILTE_ENABLE_RTREE, SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION, SQLITE_SOUNDEX, and SQLITE_DQS=0. These options shouldn't be enabled by default though; I am just suggesting adding a single option to enable all of them at once (still allowing enabling them individually too, if wanted), since they are useful for the use of the command shell and other programs like it (a few of these options are unlikely to be useful outside of such programs). There are additional options that are useful for the command shell, but those should probably be set separately (although they should be documented, so that you will know which options to use).

(6) By anonymous on 2021-04-02 07:11:06 in reply to 1 [link] [source]

Injecting some actual numbers: with default options, building 3.35.0 (because I have it lying around), --enable-update-limit adds 6032 bytes to libsqlite3.so, an increase of 0.15%, and 19768 bytes to libsqlite3.a, an increase of 0.21%.

These are not big numbers. -Os and --enable-update-limit actually produces smaller static libraries (but much bigger shared libraries, weirdly enough - I think GCC likes inlining with -Os, which might explain that?) than the default-options build; with more than ten seconds of effort I could probably chop a lot more off (e.g., the binaries aren't even stripped!). There are much, much bigger wins than LIMIT-on-DELETE being disabled.

This is why I suspect that consumers who very much care about code size are likely to be sophisticated and already twiddling options to shave bytes: the default options leave so much on the table - so having to twiddle more options isn't much of an imposition. In contrast, asking for the modal consumer to enable features at compile time really is quite an imposition, because they have to stray some distance from the happy path of using whatever general-purpose SQLite they have lying around (whether installed by the system or bundled together with language bindings) and trusting it'll just work so long as it's recent enough.