Open simonw opened 11 months ago
More notes in here:
Not all Python/SQLite installations exhibit this problem by default!
It turns out this is controlled by the legacy_alter_table
pragma: https://sqlite.org/pragma.html#pragma_legacy_alter_table
If that PRAGMA is turned on (default in newer SQLites) then alter table
will error if you try to rename a table that is referenced in a view.
Here's a one-liner to test if it is on or not:
python -c 'import sqlite3; print(sqlite3.connect(":memory:").execute("PRAGMA legacy_alter_table").fetchall())'
Options:
recreate_views: bool
parameter to table.transform()
controlling if views that might reference this table are stashed and dropped and recreated within a transaction as part of the operation. But should that be True
or False
by default?PRAGMA
and automatically do that view workaround if it's turned onPRAGMA
off for the duration of the .transform()
operation and on again at the end. Does it only affect the current connection?transform()
in a transaction, detect the "error in view"
, "no such table"
error, if spotted then do the VIEW workaround and try againI'm on the fence as to which of these I like the most. I'm tempted to go with the one which just drops VIEWS and recreates them all the time, because it feels simpler.
I shipped the view recreating fix in datasette-edit-schema
, so at least I can start exercising that fix and see if it has any weird issues.
Just noting that the fix @simonw mentions is here in datasette-edit-schema.
I'm using views that reference tables that are changing, so I'm likely to want this fix.
The documentation for the legacy_alter_table
pragma states that the behavior it controls is per-connection, and not persistent.
Regardless, using the pragma seems a bad idea to me, because:
The recreate_views
idea seems like it might be useful, but I reckon You Ain't Gonna Need It. If someone has a lot of views, and it takes ages to recreate them, perhaps. That seems highly unlikely.
I got this error trying to drop a column from a table that was part of a SQL view:
Upon further investigation I found that this pattern seemed to fix it:
So grab a copy of any view that might reference this table, start a transaction, drop those views, run the transform, recreate the views again.
Originally posted by @simonw in https://github.com/simonw/datasette-edit-schema/issues/35#issuecomment-1683370548