VACUUM and transactions

[Jnosh]

The DatabaseWriter.vacuum() and DatabaseWriter.vacuum(into:) methods currently execute outside of a transaction as required by VACUUM by using writeWithoutTransaction.

However, VACUUM will also fail if there is any ongoing transaction running at the same time.

Since writeWithoutTransaction is not reentrant we can't simply wrap DatabaseWriter.vacuum() to ensure there are no ongoing transactions.

Would it make sense to use barrierWriteWithoutTransaction instead to ensure that no other (GRDB) transactions are taking place?
Or is there a need for both versions, i.e. is there a use case for "opportunistic VACUUM" where one would want to try to VACUUM but fail if there are ongoing reads?

[groue]

Hello again, @Jnosh!

Oh, I suppose you are talking about concurrent read transactions. Good catch!

Would it make sense to use barrierWriteWithoutTransaction instead to ensure that no other (GRDB) transactions are taking place?

Sure! As you say, this won't prevent other connections from messing with the vacuum, but at least DatabasePool users won't be surprised by a concurrent read somewhere 👍

The vacuum doc should be updated:

 /// Rebuilds the database file, repacking it into a minimal amount of
 /// disk space.
 ///
+/// The vacuum is not executed until all currently executing
+/// database accesses finish executing (both reads and writes).
+/// At that point, the vacuum is executed. Once done, other
+/// database accesses can proceed.
 ///
 /// Related SQLite documentation: <https://www.sqlite.org/lang_vacuum.html>

Is there a use case for "opportunistic VACUUM" where one would want to try to VACUUM but fail if there are ongoing reads?

I do not know. I lack experience with VACUUM. As long as it is possible to go low level, we can leave this question up to the support:

try writer.writeWithoutTransaction { try $0.execute(sql: "VACUUM") }

[groue]

Oh, I suppose you are talking about concurrent read transactions. Good catch!

Would you please provide more details on the kind of unwanted scenario that writeWithoutTransaction would prevent? I have difficulties getting a database error.

[Jnosh]

Hmm indeed, I can't get it to fail either. I was mostly going off a defensive reading of the documentation. Vacuum does seem to require exclusive access as expected but it seems to wait for ongoing read transactions from the same connection to finish so barrierWriteWithoutTransaction doesn't actually seem to be necessary after all.

I will ask on the SQLite forums when I get some time if that is indeed guaranteed behavior, at least with modern SQLite versions, and if they could update the documentation if so. I'll get back to you when I know more.

[groue]

OK, let's gather more information. The initial question remains pretty relevant.