VT table behavior change between 3.10 and 3.17

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

VT table behavior change between 3.10 and 3.17

Bob Friesenhahn
We are trying to update from sqlite3 3.10 to 3.17.  Our virtual table
modules are encountering problems with 3.17 since the 'xOpen' callback
is now being called for value change and row deletion operations.
Previously it was only being called for read-only queries.

We are using reader/writer locks and there is not a convenient way to
transition from being a reader to being a writer.  A file is opened by
the 'xOpen' callback and we need to know if the file should be opened
read only, or read/write.

The change in behavior can only work with virtual table modules which
are able to smoothly transition between the state established by
'xOpen' to the 'xUpdate' call or know the intent when 'xOpen' is
called.  This did not seem to be a requirement before.

In what sqlite3 version did this behavior change?

Is there a way to know when the 'xOpen' callback is called if it is to
support an update transaction (i.e. 'xUpdate' callback will be
called)?

Thanks,

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
Can you provide an example of the bytecode produced?

In the SQLite shell type:

.explain
explain <query>;

Typical output (with SQLite version 3.7.14.1):

asql> .explain
asql> explain update mytable set myfield=2 where myconst=7;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00  NULL
1     Goto           0     73    0                    00  NULL

2     OpenEphemeral  1     26    0                    08  NULL

3     VOpen          0     0     0     vtab:1F77F958:2B834C233990  00  NULL

4     Integer        1     1     0                    00  NULL
5     Integer        0     2     0                    00  NULL
6     VFilter        0     40    1                    00  NULL

7     VColumn        0     1     4                    00  sgb_dd_play_mode_text.game_no
8     Integer        7     5     0                    00  NULL
9     Ne             5     39    4     collseq(BINARY)  6b  NULL
10    Rowid          0     6     0                    00  NULL
... retrieve unchanged fields
17    Integer        2     13    0                    00  NULL
...retrieve unchanged fields
36    MakeRecord     6     26    4                    00  NULL
37    NewRowid       1     5     0                    00  NULL
38    Insert         1     4     5                    08  NULL

39    VNext          0     7     0                    00  NULL

40    Close          0     0     0                    00  NULL
41    Rewind         1     71    0                    00  NULL

...retrieve stored rows

69    VUpdate        0     27    32    vtab:1F77F958:2B834C233990  02  NULL
70    Next           1     42    0                    00  NULL
71    Close          1     0     0                    00  NULL

72    Halt           0     0     0                    00  NULL

73    VBegin         0     0     0     vtab:1F77F958:2B834C233990  00  NULL
74    Goto           0     2     0                    00  NULL


Explanation:

- xBestIndex is called while preparing the statement

- (73) VBegin is called to signal the start of a transaction
- (3) VOpen is called to open the table for reading (typically store a read-only file handle in your VT cursor structure)
- (4..6) VFilter is called to establish the rowset fort he cursor (typically rewind your data file and retrieve the first record)
- (7..9) WHERE condition is checked
- (10..36) The new records contents is constructed from the VColumn return values and the results of the expressions in the SET clause
- (37..38) The new record is stored in an ephemeral table
- (39) VNext is called (typically retrieve the next record)
- (40) VClose is called (typically close the read only file handle in your VT cursor structure)
- (42..68) The new record is retreived from the ephemeral table
- (69) VUpdate is called (typically open a read-write file handle and store it in your VT table structure)
- (70) retrieve next row for update
- (71) Close the ephemeral table (and automatically drop it)
-(72) End program, calling VCommit to signal the end of the transaction (typically flush and close the read-write file handle in the table structure)

Note that opening a read-write file handle is done on the first VUpdate instead of VBegin because there may be no rows that require updating.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bob Friesenhahn
Gesendet: Dienstag, 28. März 2017 21:49
An: SQLite mailing list <[hidden email]>
Betreff: [sqlite] VT table behavior change between 3.10 and 3.17

We are trying to update from sqlite3 3.10 to 3.17.  Our virtual table modules are encountering problems with 3.17 since the 'xOpen' callback is now being called for value change and row deletion operations.
Previously it was only being called for read-only queries.

We are using reader/writer locks and there is not a convenient way to transition from being a reader to being a writer.  A file is opened by the 'xOpen' callback and we need to know if the file should be opened read only, or read/write.

