Repository navigation
Description
Summary
With the current npm releases (@libsql/client@0.18.0, embedded libsql@0.5.29), an unqualified DELETE FROM <table> clears the base table but leaves rows in the vector index's generated <index>_shadow table. Reusing a rowid on the next insert then fails with:
SQLITE_ERROR: vector index(insert): failed to insert shadow row
The same full-table deletion with a WHERE clause (WHERE 1 = 1) correctly removes the shadow rows and permits reinsertion.
Environment
- macOS arm64
- Node.js
v25.2.1 @libsql/client@0.18.0libsql@0.5.29@libsql/darwin-arm64@0.5.29- local disposable
file:database
These were the latest npm versions when reproduced on October 12, 2026.
Minimal reproduction
Install:
npm install @libsql/client@0.18.0Run:
import { createClient } from '@libsql/client';
import fs from 'node:fs';
fs.rmSync('repro.db', { force: true });
fs.rmSync('repro.db-wal', { force: true });
fs.rmSync('repro.db-shm', { force: true });
const db = createClient({ url: 'file:repro.db' });
const execute = (sql, args = []) => db.execute({ sql, args });
try {
await execute('CREATE TABLE vectors (id TEXT PRIMARY KEY, embedding F32_BLOB(3))');
await execute('CREATE INDEX vectors_idx ON vectors (libsql_vector_idx(embedding))');
for (let i = 0; i < 4; i++) {
await execute(
'INSERT INTO vectors (id, embedding) VALUES (?, vector32(?))',
[`id-${i}`, JSON.stringify([i + 1, i + 2, i + 3])],
);
}
const deletion = await execute('DELETE FROM vectors');
console.log('rowsAffected', deletion.rowsAffected);
console.log('base', (await execute('SELECT count(*) FROM vectors')).rows[0][0]);
console.log('shadow', (await execute('SELECT count(*) FROM vectors_idx_shadow')).rows[0][0]);
console.log('quick_check', (await execute('PRAGMA quick_check')).rows[0][0]);
console.log('integrity_check', (await execute('PRAGMA integrity_check')).rows[0][0]);
try {
await execute(
'INSERT INTO vectors (id, embedding) VALUES (?, vector32(?))',
['replacement', '[9,8,7]'],
);
console.log('UNEXPECTED: insert succeeded');
} catch (error) {
console.log(error.message);
}
} finally {
db.close();
}Actual behavior
rowsAffected 4
base 0
shadow 4
quick_check ok
integrity_check ok
SQLITE_ERROR: vector index(insert): failed to insert shadow row
In this minimal case, both PRAGMA quick_check and PRAGMA integrity_check still returned ok, so those pragmas did not expose the stale generated rows.
Expected behavior
Deleting all base rows should also remove all corresponding vector-index shadow rows, and a subsequent insert should succeed.
Trigger is specific to the no-WHERE delete path
The following matrix was run against a fresh database per case:
| Delete statement | Base/shadow after delete | Next insert |
|---|---|---|
DELETE FROM vectors |
0/4 |
fails |
DELETE FROM vectors WHERE 1 = 1 |
0/0 |
succeeds |
DELETE FROM vectors WHERE rowid IS NOT NULL |
0/0 |
succeeds |
DELETE FROM vectors WHERE rowid IN (SELECT rowid FROM vectors) |
0/0 |
succeeds |
DELETE FROM vectors WHERE id LIKE 'id-%' |
0/0 |
succeeds |
four DELETE ... WHERE id = ? statements |
0/0 |
succeeds |
For the tested package versions, a WHERE clause that matches every row is therefore a confirmed workaround for this reproduction.
Evidence ladder:
libsql@0.5.29via@libsql/client@0.18.0reproduces the stale shadow rows and failed reinsertion.EXPLAIN DELETE FROM vectorsusesClearopcodes, whileEXPLAIN DELETE FROM vectors WHERE 1 = 1uses row-wiseIdxDelete/Deleteopcodes.- On current
tursodatabase/libsqlmain (d6c75af6353bb1c34985399608e37cd272a35aa1when inspected),libsql-sqlite3/src/delete.c:459-494contains the no-WHERE truncate path usingOP_Clear, whilelibsql-sqlite3/src/vectordiskann.c:1657-1734contains the ordinary per-row vector deletion path and itsdiskAnnDeleteShadowRowcall. - Current-main vector tests cover qualified deletes (
libsql-sqlite3/test/libsql_vector_index.test:91-99,123-130,266-278) but not an unqualified full-table delete.
This evidence suggests that the truncate path bypasses the observed per-row shadow-table maintenance. I reproduced the executable failure against the latest published npm package; I did not build current main locally.
Impact
Applications that truncate and refill a vector-indexed table can deterministically leave stale derived state and then fail on reinsertion. Dropping and recreating the vector index reconciled the disposable reproduction, but application-level rebuilds are costly and are not a substitute for maintaining the index during DELETE.
Relationship to existing reports
I could not find an existing issue for this exact no-WHERE/truncate-optimization trigger.
- Vector index corruption on row delete with duplicates #2197 reports qualified deletion with duplicate vectors causing search omissions; this reproduction does not use duplicate vectors and fails on the next insert.
- vector search: fix delete of rows with NULL vector value #1660 fixed qualified deletion of rows whose vector value is NULL; this reproduction uses non-NULL vectors and bypasses the per-row delete path.
- fix dump and vacuum of vector indices #1591 and fix behaviour of VACUUM for vector indices to make rowid consistent between shadow tables and base table #1641 address dump/VACUUM maintenance of vector shadow tables, which appears related in architecture but uses a different trigger.
Suggested regression coverage
A vector-index test that:
- inserts several non-NULL vectors;
- executes unqualified
DELETE FROM table; - verifies the base and shadow counts are both zero;
- inserts another vector successfully.
Activity
fix(libsql): keep vector indexes writable after truncateIndex (#26802)
colinhacks commentedon Nov 19, 2026
Same error over HTTP on sqld 0.24.31 and on Turso Cloud. Before the reinsert,
vector_top_kstill returns the deleted row:I built the shell from #2302 and from its parent. The parent fails as above. With the PR, the shadow table is empty after the delete,
vector_top_kreturns nothing, and the insert succeeds.