Read/Write Locks Database

[archiewood]

trafficstars

To Reproduce

1. Open a Read/Write connection using SQLTools to a duckdb
2. (Don't run any queries)
3. Attempt to access the duckdb from another client

Expected result

Another client should be able to access the duckdb

(Assuming no read/write queries are currently running)

Actual result

A .wal file is generated for the db, suggesting there are changes to the DB that are yet to be commited

IO Error: Could not set lock on file "../../needful_things.duckdb": Resource temporarily unavailable

Extra Context

See #4

This is somewhat by design from duckdb, but we should be able to close the readwrite connection after a query has finished / when there are no queries running.

I think core SQLTools is doing something unexpected here on connection which

Workarounds

1. Either: Running any query from duckdb SQLTools seems to clear the .wal and close the connection
2. Or: Manually close the connection from the duckdb sidebar

[MartinR-PCS]

When I connect to connection defined RW in SQL tools it immediately blocks any access to the file with error "... is being used by another process" => I'm not able to send any query or close the connection then Driver version: 0.0.4 SQLTools: 0.28.0 VSCode: 1.83.1

[alex-gable]

Also facing this bug. This extension doesn't release the lock upon disconnect. You have to quit vscode entirely.

Also, this shouldn't be necessary at all when the connection is set as read-only

[alex-gable]

@archiewood I think the issue is fairly straightforward actually. I noticed this doesn't happen when you test the connection, which should have the same functionality as a READ_ONLY saved connection. So, what's the difference?

in testConnection we await this.close().

In close, we do not await. We're likely setting the connection to null before the closing happens.

I don't have a way to test this, but it should be a 60s change tbh.

[alex-gable]

I attempted to make this change above. it certainly makes an improvement but is still flaky, That said, I was able to find the true culprit

When you disconnect and close out of all windows (results, *.session.sql, any *.sql file), there's still a lingering language server process. killing that PID will release the duckdb connection (and a new sqltools language server process will spin up)

[archiewood]

@alex-gable thanks for the digging.

Will happily accept a PR if you find anything to make it less flaky!