The change in behavior can only work with virtual table modules which are able to smoothly transition between the state established by 'xOpen' to the 'xUpdate' call or know the intent when 'xOpen' is called.  This did not seem to be a requirement before.

In what sqlite3 version did this behavior change?

Is there a way to know when the 'xOpen' callback is called if it is to support an update transaction (i.e. 'xUpdate' callback will be called)?

Thanks,

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Dan Kennedy-4
In reply to this post by Bob Friesenhahn
On 03/29/2017 02:48 AM, Bob Friesenhahn wrote:
> We are trying to update from sqlite3 3.10 to 3.17. Our virtual table
> modules are encountering problems with 3.17 since the 'xOpen' callback
> is now being called for value change and row deletion operations.
> Previously it was only being called for read-only queries.

Something else is going on I think. There has never been a version of
SQLite that could do an UPDATE or DELETE on a virtual table without
invoking xOpen() to create a cursor. It needs the cursor to determine
which rows are matched by the WHERE clause.

>
> We are using reader/writer locks and there is not a convenient way to
> transition from being a reader to being a writer.  A file is opened by
> the 'xOpen' callback and we need to know if the file should be opened
> read only, or read/write.
>
> The change in behavior can only work with virtual table modules which
> are able to smoothly transition between the state established by
> 'xOpen' to the 'xUpdate' call or know the intent when 'xOpen' is
> called.  This did not seem to be a requirement before.
>
> In what sqlite3 version did this behavior change?
>
> Is there a way to know when the 'xOpen' callback is called if it is to
> support an update transaction (i.e. 'xUpdate' callback will be called)?

When a write-transaction is opened on a virtual table the xBegin method
is called. xCommit() or xRollback() to end the transaction. Any xOpen()
that is part of an UPDATE or DELETE operation will occur within a
transaction, and any xOpen() outside of a transaction must be a
read-only query. But within an open transaction there is no way to tell
whether a specific xOpen() call is part of a read-only or read-write
statement.

Dan.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
According to the documentation of Virtual Tables and Opcodes:

xBegin( table ) is called to announce that SQlite intends to write to the table. There is no cursor involved. Do whatever is necessary to write to the backing store and set any VT implementation specific information in the table structure (e.g. a file handle enabled for writing)

xUpdate( table, rowid[, values] ) is called to perform a change (write to) the VT. Insert, delete or rewrite the record, or at least save whatever is required to actually perform the changes when xCommit is called.

xSync( table ) is called on ALL virtual tables involved in a transaction that is READY to commit. If anything is known that would cause a commit to fail, tell SQLite about that NOW.

xCommit( table ) resp. xRollback( table ) is called to instruct the VT to commit resp. rollback all the changes (made via xUpdate) since the last call to xBegin.


xOpen( table, cursor) is called to announce that SQLite intends to read from a table. A cursor cannot be used to write to a table. Do whatever is necessary to read from the backing store and set any VT implementation specific fields in the cursor structure (e.g. a file handle enabled for reading)

xFilter( cursor ) is called to set the cursor to the first row of data. If ths VT is in an inner loop (RH side, after reordering by the quera planner) of a join, xFilter may be called multiple times for the same cursor.

xRowid( cursor ), xColumn( cursor, field number ) return the unique(!!) rowid or the contents of the specfiied field of the current record

xNext( cursor ) advance to the next row

xEof( cursor ) query if there is a "current row"

xClose( cursor ) is called when SQLite no longer requires the cursor. NOTE: If you are using posix advisory locks in your VT implementation, it would be wise to postpone closing the file handle until the end of the transaction. Closing ANY file handle on a certain file in ANY thread will cause ALL the locks on that file held by ANY thread in the whole process to be released.


To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is:

xBegin()  - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()... - xSync() - xCommit()



-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dan Kennedy
Gesendet: Mittwoch, 29. März 2017 10:03
An: [hidden email]
Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

On 03/29/2017 02:48 AM, Bob Friesenhahn wrote:
> We are trying to update from sqlite3 3.10 to 3.17. Our virtual table
> modules are encountering problems with 3.17 since the 'xOpen' callback
> is now being called for value change and row deletion operations.
> Previously it was only being called for read-only queries.

Something else is going on I think. There has never been a version of SQLite that could do an UPDATE or DELETE on a virtual table without invoking xOpen() to create a cursor. It needs the cursor to determine which rows are matched by the WHERE clause.

