SQLite User Forum

Session behavoir changed in 3.48
Login
Chronological

Session behavior changed in 3.48

(1.1) By Marco Bubke (marcob) on 2025-01-23 20:22:26 edited from 1.0 [link] [source]

We have two tables:

CREATE TABLE data(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, number NUMERIC, value NUMERIC)

CREATE TABLE tags(id INTEGER PRIMARY KEY AUTOINCREMENT, dataId INTEGER NOT NULL REFERENCES data ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, tag NUMERIC)

  1. Start transaction
  2. Write some "data" entries
  3. Create a session
  4. Write some "tags"
  5. Get the changeset
  6. Delete session
  7. Remove entry in "data"(which is a key for "tags")
  8. Apply change set (with "tags")
  9. Commit transaction

It worked before, and the tags of the change set were removed when we committed the transaction. Now, we get a foreign key constraint error when we commit the transaction.

I am not sure what the expected behavior is.

(2.1) By Dan Kennedy (dan) on 2025-01-24 16:24:43 edited from 2.0 in reply to 1.1 [link] [source]

Thanks for reporting this. There were some changes surrounding the handling of foreign-keys when applying changesets.

I don't quite follow this test case though. Are the following points all correct?

  • Step 4 INSERTs a row into table "tags".
  • Step 7 removes the entry from "data" which is the parent of the row inserted into "tags" by step 4. The CASCADE causes the row inserted by step 4 to also be deleted.
  • Step 8 reinserts the row written by step 4, but this time there is no parent row in "data". i.e. there is a foreign key violation in the db.

If so, then I don't think you should be able to commit the transaction and the COMMIT command returning SQLITE_CONSTRAINT is right. It should not be possible to introduce FK violations using sessions/changesets.

Dan.

(3.1) By Marco Bubke (marcob) on 2025-01-24 18:29:50 edited from 3.0 in reply to 2.1 [link] [source]

The test case can be found there: https://github.com/qt-creator/qt-creator/blob/qds/dev/tests/unit/tests/unittests/sqlite/sqlitesessions-test.cpp#L335. Sorry, it is C++ under a wrapper.

The session resolution cannot work because the commit only enforces the foreign key. However, it worked before, which I found interesting.

If I remove the apply, it works. The cascade is only executed at commit time, so should there be no difference? The tags are only removed at commit time.

(4) By Dan Kennedy (dan) on 2025-01-25 15:40:31 in reply to 3.1 [link] [source]

The cascade is only executed at commit time, so should there be no difference? The tags are only removed at commit time.

That's not quite right. The CASCADE deletes rows from "tags" at the same time as the rows in "data" are deleted, even when an FK constraint is deferred. It's only the checking of the FK violation and raising an SQLITE_CONSTRAINT that is deferred to commit time.

I think that it worked in earlier versions was a bug. Now fixed.

Dan.

(5) By Marco Bubke (marcob) on 2025-01-26 12:52:00 in reply to 4 [source]

Oh, I misunderstood. Is there another way to apply multiple changesets and then enforce the constraints so that the changes that violate the constraints are not applied?

(6) By Dan Kennedy (dan) on 2025-01-29 19:42:14 in reply to 5 [link] [source]

Hi,

This was actually a bug in sessions. Hopefully now fixed here:

https://sqlite.org/src/info/d7c07581

Dan.