Too many SQL variables error

[kevinlang]

While cleaning up the benchmarks in ecto_sqlite3, I hit this error due to the benchmarks setup code that used insert_all hitting this limit.

Couple options I can think of:

1. Simply list this as a limitation in the documentation
2. Have Exqlite detect and avoid this case via intelligent batching
3. Bump the limit via a compile time option that SQLite already exposes

FWIW you can see the code that hits this limit by looking at sqlite.c:101742

(3) seems like a reasonable choice. The trade off is mentioned in sqlite.c:

/*
** The maximum value of a ?nnn wildcard that the parser will accept.
** If the value exceeds 32767 then extra space is required for the Expr
** structure.  But otherwise, we believe that the number can be as large
** as a signed 32-bit integer can hold.
*/

[warmwaffles]

I don't know what the performance implications of bumping that limit up is. We should go look at sqlite3 in ruby to see what it does for active record.

I'd err towards the side of documentation versus bumping the limit.

[warmwaffles]

@kevinlang maybe we could figure out a way to support it just being left at default but also allowing someone who knows what they are doing to bump the limit up.

[thomastay]

Ran into this today. The limit is well documented in point 9 here: https://sqlite.org/limits.html, but exqlite seems to hit this error below the 32k variable limit somehow. There's also supposedly a way to reduce this limit at runtime, which might complicate how we might want to expose this.

Edit: ran into this issue while testing, so this isn't a big deal for me. Just wanted to chip in.

[warmwaffles]

@thomastay curious what were you doing to hit that limit? bulk inserting?

[thomastay]

yeah, this is for a small personal project of mine. The API that I'm using can generate a lot of nested data, which when normalized can result in inserting about 10k rows in a join table. Doing 10k insert statements is pretty slow, so I did an insert all and hit this issue. It's pretty easy to get around this by chunking the inserts by 999, just wanted to bring this up.

[warmwaffles]

Interesting. Think you can mock up a dummy integration test for us and we can get this to be configurable / able to better handle that situation?

[thomastay]

@warmwaffles sure, i'll work on it sometime this week if i get the chance. was out last week. Is it ok if i write the test in ecto_sqlite3, since my issue is really starting from there?