Skip to content

Unqualified DELETE leaves libsql_vector_idx shadow rows and breaks the next insert #2289

Open

Description

@CodingButter

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.0
  • libsql@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.0

Run:

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:

  1. libsql@0.5.29 via @libsql/client@0.18.0 reproduces the stale shadow rows and failed reinsertion.
  2. EXPLAIN DELETE FROM vectors uses Clear opcodes, while EXPLAIN DELETE FROM vectors WHERE 1 = 1 uses row-wise IdxDelete/Delete opcodes.
  3. On current tursodatabase/libsql main (d6c75af6353bb1c34985399608e37cd272a35aa1 when inspected), libsql-sqlite3/src/delete.c:459-494 contains the no-WHERE truncate path using OP_Clear, while libsql-sqlite3/src/vectordiskann.c:1657-1734 contains the ordinary per-row vector deletion path and its diskAnnDeleteShadowRow call.
  4. 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.

Suggested regression coverage

A vector-index test that:

  1. inserts several non-NULL vectors;
  2. executes unqualified DELETE FROM table;
  3. verifies the base and shadow counts are both zero;
  4. inserts another vector successfully.

Activity

  1. colinhacks commented on Nov 19, 2026

    @colinhacks

    Same error over HTTP on sqld 0.24.31 and on Turso Cloud. Before the reinsert, vector_top_k still returns the deleted row:

    create table docs (id integer primary key, emb F32_BLOB(3));
    create index docs_emb on docs (libsql_vector_idx(emb));
    insert into docs values (1, vector('[1,2,3]'));
    delete from docs;
    select id from vector_top_k('docs_emb', vector('[1,2,3]'), 1);  -- 1, with docs empty

    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_k returns nothing, and the insert succeeds.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions