Possible bug: pragma table_info(A) does not work, but equivalent select does in preupdate hook
(1) By Willem (sn0wbl1nd) on 2020-05-23 19:38:40
We use the preupdate hook to track changes made to the database. In the implementation, in the preupdate hook, we used (via the API): ``` PRAGMA table_info(tablename) ``` which after a call to step produces the result SQLITE_ROW if the table exists. We found that in a test script this worked the first time, but not the second. Replacing the PRAGMA statement with the following solved the issue: ``` SELECT * FROM pragma_table_info(tablename) ``` I have little insight into how or why one works and the other doesn't, or why this happens only the second time. Careful review of the code shows that previous statements are finalized when we get to this part of the code.
(2) By Simon Slavin (slavin) on 2020-05-23 23:12:07 in reply to 1 [link]
What type of SQL command are you using to call the hook ? <code>INSERT</code>, <code>UPDATE</code>, or <code>DELETE</code> ? After the preupdate hook calls your routine, does your routine correctly had control back to SQLite to allow it to continue ? Do you check the result code returned and make sure that the calling function returns <code>SQLITE_OK</code> ? If one SQL command updates many rows of a table, are you expecting your hook to be called once or many times ?
(3) By Willem (sn0wbl1nd) on 2020-05-24 17:16:34 in reply to 2 [link]
Thank you for the follow up! It is during pre-update - INSERT. This is the sequence of commands that produce the result, starting with an empty db file (the content is arbitrary and part of a test script): ``` CREATE TABLE dogs (tag INTEGER PRIMARY KEY, name VARCHAR, age INT, height REAL); INSERT INTO dogs (name, age, height) VALUES ("Scooby", 8, 0.8); INSERT INTO dogs (name, age, height) VALUES ("Scrappy", 6, 0.4); CREATE TABLE people (rowid INTEGER PRIMARY KEY, name VARCHAR, age INT, height REAL); ``` The described failure occurs on the second CREATE TABLE, performing `PRAGMA tableinfo(people)` in the preupdate hook. For the first CREATE it works fine. We open separate db handles for the client side (submitting the test script commands) and running the PRAGMA table_info; the preupdate hook is installed on the client handle. After the error and in my debugging I carefully went over all statements and made sure everything was finalized properly. The command completes normally. This is supported by the fact that replacing the sequence `PRAGMA tableinfo...` with `SELECT * FROM pragma_table_info...` it does work as expected. I did some refactoring yesterday and will try again with the PRAGMA version to see if something has changed.
(5) By TripeHound on 2020-05-24 22:09:00 in reply to 3 [link]
Are you using transactions in any way? Either explicitly, with `BEGIN` statements in your scripts, or perhaps created "automatically" by whatever layer your script uses to talk to SQLite? (I believe some Python wrappers are notorious for creating "unexpected" transactions). Not sure whether that would explain the difference between `PRAGMA tableinfo` and the `SELECT * FROM pragma_table_info`, but might be helpful in tracking down what's happening.
(6) By Willem (sn0wbl1nd) on 2020-05-24 23:01:05 in reply to 5 [link]
No, not as yet. And we use the C API directly. All this is submitted using the prepare/step functions. The test scripts are submitted through a test fixture we created. I can't help but think it is a quirk in the implementation because select works and gives exactly the same information. What is also telling is that the result code is SQLITE_DONE, not some kind of error, suggesting the API simply believes the table should be empty.
(7) By Simon Slavin (slavin) on 2020-05-25 01:56:27 in reply to 3 [link]
Just out of interest, can you try removing the <code>INSERT</code> commands ? Then try swapping the order of the <code>CREATE TABLE</code> commands. PS: As a side comment, SQLite has no <code>VARCHAR</code> type, use <code>TEXT</code> instead. And use single apostrophes to quote strings, not speech marks. But these should have no effect on the problem you reported.
(8) By Willem (sn0wbl1nd) on 2020-05-25 03:07:50 in reply to 7 [link]
The plot thickens. Swapping the order of create+insert does not matter, and results in the same error on the second table. But: creating both tables first, and then inserting data in either - that _does_ make a difference, and the aberrant behaviour is not present. Note that in our implementation we only respond to data being added (in preupdate), and if it is in a table/column we don't know about, we use `PRAGMA table_info()` to get the information. What this means is that it works if: - multiple tables are created - then first table, on insert: pragma table_info works - next table, on insert: pragma table_info works As opposed to previously: - create table A - on insert in A: pragma table_info works - create table B - on insert in B: pragma table_info does not work Yes - the types - I know. We are also testing how to represent and retrieve different types and track the declared type for our application. Thanks about the comment for the quotes!
(9) By Willem (sn0wbl1nd) on 2020-05-25 03:28:56 in reply to 7 [link]
Some more results - using shorthand. On each INSERT there is a call to `PRAGMA table_info() for that table: ``` CREATE A CREATE B CREATE C INSERT A INSERT B INSERT C ``` ** all good ** ``` CREATE A INSERT A CREATE B CREATE C INSERT B INSERT C ``` ** INSERT B and INSERT C fail ** ``` CREATE A CREATE B INSERT A CREATE C INSERT B INSERT C ``` ** all good ** ``` CREATE B INSERT B CREATE A INSERT A CREATE C INSERT C ``` ** INSERT A and INSERT C fail ** Basically, if a table is created, then an insert in that table (with call to `PRAGMA table_info`), the second and subsequent calls to `PRAGMA table_info()` fail. But... all these succeed if PRAGMA ... is replaced with `SELECT * FROM pragma_table_info()`.
(4) By Willem (sn0wbl1nd) on 2020-05-24 17:33:58 in reply to 2 [link]
I should add that the failure occurs on the first insert command _after_ the last create table statement. I retested it and the problem remains the same: works with select, fails with pragma. Putting a breakpoint in the code on where the result code returns (`SQLITE_DONE` where `SQLITE_ROW` is expected), and opening the file with sqlite3 shell, pragma table_info(people); works as expected. It is as if the route by which "people" is associated with the table data is subtly different depending on whether I use select or pragma.