Attach fails with duckdb.IOException: IO Error: Cannot open file "foo.duckdb.wal": No such file or directory

[mima-hlavacek]

What happens?

Sometimes, when doing attach and detach in parallel, the attach part fails with duckdb.IOException: IO Error: Cannot open file "foo.duckdb.wal": No such file or directory.

To Reproduce

Run the following code snippet repeatedly. Since it does parallel checkpoints, it will usually deadlock (#9341), so you'll need to kill it in that case. Sometimes (about 1 in 10 tries), though, the attach would fail with duckdb.IOException: IO Error: Cannot open file "foo.duckdb.wal": No such file or directory. Couldn't really reproduce it in another way, sorry...

from concurrent.futures import ThreadPoolExecutor
from pathlib import Path
from random import random

import duckdb


def run_thread(cursor, name):
    cursor.execute(f"insert into {name}.{name} select sum(i) from range({int(random()*1000000)}) r(i)")
    cursor.execute(f"checkpoint {name}")

    cursor.execute(f"detach {name}")
    cursor.execute(f"attach '{name}.duckdb' as {name}")


Path("foo.duckdb").unlink(missing_ok=True)
Path("bar.duckdb").unlink(missing_ok=True)
conn = duckdb.connect()
cursor_foo = conn.cursor()
cursor_bar = conn.cursor()
cursor_foo.execute("attach 'foo.duckdb' as foo")
cursor_foo.execute("create table foo.foo(foo bigint)")
cursor_bar.execute("attach 'bar.duckdb' as bar")
cursor_bar.execute("create table bar.bar(bar bigint)")

for i in range(1000):
    print(i)
    with ThreadPoolExecutor(max_workers=2) as executor:
        footure = executor.submit(run_thread, cursor_foo, "foo")
        barture = executor.submit(run_thread, cursor_bar, "bar")
        footure.result()
        barture.result()

OS:

Ubuntu x64 in WSL on Windows 11

DuckDB Version:

0.9.2.dev14+g0ef2a6faa2

DuckDB Client:

Python

Full Name:

Míma Hlaváček

Affiliation:

Blindspot.ai

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[Tishj]

I don't think this is expected to work, checkpoint rewrites/updates the database on disk, attach looks up the database from disk

There is no abstraction on top of this, when the checkpoint is being written it is likely unavailable on the filesystem - causing attach to fail

Or, because you run multiple checkpoints in parallel the WAL has been replayed and promptly deleted by one checkpoint - while another expects it to still exist

[mima-hlavacek]

It's very surprising that this happens, though. There are two threads, and each interacts exclusively with one of the two attached databases. Does that mean that I can expend any transaction to appear in a WAL to any attached database, even if the transaction does not interact in said database in any way?

[Tishj]

Hmm that's fair, I hadnt considered that

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.