cannot store BLOB value in TEXT column

[anacrolix]

Runtime error: while compressing chunk for key a

Caused by:
    0: cannot store BLOB value in TEXT column _info_file_zstd.path
    1: Error code 19: Abort due to constraint violation

I got this I think because my original table that is now transparently compressed was strict, and had text set on the field. I guess this is most likely just an oversight given strict is relatively new. I was able to proceed by writing over the schema (as there's no alter table to do this), sqlite doesn't actually store anything on disk for strict. I didn't take any chances in case strict would cause implicit conversions if I made it blob or anything else and just removed text and strict from the table.

[phiresky]

This is a good observation. I'm a fan of the STRICT option too, but I didn't notice it caused an issue here.

Abusing the text field to store blobs is kind of a hack in any case, but I think there's no way around this without rewriting the table fully during the zstd_enable_transparent() step, which I kind of want to avoid.

I think the best we could do right now is to detect strict tables and reject them beforehand.

strict would cause implicit conversions

Yes. From what I know strict does change the behaviour of implicit conversions. Also the current behaviour of sqlite-zstd means that it looks at the schema at conversion time to determine whether the decompressed data should be shown as a BLOB or TEXT, so setting the column type to BLOB would lead to incorrect results.

[anacrolix]

Thanks for the clarification. What about switching the column in the rewritten table to be of type "any", no matter what. That should retain the existing behaviour regardless of whether strict is set or not?

[phiresky]

switching the column in the rewritten table to be of type "any",

That would probably work. Then both uncompressed text and compressed blobs could be in the column.

But SQLite still doesn't have the feature to change a column type without rewriting the whole table so it's not possible (I think) to do this during the enable step without a large perf hit.