>
> We are using reader/writer locks and there is not a convenient way to
> transition from being a reader to being a writer.  A file is opened by
> the 'xOpen' callback and we need to know if the file should be opened
> read only, or read/write.
>
> The change in behavior can only work with virtual table modules which
> are able to smoothly transition between the state established by
> 'xOpen' to the 'xUpdate' call or know the intent when 'xOpen' is
> called.  This did not seem to be a requirement before.
>
> In what sqlite3 version did this behavior change?
>
> Is there a way to know when the 'xOpen' callback is called if it is to
> support an update transaction (i.e. 'xUpdate' callback will be called)?

When a write-transaction is opened on a virtual table the xBegin method is called. xCommit() or xRollback() to end the transaction. Any xOpen() that is part of an UPDATE or DELETE operation will occur within a transaction, and any xOpen() outside of a transaction must be a read-only query. But within an open transaction there is no way to tell whether a specific xOpen() call is part of a read-only or read-write statement.

Dan.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Cezary H. Noweta
Hello,

On 2017-03-29 10:48, Hick Gunter wrote:
> According to the documentation of Virtual Tables and Opcodes:
> [...]
> xOpen( table, cursor) is called to announce that SQLite intends to
> read from a table. A cursor cannot be used to write to a table. Do
> whatever is necessary to read from the backing store and set any VT
> implementation specific fields in the cursor structure (e.g. a file
> handle enabled for reading)

Where have you taken above fragment from? The doc says: ``The xOpen
method creates a new cursor used for accessing (read and/or writing) a
virtual table.'' -- at least from 3.8.5 until now. It is not the best to
rely on the assumption that xOpen-ed cursor is read-only, or have I
missed something? Why xOpen-ed cursor cannot be used to write to a table?

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cezary H. Noweta
Gesendet: Mittwoch, 29. März 2017 12:37
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello,

On 2017-03-29 10:48, Hick Gunter wrote:
> According to the documentation of Virtual Tables and Opcodes:
> [...]
> xOpen( table, cursor) is called to announce that SQLite intends to
> read from a table. A cursor cannot be used to write to a table. Do
> whatever is necessary to read from the backing store and set any VT
> implementation specific fields in the cursor structure (e.g. a file
> handle enabled for reading)

Where have you taken above fragment from? The doc says: ``The xOpen method creates a new cursor used for accessing (read and/or writing) a virtual table.'' -- at least from 3.8.5 until now. It is not the best to rely on the assumption that xOpen-ed cursor is read-only, or have I missed something? Why xOpen-ed cursor cannot be used to write to a table?

-- best regards

Cezary H. Noweta

The xUpdate method alone is used to perform updates to virtual tables. It does not take a cursor argument.

Also, there is no method taking a cursor argument that allows data to be changed.

The documentation is correct in that UPDATE and DELETE queries involve reading from the table first, and xOpen will be called to do that. Pure INSERT queries don't even call xOpen.


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Cezary H. Noweta
Hello,

On 2017-03-29 13:07, Hick Gunter wrote:
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cezary H. Noweta
> Gesendet: Mittwoch, 29. März 2017 12:37
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

> [...] Why xOpen-ed cursor cannot be used to write to a table?

> The xUpdate method alone is used to perform updates to virtual
> tables. It does not take a cursor argument.

> Also, there is no method taking a cursor argument that allows data to be changed.

> The documentation is correct in that UPDATE and DELETE queries
> involve reading from the table first, and xOpen will be called to do
> that. Pure INSERT queries don't even call xOpen.

Indeed, the sole place, where VOpen is used, is a processing of WHERE
clauses (UPDATE, DELETE have a WHERE clause, while INSERT, REPLACE have
not).

My questions concerned a connection between your first sentence
(``According to the documentation...'') and the remaining part of your
post. Now, I see that you have explained the behavior of SQLite rather
then quoted the documentation. I'm sorry for OT disturbance.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Dominique Devienne
On Wed, Mar 29, 2017 at 2:33 PM, Cezary H. Noweta <[hidden email]>
wrote:

> My questions concerned a connection between your first sentence
> (``According to the documentation...'') and the remaining part of your post.

Now, I see that you have explained the behavior of SQLite rather then
> quoted the documentation. I'm sorry for OT disturbance.


That was a totally valid question IMHO, and not really OT.

And Gunter's "doc" belongs in the official doc IMHO, probably not as-is,
but the gist of it at least.

Thank you again Gunter for these explanations on vtables. Truly useful. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
In reply to this post by Cezary H. Noweta

> [...] Why xOpen-ed cursor cannot be used to write to a table?

> The xUpdate method alone is used to perform updates to virtual tables.
> It does not take a cursor argument.

> Also, there is no method taking a cursor argument that allows data to be changed.

> The documentation is correct in that UPDATE and DELETE queries involve
> reading from the table first, and xOpen will be called to do that.
> Pure INSERT queries don't even call xOpen.

Indeed, the sole place, where VOpen is used, is a processing of WHERE clauses (UPDATE, DELETE have a WHERE clause, while INSERT, REPLACE have not).

My questions concerned a connection between your first sentence (``According to the documentation...'') and the remaining part of your post. Now, I see that you have explained the behavior of SQLite rather then quoted the documentation. I'm sorry for OT disturbance.

-- best regards

Cezary H. Noweta

-----

No problem, take it as a digest of the documentation enhanced by the experience of writing several VT implementations. Most of which, BTW, don't care about transactions but apply changes immediately, using xCommit/xRollback only as a hint that the backing store may now be "closed" if applicable (e.g. file-based VT). This is because we use SQLite mainly as a query tool and data store integrity is maintained by the application itself. Catastrophic failures are repaired via restore of a checkpoint and rollforward of the transaction log.


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Bob Friesenhahn
In reply to this post by Hick Gunter
On Wed, 29 Mar 2017, Hick Gunter wrote:
>
> To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is:
>
> xBegin()  - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()... - xSync() - xCommit()

If this is supposed to be the case, then it seems that xClose() is not
being called before xUpdate() with 3.17.

The problematic virtual table module seems to be improperly
constructed and was implemented based on observed behavior at the
time.  There is inadequate tracing in the code so proper tracing will
be added when it is re-written.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
Can you support this notion with the byte code program of the affected statement?

In the sqlite shell type

.explain
explain <your query>

and post the output or send it to me off list.

NOTE: I am not an SQLite developer, but will need to upgrade sometime later, so getting ahead of possible changes would be helpful.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 29. März 2017 15:34
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

On Wed, 29 Mar 2017, Hick Gunter wrote:
>
> To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is:
>
> xBegin()  - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()...
> - xSync() - xCommit()

If this is supposed to be the case, then it seems that xClose() is not being called before xUpdate() with 3.17.

The problematic virtual table module seems to be improperly constructed and was implemented based on observed behavior at the time.  There is inadequate tracing in the code so proper tracing will be added when it is re-written.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Dan Kennedy-4
On 03/29/2017 08:41 PM, Hick Gunter wrote:

> Can you support this notion with the byte code program of the affected statement?
>
> In the sqlite shell type
>
> .explain
> explain <your query>
>
> and post the output or send it to me off list.
>
> NOTE: I am not an SQLite developer, but will need to upgrade sometime later, so getting ahead of possible changes would be helpful.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bob Friesenhahn
> Gesendet: Mittwoch, 29. März 2017 15:34
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17
>
> On Wed, 29 Mar 2017, Hick Gunter wrote:
>> To avoid anomalies when changing "key fields", SQLite will scan through the whole cursor first, saving the rowids and new contents of the record(s) satisfying the WHERE clause. It will then close the cursor and call xUpdate for the affected records. The main sequence is:
>>
>> xBegin()  - xOpen() - xFilter() - xNext()... - xClose() - xUpdate()...
>> - xSync() - xCommit()
> If this is supposed to be the case, then it seems that xClose() is not being called before xUpdate() with 3.17.

Consider the order of the xClose() and xUpdate() calls in the above to
be undefined. I suspect it changed for *some* UPDATE statements as part
of the single-pass-UPDATE optimization that went into 3.17.0.

Dan.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Bob Friesenhahn
In reply to this post by Hick Gunter
On Wed, 29 Mar 2017, Hick Gunter wrote:

> Can you provide an example of the bytecode produced?

sqlite> .explain
sqlite> explain delete from device_cfgrecord where name == 'bar';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --
-------------
0     Init           0     15    0                    00
1     Null           0     1     0                    00
2     VOpen          0     0     0     vtab:CB1700    00
3     Integer        0     2     0                    00
4     Integer        0     3     0                    00
5     VFilter        0     11    2                    00
6       VColumn        0     1     4                    00
7       Ne             5     10    4     (BINARY)       52
8       Rowid          0     6     0                    00
9       RowSetAdd      1     6     0                    00
10    VNext          0     6     0                    00
11      RowSetRead     1     14    6                    00
12      VUpdate        0     1     6     vtab:CB1700    02
13    Goto           0     11    0                    00
14    Halt           0     0     0                    00
15    Transaction    0     1     724   0              01
16    VBegin         0     0     0     vtab:CB1700    00
17    String8        0     5     0     bar            00
18    Goto           0     1     0                    00

sqlite> explain update device_cfgrecord set value = 'gumby' where name
== 'bar';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --
-------------
0     Init           0     27    0                    00
1     OpenEphemeral  1     5     0                    00
2     VOpen          0     0     0     vtab:CB1700    00
3     Integer        0     8     0                    00
4     Integer        0     9     0                    00
5     VFilter        0     17    8                    00
6       VColumn        0     1     10                   00
7       Ne             11    16    10    (BINARY)       52
8       Rowid          0     1     0                    00
9       Rowid          0     2     0                    00
10      VColumn        0     0     3                    00
11      VColumn        0     1     4                    00
12      String8        0     5     0     gumby          00
13      MakeRecord     1     5     6                    00
14      NewRowid       1     7     0                    00
15      Insert         1     6     7                    00
16    VNext          0     6     0                    00
17    Rewind         1     25    0                    00
18      Column         1     0     1                    00
19      Column         1     1     2                    00
20      Column         1     2     3                    00
21      Column         1     3     4                    00
22      Column         1     4     5                    00
23      VUpdate        0     5     1     vtab:CB1700    02
24    Next           1     18    0                    00
25    Close          1     0     0                    00
26    Halt           0     0     0                    00
27    Transaction    0     1     724   0              01
28    VBegin         0     0     0     vtab:CB1700    00
29    String8        0     11    0     bar            00
30    Goto           0     1     0                    00

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
Seems like the Close (0) Opcode (expected at line 10.5 and 16.5 respectively) is suspiciously missing from the generated program. I guess this will be handled in Halt (where it was probably checked anyway all along). But the VUpdate opcode is still outside the VNext loop.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 29. März 2017 17:05
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

On Wed, 29 Mar 2017, Hick Gunter wrote:

> Can you provide an example of the bytecode produced?

sqlite> .explain
sqlite> explain delete from device_cfgrecord where name == 'bar';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --
-------------
0     Init           0     15    0                    00
1     Null           0     1     0                    00
2     VOpen          0     0     0     vtab:CB1700    00
3     Integer        0     2     0                    00
4     Integer        0     3     0                    00
5     VFilter        0     11    2                    00
6       VColumn        0     1     4                    00
7       Ne             5     10    4     (BINARY)       52
8       Rowid          0     6     0                    00
9       RowSetAdd      1     6     0                    00
10    VNext          0     6     0                    00
11      RowSetRead     1     14    6                    00
12      VUpdate        0     1     6     vtab:CB1700    02
13    Goto           0     11    0                    00
14    Halt           0     0     0                    00
15    Transaction    0     1     724   0              01
16    VBegin         0     0     0     vtab:CB1700    00
17    String8        0     5     0     bar            00
18    Goto           0     1     0                    00

sqlite> explain update device_cfgrecord set value = 'gumby' where name
== 'bar';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --
-------------
0     Init           0     27    0                    00
1     OpenEphemeral  1     5     0                    00
2     VOpen          0     0     0     vtab:CB1700    00
3     Integer        0     8     0                    00
4     Integer        0     9     0                    00
5     VFilter        0     17    8                    00
6       VColumn        0     1     10                   00
7       Ne             11    16    10    (BINARY)       52
8       Rowid          0     1     0                    00
9       Rowid          0     2     0                    00
10      VColumn        0     0     3                    00
11      VColumn        0     1     4                    00
12      String8        0     5     0     gumby          00
13      MakeRecord     1     5     6                    00
14      NewRowid       1     7     0                    00
15      Insert         1     6     7                    00
16    VNext          0     6     0                    00
17    Rewind         1     25    0                    00
18      Column         1     0     1                    00
19      Column         1     1     2                    00
20      Column         1     2     3                    00
21      Column         1     3     4                    00
22      Column         1     4     5                    00
23      VUpdate        0     5     1     vtab:CB1700    02
24    Next           1     18    0                    00
25    Close          1     0     0                    00
26    Halt           0     0     0                    00
27    Transaction    0     1     724   0              01
28    VBegin         0     0     0     vtab:CB1700    00
29    String8        0     11    0     bar            00
30    Goto           0     1     0                    00

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Cezary H. Noweta
In reply to this post by Bob Friesenhahn
Hello,

