Hi!
Quick facts:
.package(url: "https://github.com/powersync-ja/powersync-swift", from: "1.7.0"),
I've started to get constraint errors suddenly on insert to the Cards table:
caught error: "operationFailed(message: nil, underlyingError: Optional(Error Domain=KotlinException
Code=0 "SqliteException(1555): constraint failed, constraint failed for SQL: INSERT INTO cards (
Which was quite hard to debug because of a lack of information.
The error constraint failed persisted, even if I kept only the id column without any other columns in the schema.
It turns out that the error was in FTS cache, which had more rows in the fts_cards_content table than the ps_data__cards table (159 VS 8). With lots of gaps in the ID sequence.
The FTS triggers are literally a copy-paste from the demo project.
I have no idea why the AFTER DELETE trigger didn't work, but this issue messed up the whole PowerSync behaviour. Do not find something in our case is better than have errors inserting that.
It feels like it's safer to rewrite the AFTER INSERT trigger like this to workaround potential FTS discrepancies -- added OR REPLACE:
// 3. Create INSERT Trigger
sqlStatements.append("""
CREATE TRIGGER IF NOT EXISTS fts_insert_trigger_\(tableName) AFTER INSERT ON \(internalName)
BEGIN
INSERT OR REPLACE INTO \(ftsTableName)(rowid, id, \(stringColumnsForInsertList))
VALUES (
NEW.rowid,
NEW.id,
\(generateJsonExtracts(type: .columnOnly, sourceColumn: "NEW.data", columns: columns))
);
END;
""")Would be happy to see a better solution. Thank you!
Hi!
Quick facts:
.package(url: "https://github.com/powersync-ja/powersync-swift", from: "1.7.0"),I've started to get constraint errors suddenly on insert to the Cards table:
Which was quite hard to debug because of a lack of information.
The error
constraint failedpersisted, even if I kept only theidcolumn without any other columns in the schema.It turns out that the error was in FTS cache, which had more rows in the
fts_cards_contenttable than theps_data__cardstable (159 VS 8). With lots of gaps in the ID sequence.The FTS triggers are literally a copy-paste from the demo project.
I have no idea why the
AFTER DELETEtrigger didn't work, but this issue messed up the whole PowerSync behaviour. Do not find something in our case is better than have errors inserting that.It feels like it's safer to rewrite the
AFTER INSERTtrigger like this to workaround potential FTS discrepancies -- addedOR REPLACE:// 3. Create INSERT Trigger sqlStatements.append(""" CREATE TRIGGER IF NOT EXISTS fts_insert_trigger_\(tableName) AFTER INSERT ON \(internalName) BEGIN INSERT OR REPLACE INTO \(ftsTableName)(rowid, id, \(stringColumnsForInsertList)) VALUES ( NEW.rowid, NEW.id, \(generateJsonExtracts(type: .columnOnly, sourceColumn: "NEW.data", columns: columns)) ); END; """)Would be happy to see a better solution. Thank you!