Bug: CREATE_FTS_INDEX fails under concurrent writes and can leave the index name unusable #121

This work was produced with the help of language models.

Ladybug version

v0.21.2 (PyPI ladybug, INSTALL fts) and main at 0ba61c4a9 (extensions 036c1d9).

What operating system are you using?

Ubuntu 24.04 (Linux x86_64)

What happened?

CREATE_FTS_INDEX runs as about 15 separate write transactions. If another connection is writing when any of them starts, it fails with Cannot start a new write transaction in the system. When that happens after the first internal table exists, the leftover table blocks every retry: CREATE_FTS_INDEX reports that the table already exists, DROP_FTS_INDEX reports that there is no such index, and DROP TABLE cannot see the internal table either.

The script below keeps a second connection writing one row every 20 ms while the first creates six indexes:

idx1: Cannot start a new write transaction in the system. Only one write transaction at a time ...
  retry: Binder exception: Table: 0_idx1_appears_info already exists. Please drop or rename the table ...
  drop:  Binder exception: Table Doc doesn't have an index with name idx1.

Are there known steps to reproduce?

import threading, time
import ladybug as lb

db = lb.Database("fts-concurrent.lbug")
a, b = lb.Connection(db), lb.Connection(db)
a.execute("INSTALL fts")
a.execute("LOAD fts")
a.execute("CREATE NODE TABLE Doc(id INT64 PRIMARY KEY, body STRING)")
a.execute("UNWIND range(0, 199999) AS i CREATE (:Doc {id: i, body: 'apple banana cherry'})")
next_id = 10**6

def run(q):
    try:
        return a.execute(q).get_all()
    except RuntimeError as e:
        return str(e)[:90]

for n in range(6):
    stop = False

    def writer():
        global next_id
        while not stop:
            try:
                b.execute(f"CREATE (:Doc {{id: {next_id}, body: 'date'}})")
                next_id += 1
            except RuntimeError:
                pass  # CREATE_FTS_INDEX holds the write transaction
            time.sleep(0.02)

    t = threading.Thread(target=writer)
    t.start()
    first = run(f"CALL CREATE_FTS_INDEX('Doc', 'idx{n}', ['body'])")
    stop = True
    t.join()
    print(f"idx{n}: {first}")
    print(f"  retry: {run(f'CALL CREATE_FTS_INDEX(\"Doc\", \"idx{n}\", [\"body\"])')}")
    print(f"  drop:  {run(f'CALL DROP_FTS_INDEX(\"Doc\", \"idx{n}\")')}")

On both 0.21.2 and main, all six CREATEs failed and five of the six names were left unusable.

Actual (0.21.2, main): Cannot start a new write transaction in the system, and every retry fails on the leftover 0_idx1_appears_info table.
Expected: CREATE waits for the other writer, or fails without leaving anything behind.

Likely cause

Each internal step commits in its own write transaction, so the steps that finished before the failure stay behind, while the index catalog entry that DROP_FTS_INDEX looks for is never created. On retry, validateInternalTablesNotExist (create_fts_index.cpp#L151) rejects the leftover table.

Related: #77 (leftovers after a crash during CREATE or DROP). Its fix #104 drops schema-confirmed internal tables when the index entry is absent, which is the state every retry above hits, so with it the retry should succeed; the CREATE itself still fails while another connection writes.