Re: [sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp
Dan, I tried the above patch and it resolves the issue! Also you were right about use of "*" in attach. I misinterpreted the output of my test in that case. Thank you so much for taking the time to fix this! When can I expect this patch to make it into a release? Adam Levy On Thu, Feb 27, 2020 at 8:23 AM Dan Kennedy wrote: > > > On 27/2/63 05:04, Adam Levy wrote: > > Hi all > > > > When I have a database connection with a temp table of the same name > > as a table in main, I am getting what I feel is unexpected behavior > > from the session extension. > > > > Most succinctly, I start a session on a connection on the main db with > > a table with an INTEGER PRIMARY KEY, attach it to all tables by > > passing NULL, make a change such as a single INSERT in aforementioned > > table, and then record a changeset. Then I invert this changeset, and > > apply that inverse to the same connection, to reverse the changes just > > made. > > > > Normally this works flawlessly. However, if a table by the same name > > exists in the temp database on that connection, I run into odd > > behavrior. > > > > If the temp table has different rows than the main table, a very odd > > changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA > > but when I examine the changeset iter the values for the columns, they > > match the conflicting values. The inverse of an INSERT is a DELETE so > > this conflict type indicates that one of the non-primary key column > > values doesn't match, but in every examination of the database and the > > changeset I have performed they do match. The primary key exists and > > matches as well. > > > > If the temp table has the same rows at the main table (what the > > changeset expects), the changeset_apply does not raise a conflict, but > > it takes no affect on the main table. > > Thanks for reporting this. As far as I can figure, it's a bug in > sqlite3changeset_apply(), not sqlite3session_attach(). Now fixed here: > >https://sqlite.org/src/info/f71a13d072398c9f > > If you get the chance, please check if this fixes your test script and > let us know if it does not. > > > This can all be avoided by being explicit about the database when > > calling sqlite3session_attach. > > For example passing "*" or "main.*" or "main." avoids the > > issue. > > I think that's just because sqlite3session_attach() doesn't understand > any of those shorthands. If you pass "*", it searches for a table named > "*", not a table that matches the glob pattern "*". > > Thanks, > > Dan. > > > > > > > But it occurs if NULL is passed. This is not obvious and > > arguably not expected given that the documentation about the Session > > extension makes it seem like it should be already scoped to a single > > database on a connection. After all, sqlite3session_create accepts the > > database as an argument. Also, sqlite3_changeset_apply appears to be > > specific to the "main" database. So it shouldn't care about whats in > > the temp database at all. > > > > From all of the docs I have read on the Session extension this seems > > like misbehavior at the level of sqlite3session_attach being passed > > NULL for the zTab argument. At the minimum perhaps a note in the > > documentation of sqlite3session_attach would be appropriate. > > > > Below is a very simple example that can reproduce this behavior. It > > has a number of lines commented out that describe how the bug can be > > avoided to caused. > > > > Its written in Golang, but the library it uses is just a very thin > > wrapper around the C interface. It is using the latest 3.31.1 > > amalgamation. I help maintain the SQLite library that it uses and can > > confirm that everything being called here is a very thin wrapper > > around the C interface. I could write a C example but it would take me > > more work. > > > > The library it calls to print the SQL of the changeset is a utility I > > wrote that is just using a thin wrapper around the changeset_iter to > > parse the changeset into human readable SQL. It of course is not aware > > of database names so it doesn't print "main" but this SQL is just for > > displaying to the user. The values returned by > > sqlite3changeset_conflict are formatted into the comments of that > > printed SQL. > > > > The code can be more easily downloaded here: > > https://github.com/AdamSLevy/sqlite-session-bug > > > > // main.go > > > > pack
[sqlite] Issues with sqlite3session_attach(conn, NULL) w/ duplicate table in temp
Hi all
When I have a database connection with a temp table of the same name
as a table in main, I am getting what I feel is unexpected behavior
from the session extension.
Most succinctly, I start a session on a connection on the main db with
a table with an INTEGER PRIMARY KEY, attach it to all tables by
passing NULL, make a change such as a single INSERT in aforementioned
table, and then record a changeset. Then I invert this changeset, and
apply that inverse to the same connection, to reverse the changes just
made.
Normally this works flawlessly. However, if a table by the same name
exists in the temp database on that connection, I run into odd
behavrior.
If the temp table has different rows than the main table, a very odd
changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA
but when I examine the changeset iter the values for the columns, they
match the conflicting values. The inverse of an INSERT is a DELETE so
this conflict type indicates that one of the non-primary key column
values doesn't match, but in every examination of the database and the
changeset I have performed they do match. The primary key exists and
matches as well.
If the temp table has the same rows at the main table (what the
changeset expects), the changeset_apply does not raise a conflict, but
it takes no affect on the main table.
This can all be avoided by being explicit about the database when
calling sqlite3session_attach.
For example passing "*" or "main.*" or "main." avoids the
issue. But it occurs if NULL is passed. This is not obvious and
arguably not expected given that the documentation about the Session
extension makes it seem like it should be already scoped to a single
database on a connection. After all, sqlite3session_create accepts the
database as an argument. Also, sqlite3_changeset_apply appears to be
specific to the "main" database. So it shouldn't care about whats in
the temp database at all.
From all of the docs I have read on the Session extension this seems
like misbehavior at the level of sqlite3session_attach being passed
NULL for the zTab argument. At the minimum perhaps a note in the
documentation of sqlite3session_attach would be appropriate.
Below is a very simple example that can reproduce this behavior. It
has a number of lines commented out that describe how the bug can be
avoided to caused.
Its written in Golang, but the library it uses is just a very thin
wrapper around the C interface. It is using the latest 3.31.1
amalgamation. I help maintain the SQLite library that it uses and can
confirm that everything being called here is a very thin wrapper
around the C interface. I could write a C example but it would take me
more work.
The library it calls to print the SQL of the changeset is a utility I
wrote that is just using a thin wrapper around the changeset_iter to
parse the changeset into human readable SQL. It of course is not aware
of database names so it doesn't print "main" but this SQL is just for
displaying to the user. The values returned by
sqlite3changeset_conflict are formatted into the comments of that
printed SQL.
The code can be more easily downloaded here:
https://github.com/AdamSLevy/sqlite-session-bug
// main.go
package main
import (
"bytes"
"fmt"
"io"
"log"
"crawshaw.io/sqlite"
"crawshaw.io/sqlite/sqlitex"
"github.com/AdamSLevy/sqlitechangeset"
)
func run() error {
conn, err := sqlite.OpenConn(":memory:", 0)
if err != nil {
return fmt.Errorf("sqlite open: %w", err)
}
defer conn.Close()
fmt.Println("Creating tables...")
err = sqlitex.ExecScript(conn, `
CREATE TABLE main.t(id INTEGER PRIMARY KEY, a,b,c);
CREATE TABLE temp.t(id INTEGER PRIMARY KEY, a,b,c) --- remove this
line or rename the table to avoid issue;
`)
if err != nil {
return err
}
fmt.Println("Starting session on main...")
sess, err := conn.CreateSession("main")
if err != nil {
return err
}
defer sess.Delete()
// An empty string will pass NULL to sqlite3session_attach and allow
// the bug.
var attach string
// Any of these prevent the bug.
//attach = "*"
//attach = "main.*"
//attach = "main.t"
fmt.Printf("Attaching to %s ...\n", attach)
if err := sess.Attach(attach); err != nil {
return err
}
fmt.Println("Inserting into main.t ...")
commit := sqlitex.Save(conn)
err = sqlitex.ExecScript(conn, `
INSERT INTO main.t(a,b,c) VALUES (1,2,3);
INSERT INTO temp.t(a,b,c) VALUES (1,2,3) --- This line avoids the
conflict, but the changeset doesn't apply to main.t like expected;
`)
if err != nil {
return err
}
commit()
sql, err := sqlitechangeset.SessionToSQL(conn, sess)
if err != nil {
return err
}
fmt.Println("changeset:", sql)
chgset := bytes.NewBuffer(nil)
if err := sess.Changeset(chgset); err != nil {
return err
}
invrt := bytes.NewBuffer(nil)
invrtCp :=
[sqlite] Typo in docs
The documentation for PRAGMA schema_version has a small grammar error in the first sentence: "The schema_version pragma will to get or set the value of the schema-version integer at offset 40 in the database header. " Note the "will to get or set". The "to" should be removed. Thanks Adam ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record
Why can't the knowledge of the "last row" be obtained and used by the application after _step returns SQLITE_DONE? Although what Doug suggested could work, it doesn't make sense to me to add an extra count or max query just to know how many results will be returned when those results will be queried anyway. On Tue, Oct 15, 2019, 1:16 PM Doug wrote: > How about something like this that costs more to run: > > Given a table T with columns A, B, C,.. > BEGIN TRANSACTION > SELECT Count(*) AS Count [filter spec]; > SELECT A,B,C,... [filter spec]; > ROLLBACK or COMMIT > > Doug > > > -Original Message- > > From: sqlite-users > > On Behalf Of Simon Slavin > > Sent: Tuesday, October 15, 2019 8:35 AM > > To: SQLite mailing list > > Subject: Re: [sqlite] Last record > > > > On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch> > > wrote: > > > > > how could I know if I am reading the last record with > > > sqlite (sqlite3_step)? > > > > Sorry, there's no way to do that for some arbitrary SELECT. > > Because SQLite itself may not know. > > > > SQLite does not always process your query and store all the > > results in memory. If there's an ideal index for your query, each > > call to _step() just one more row. SQLite itself doesn't know it > > has reached the end until it gets an error because it runs off the > > end of the index. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > > users > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ensure a snapshot remains readable
My application requires a way to build a "pending state" in the database while allowing users to query data from both the "official state" and the "pending state". I am achieving this using sessions and snapshots. When pending data first comes into the application, I take a snapshot of the current "official" state of the database and keep a read transaction open on one read only connection for the life of the snapshot to prevent it being checkpointed out of the WAL, and then start a session that tracks all changes on my one write connection so that the pending data may later be "rolled back" by applying the inverse changeset. Then I proceed to commit incoming pending data to the write connection. Any user querying the application for the pending state, is reading from a read only connection which will reflect the current latest state of the database, which includes the pending data, if any exists. Any user querying for the official state, is reading from a read only connection that has a read transaction started from the official state snapshot captured above, if any pending data exists. When the next official state update occurs, the snapshot is freed, its associated read transaction that I set aside is closed (and the read connection is returned to my read conn pool), and the inverse changeset is used to roll back the pending state on a write connection before applying the official state update. Any reader currently reading from the snapshot should be able to proceed until they end their read transaction. I assumed that keeping a read transaction open on the snapshot would be enough to prevent the WAL from being checkpointed past the snapshot, making it unavailable for use by other read connections. However, despite keeping one read transaction open, I am still getting SQLITE_ERROR_SNAPSHOT from sqlite3_snapshot_open when the pending data grow large enough to force an auto checkpoint. I am fairly confident that the snapshot getting checkpointed out of the WAL is the cause of this error since it went away when I disabled auto checkpoints. Is my assumption incorrect? If so, is there any way to ensure a snapshot remains in the WAL without going so far as to manually manage checkpoints myself? Finally if I must manually manage checkpoints, is it possible for a PASSIVE checkpoint to leave the WAL in a state that will prevent taking a snapshot in the first place? Specifically, can a PASSIVE checkpoint cause the following requirement for sqlite3_snapshot_get to no longer hold: "One or more transactions must have been written to the current wal file since it was created on disk (by any connection). This means that a snapshot cannot be taken on a wal mode database with no wal file immediately after it is first opened. At least one transaction must be written to it first." Thank you Adam Levy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ensure WAL file for sqlite3_snapshot_get
One of the requirements for sqlite3_snapshot_get is that "One or more transactions must have been written to the current wal file since it was created on disk (by any connection). This means that a snapshot cannot be taken on a wal mode database with no wal file immediately after it is first opened. At least one transaction must be written to it first." I am looking for a simple way to ensure that this is the case. One way of course, is to simply start a write transaction with at least one write and roll it back. Simply doing BEGIN IMMEDIATE; COMMIT; does not appear to qualify although it does cause the WAL file to be created, it must not write a transaction to the WAL file. Creating a "test" table and adding a row and then rolling this back seems to work, but I'd like to not have to rely on a database not already having a table by a particular name. Is there a simpler way that is more like a NO-OP of some sort but that will ensure that the WAL file has at least one transaction in it? Thank you Adam ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
