SQLite Forum

Do not auto reset after schema change
Login

Do not auto reset after schema change

(1) By anonymous on 2020-06-29 18:29:26 [link] [source]

I want to use the features of the sqlite3_prepare_v3() interface, but to have it auto reset and reprepare after a schema change only if it is the first step, and to not do that you have already stepped part way through the table since it has last been reset, to return a SQLITE_SCHEMA error in such a case. So, it is something in between the old and new behaviour.

(2) By Richard Hipp (drh) on 2020-06-29 18:40:27 in reply to 1 [link] [source]

Each run of a prepared statement is completely contained within a read transaction. So, in other words, the prepared statement sees the database for a single snapshot in time. That means that from the point of view of the prepared statement, the schema cannot change while the prepared statement is running.

In WAL mode, other connections can indeed change the schema while the original prepared statement is running. But those schema changes are done in a separate transaction. The original read transaction used by the prepared statement is unaffected by that schema change. The original prepared statement continues to see the schema as it existed prior to the changes in other transactions.

(3) By Larry Brasfield (LarryBrasfield) on 2020-06-29 18:41:31 in reply to 1 [link] [source]

I don't think the schema can change while a prepared query is being stepped. If it could, I think that would violate several promises made at the library interface. Further, and in support of those promises, whether made explicit therein or not, prepared statements execute within a transaction. Even if the schema were to change for other connections, it would not affect the results of a query or DML statement.

BTW, I read your "only if it is the first step" as "only if no stepping has begun".

(4) By Richard Damon (RichardDamon) on 2020-06-30 01:23:14 in reply to 3 [source]

Couldn't the program use the same connection/transaction to issue an ALTER TABLE command that invalidates the preperation of the statement?

(5) By Larry Brasfield (LarryBrasfield) on 2020-06-30 02:37:55 in reply to 4 [link] [source]

It appears (to me, at least) that the OP is willing for a schema change to cause repreparation when it occurs between the initial sqlite3_prepare_v3(...) and the beginning of execution (or "first step".) Once that begins, the only way for an ALTER TABLE statement to interact with that prepared (or reprepared) statement would be for it to be included within that same set of statements. Otherwise, the ALTER execution would occur in a different transaction, and thereby have no effect of the kind the OP wishes to avoid. It would be pathological for the OP (or anybody else) to worry about "chance" schema changes occurring as a result of executing the statement sequence they wrote and then interacting with other statements within the same set and transaction.

I think it is important to understand that the ACID guarantees are integral to the transaction mechanism. Within a transaction, there can be no inter-statement interference because, presumably, each statement's nominal effect is exactly what is intended. (So we call it "what was commanded" rather than "interference".) And the ACID guarantees include preclusion of inter-statement interference between statements that belong to different transactions.

The only time a repreparation can occur is after a (re)preparation and before an execution of the engine code resulting from the preparation. If the OP wants to avoid that, the bare sqlite3_prepare() is available, which will produce an error upon execution instead of rebuilding the engine code (which is the only recourse because the SQL is not kept with which to do the rebuild.)

(6.1) By Keith Medcalf (kmedcalf) on 2020-06-30 06:31:26 edited from 6.0 in reply to 4 [link] [source]

After a prepared statement has commenced execution, there is no change that can be made by alter table on that connection which can cause a change which would effect the correct execution of the query. What sort of ALTER TABLE do you think could be issued that would make the execution invalid and need the statement to be re-prepared during execution?

And statements which would "conflict" with the executing statement (such as an attempt to drop one of the tables or indexes being actively used) would get a locked error. Hopefully an ALTER TABLE ... DROP COLUMN ... would also result in the same locked error -- but you do not have to worry about that (yet) since the ability to use ALTER TABLE to drop a column does not exist.

So it is not possible for a statement to be "re-prepared" during execution (not to mention the obvious question of how the execution would be "re-stepped" to the same place so that it could "carry on" if that were possible.

So a re-prepare can only occur for a statement that is not yet executing (ie, on the first call to sqlite3_exec -- the one that causes the VDBE code to enter the executing state). A sqlite3_reset causes the execution to end, and a subsequent call to sqlite3_exec on the same statement is a "first call" to the statement and thus may re-prepare the statement if the schema has changed since it was last prepared.

All table / column names have already been resolved in the VDBE code, so even if you did change the "name" of a column, add a new "column", or change the name of a table, those changes would have no effect on the execution of the VDBE code nor the correctness of the result.

(7.1) By Keith Medcalf (kmedcalf) on 2020-06-30 06:33:15 edited from 7.0 in reply to 6.0 [link] [source]

The proof is in the pudding:

>python
Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:37:02) [MSC v.1924 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection()
>>> db.execute('create table x(x)')
<newapsw.Cursor object at 0x000001C993FA3340>
>>> db.execute('insert into x select value from wholenumber where value between 1 and 10');
<newapsw.Cursor object at 0x000001C996656100>
>>> for row in db.execute('select * from x'):
...  print(row)
...
Row(x=1)
Row(x=2)
Row(x=3)
Row(x=4)
Row(x=5)
Row(x=6)
Row(x=7)
Row(x=8)
Row(x=9)
Row(x=10)
>>> cr = db.execute('select * from x')
>>> next(cr)
Row(x=1)
>>> next(cr)
Row(x=2)
>>> db.execute('alter table x rename to y')
<newapsw.Cursor object at 0x000001C996656100>
>>> next(cr)
Row(x=3)
>>> cr2 = db.execute('select * from y')
>>> next(cr2)
Row(x=1)
>>> next(cr2)
Row(x=2)
>>> db.execute('alter table y add column z')
<newapsw.Cursor object at 0x000001C996656220>
>>> next(cr)
Row(x=4)
>>> next(cr2)
Row(x=3)
>>> cr3 = db.execute('select * from y')
>>> next(cr)
Row(x=5)
>>> next(cr2)
Row(x=4)
>>> next(cr3)
Row(x=1, z=None)
>>> next(cr3)
Row(x=2, z=None)
>>> db.execute('alter table y rename x to y')
<newapsw.Cursor object at 0x000001C996656280>
>>> next(cr)
Row(x=6)
>>> next(cr2)
Row(x=5)
>>> next(cr3)
Row(x=3, z=None)
>>> cr4 = db.execute('select * from y')
>>> next(cr4)
Row(y=1, z=None)
>>> next(cr)
Row(x=7)
>>>