On 2017-03-29 15:34, Bob Friesenhahn wrote:
> If this is supposed to be the case, then it seems that xClose() is not
> being called before xUpdate() with 3.17.

BTW. Why (while in UPDATE) there is still emitted Close just before
Halt? Halt closes opened cursors at the very beginning. Maybe Close
should be omitted as in SELECT/DELETE?

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Hick Gunter
The Close (1) in the second program is closing (=deleting) the ephemeral table. I have no idea if this would also be handled in Halt or not.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Cezary H. Noweta
Gesendet: Mittwoch, 29. März 2017 17:45
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] VT table behavior change between 3.10 and 3.17

Hello,

On 2017-03-29 15:34, Bob Friesenhahn wrote:
> If this is supposed to be the case, then it seems that xClose() is not
> being called before xUpdate() with 3.17.

BTW. Why (while in UPDATE) there is still emitted Close just before Halt? Halt closes opened cursors at the very beginning. Maybe Close should be omitted as in SELECT/DELETE?

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Richard Hipp-3
On 3/29/17, Hick Gunter <[hidden email]> wrote:
> The Close (1) in the second program is closing (=deleting) the ephemeral
> table. I have no idea if this would also be handled in Halt or not.

OP_Halt always closes all open cursors.  Calling sqlite3_reset() does
too.  In fact, it's the same block of code that does both.

One could omit all OP_Close opcodes and I think everything would still
work.  But sometimes an OP_Close can free up resources sooner rather
than later.  Also, some b-tree operations are faster if there is only
a single open cursor on that b-tree, and so it is advantageous to keep
the number of open cursors to a minimum.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Cezary H. Noweta
Hello,

On 2017-03-29 18:00, Richard Hipp wrote:
> One could omit all OP_Close opcodes and I think everything would still
> work.  But sometimes an OP_Close can free up resources sooner rather
> than later.  Also, some b-tree operations are faster if there is only
> a single open cursor on that b-tree, and so it is advantageous to keep
> the number of open cursors to a minimum.

OP_Close with virtual tables:

+---------+-------------+---------------+---------------+
| VERSION | SELECT      | DELETE        | UPDATE        |
+---------+-------------+---------------+---------------+
| 3.10    | AT THE END  | IN THE MIDDLE | IN THE MIDDLE |
+---------+-------------+---------------+---------------+
| 3.17    | DISAPPEARED | DISAPPEARED   | AT THE END    |
+---------+-------------+---------------+---------------+

``AT THE END'' means just before OP_Halt. Update of virtual tables is
treated separately so it could be easy adjusted for consistency.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Cezary H. Noweta
In reply to this post by Richard Hipp-3
Hello,

On 2017-03-29 18:00, Richard Hipp wrote:
> One could omit all OP_Close opcodes and I think everything would still
> work.  But sometimes an OP_Close can free up resources sooner rather
> than later.  Also, some b-tree operations are faster if there is only
> a single open cursor on that b-tree, and so it is advantageous to keep
> the number of open cursors to a minimum.

I've just found ``Check-in [32be7aae]''
(http://www.sqlite.org/cgi/src/info/32be7aae92ee48bf):

Date: 2016-11-22 01:26:42
User: drh
Comment: Remove unnecessary OP_Close opcodes for a size reduction and
performance increase.

AFAIK, your intention was to remove unnecessary OP_Close (in case of
virtual table cursors). The dangling, problematic OP_Close is at the
very end of ``update.c'' (updateVirtualTable(...)):

sqlite3VdbeAddOp2(v, OP_Close, ephemTab, 0);

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VT table behavior change between 3.10 and 3.17

Bob Friesenhahn
In reply to this post by Richard Hipp-3
I got the virtual table module which obviously breaks with 3.17 to
work again.

Is there particular cursor behavior which must be provided although an
update is currently or has just been made to the underlying store?

With my new implementation, the existing cursor uses prior data until
it is closed.

What is the correct expectation?

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...