3 ms·
For a long time sqlite did not have DROP COLUMN and RENAME COLUMN support, which are both pretty essential. I'm embarrassed to admit that I didn't realize RENA
by the_duke 4y ago
For a long time sqlite did not have DROP COLUMN and RENAME COLUMN support, which are both pretty essential.
I'm embarrassed to admit that I didn't realize RENAME COLUMN was actually added in 3.25, almost four years ago.
DROP COLUMN was only just added last year in 3.35.
I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column.
This changes things! But even now, ALTER TABLE is not transactional. So especially with many concurrent readers there can definitely be situations where you'd still want to rewrite.
- teraflop 4y agoI'm not sure what you mean by "not transactional". SQLite implements transaction support at the "page" level, and builds all other database operations on top of it, which means anything that touches the bytes of the database file is transaction-safe. You can verify this for yourself: sqlite> CREATE TABLE foo(a,b,c); sqlite> INSERT INTO foo VALUES (1,2,3); sqlite> BEGIN; sqlite> ALTER TABLE foo DROP COLUMN b; sqlite> SELECT * FROM foo; 1|3 sqlite> ROLLBACK; sqlite> SELECT * FROM foo; 1|2|3 It's of course still subject to SQLite's normal restrictions on locking, which means a long-running ALTER statement will block concurrent writers (and probably also concurrent readers if you're not running in WAL mode).
- prirun 4y ago> I'm surprised a database schema lasted 9/12 years without ever renaming or dropping a column. I did have a couple of columns that were no longer needed and would have dropped them, but instead I just set them to null and ignored them. Nulls only take 1 byte of space in a row. I dropped them when DROP COLUMN was added.