I am trying to rename an INDEX by updating sqlite_master with the new name and sql. This does not seem to work as I would expect it to. The documentation at https://www.sqlite.org/lang_altertable.html does not explicitly state that this should be possible, but I do think that this type of update meets the criterion as stated in the documentation:

However, a simpler and faster procedure can optionally be used for some changes that do no affect the on-disk content in any way.

I use the following script from sqlite3.exe (test_rename_index.db should not exist):

.open test_rename_index.db

CREATE TABLE test (Column1 INTEGER);
CREATE INDEX "test.Column1" ON test (Column1);

BEGIN TRANSACTION;
PRAGMA schema_version;      -- This should output 2
PRAGMA writable_schema=ON;

UPDATE sqlite_master SET name='test_renamed.Column1', sql='CREATE INDEX "test_renamed.Column1" ON "test" (Column1)' WHERE name='test.Column1';
PRAGMA schema_version=3;    -- Because this is a new database, 3 should be correct

PRAGMA writable_schema=OFF;
COMMIT;

PRAGMA index_list(test);

The last PRAGMA outputs:

0|test.Column1|0|c|0

So the internal data structures have not been updated with the new name. After closing sqlite3.exe and restarting it, execute the following commands:

.open test_rename_index.db
PRAGMA index_list(test);

The last PRAGMA now outputs:

0|test_renamed.Column1|0|c|0

So now the internal data structures have been updated with the new name. So my questions are the following:

• Are the described steps correctly following the procedure described in the documentation? I think they do.
• Can I deduce from the documentation that updating the name column of sqlite_master is not supported as the example only updates the sql column?
• Am I correct in believing that renaming an INDEX in the way I am trying to meets the criterion of not affecting any on-disk content in any way? So reopening a connection to the database does indeed maintain a schema that is not corrupt and perfectly usable?
• If so, I think that not all internal data structures are refreshed entirely when an update in schema_version is detected. Probably those that are indexed on the name of schema objects. Would it be possible, or even easy, to refresh those structures as well?
• If so, I would like to request that feature, because it would mean that renaming an index can be done instantaneously as well, instead of dropping and creating it again.

I wanted a pragma for updating the internal data structures, and for this reason. It would help both of us, I think.

I think you mean PRAGMA schema_version. I use this in the example and it does trigger sqlite_master to be reread, and this seems to work in cases where only the sql column is updated. It does not seem to work when the name column is updated.

I think

ANALYZE sqlite_master;

will do the trick.

-- 
Regards,
Kees Nuyt

I tested this, but it does not seem to work.

First, playing with PRAGMA writable_schema=ON is not recommended.

Second, why are you using a . in your index name?
Dot is already used as a separator between DB name
and schema name, or schema name and column name, etc... You do properly use double-quotes, but why?

I guess there's a reason there's not ALTER INDEX.

First, playing with PRAGMA writable_schema=ON is not recommended.

I know you have to be careful with this, but the documentation explicitly states that it can be used as a faster and simpler procedure to perform some ALTER ... statements. Just updating a row in sqlite_master seems much faster and simpler for renaming an index than dropping it and creating it again. I was hoping that would work, and in principle I think it can be made to work.

Second, why are you using a . in your index name? Dot is already used as a separator between DB name and schema name, or schema name and column name, etc... You do properly use double-quotes, but why?

I am sorry, this is left-over from the code that actually generates the statements and is careful to prefix index names with table names to prevent name collisions. I do not think it matters for the example though. I tested now with names without a dot and the behavior is the same.

First, playing with PRAGMA writable_schema=ON is not recommended.

Well, it is OK if you are careful, and have read the documentation about the file format. However, you may have to close and reopen the database connection for it to recognize the changes.

I have successfully used PRAGMA writable_schema=ON to delete the last column of a table; after changing the schema, issue a VACUUM command. You will get an error message there are too many data in the row, but then if you use VACUUM again a second time, it will work correctly.