isRetryNeeded: another OPFS idea #105
Replies: 4 comments 9 replies
-
Only insofar as the name "step" was recycled for an async operation. step() has a long and glorious tradition of well-defined synchronous semantics, and changing those is a recipe for confusion for those well-versed in that tradition. If it's going to be async, it shouldn't be called step(). IMO.
Funnily enough... last summer, during our initial work with OPFS, Richard proposed something very similar to that at the library level. His idea was to add a new result code specifically for that case, and then do the necessary waiting/retry magic in the library. We had time constraints at the time which made it unattractive, though, in particular given that we were complete newbies to wasm, so it wasn't attempted. The idea just fell beneath the cracks after that and was never revisited. i will arrange to talk to him about that, now that what amounts to the same idea has been floated somewhere else and has larger applicability than just the OPFS async routines (which was our only focus at the time). The idea of async SQL functions, for example, didn't come up at the time (and async SQL funcs still smell strongly of "ice-skating uphill" to me, but if this approach incidentally bolts spikes onto those skates then let the ice-skating begin).
That part initially sounds like it would be horribly problematic, but... under Richard's initial proposal, the library would effectively (and client-transparently) block on each async call in that step(), in the order sqlite resolves them. So they would lose any genuine benefit of actually being async, in that they would not run concurrently, but it would (hypothetically) enable SQL like... select fetch_blob('https://...') as blob1, fetch_blob('https://...') as blob2;to operate predictably (just not with its full async potential). i admit still not understanding exactly how the retry capability would work. It sounds to me like the routine which returned the new "wait and retry" result code would have to keep track of its current state and treat new calls back into that function as retry attempts until the function either resolves and returns 0 or fails are returns some other result code. That may well be feasible. |
Beta Was this translation helpful? Give feedback.
-
It would be cool to have suspend support inside SQLite, though I'm surprised that it would have more than niche usage. It would be much less of a hack than doing it externally. On the other hand, OPFS access handles don't have much asynchronous behavior left now, just enough to be annoying. Only open, delete, access, and locking are still asynchronous, and only exclusive locking is needed (because OPFS doesn't have shared reads). I'm wondering if it would be overkill to implement general suspend support in the library that presumably would allow all VFS methods to be asynchronous and and include all locking levels. Or maybe those extra paths don't require much incremental suspend code, I don't know. |
Beta Was this translation helpful? Give feedback.
-
That niche extends past VFSes, though. In late 2022, after adding virtual table support, i attempted to add a demo virtual table which exposed a view of the OPFS filesystem but couldn't because of the OPFS async methods. At least one user in the forum has bemoaned the inability to use async methods in virtual tables. Similarly, the ability to implement user-defined SQL functions with JS async funcs would be nice. Once JSPI is widespread this all may become a non-issue, but it is currently a non-trivial hurdle. Depending on how extensive and inter-woven the changes would need to be, they way well be i have pinged Richard about picking up that topic again but he's currently neck-deep in customer support and has asked me to remind him again this weekend or early next week.
Not yet, but they're actively working a proposal for that. No idea what their time frame is, but it's not right around the corner.
i don't either - the sqlite internals are 99.9% opaque to me. My work on it is limited to the shell app and the JS/wasm bits, all but a tiny, tiny, tiny part of which (like a single trivial function) can be considered client-level code. We'll hopefully know soon whether that approach is feasible. |
Beta Was this translation helpful? Give feedback.
-
It's as trivial as using wa-sqlite! 🤪 |
Beta Was this translation helpful? Give feedback.
-
Yes, if OPFS access handles get shared reads and writes, as in the proposal, the changes to the access handle pool approach should be straightforward to support multiple connections - just change creation modes and add Web Locks. |
Beta Was this translation helpful? Give feedback.
-
Silly or not, the interesting thing is that even if This is to the point raised here:
If someone needs to access Anyways, I'm surprised the SQLite authors are so hard to convince of the need for better APIs to support statement caching in async environments like JS. Granted I did go off on a random and long exposition in that thread and wasn't very effective at communicating the need but I do find the SQLite team to be generally dismissive of any idea brought to them. Recent memory would be the disparaging remarks (now corrected) about alternate WASM builds and replies like the following --
which is wrong while also being arrogant. cc @sgbeal |
Beta Was this translation helpful? Give feedback.
-
i can't speak to the business aspects of sqlite (which are, quite frankly, none of my business (as a volunteer, doing this solely for the boundless joy of it, not an employee or contractor)). When OPFS and WASM came to our attention (not coincidentally, at the same time), we were collectively curious whether we could make sqlite work in that environment. As the only project member with any appreciable JS experience, that experiment fell into my lap. Until that point, my role within the project was administrative, mainly dealing with the forum. (My participation in Richard's projects goes back to 2008, primarily via his Fossil SCM.) As far as oversight and consensus goes, it falls into the time-honored category of "(s)he who writes the code gets to decide how it works." In my experience that's completely normal in any small-team project. Granted, any major design decisions are discussed with the core team and i invariably defer to Richard's and Dan's preferences, but for the most part i'm given free reign on the design (but also accept full responsibility if it explodes, as well as the onus of fielding all support requests). i've made clear in the forum several times that community feedback is important for the JS API, and go well out of my way to consider and address such feedback, but we've received surprisingly little concrete feedback, so the consensus, for the most part, comes down to my own estimate of what the interface should look like. FWIW, it's largely based on the experience of having written 8(?) prior sqlite scripting engine bindings over the past 17-ish years, so it's not entirely pulled out of thin air. As to library-level support for statement caching, i can't speak much to that - that would need support down in the core, which is above my proverbial pay grade. Sqlite has scores, if not hundreds, of scripting-language bindings which apparently get by just fine without support in the core C library for statement caching (an estimation based solely on the complete lack of RFEs in the forum, which is admittedly not a great metric (but it's the only metric we have)). Okay, that was probably more than y'all wanted to know, but that's the long and the short of it ;).
That particular comment, i recall very clearly, was made by Mr. K.M. He is not a project member but he has been a forum/mailing-list contributor since time immemorial. He can be (and very often is) quite difficult to deal with, and can be downright condescending, but he's tolerated because he is demonstrably a bona fide über-guru on the topics of relational databases and sqlite, on a level matched by perhaps a handful worldwide. Comments like the quote above are right on that border of grudgingly tolerated. i have, in my role as forum admin, reprimanded him in the past on this, but his particular case is a bit of a sticky situation which effectively prohibits me from outright removing him from the forum or forcing all of his posts through moderation until he very blatantly crosses a line (which he continues to walk quite effectively). More than once, a forum admin has edited posts from him to remove profanity, but we certainly don't catch them all. His position is unique within the forum, though - we're not as forgiving for anyone else. By freak coincidence it occurred to me earlier today that he has not posted in some 16 days, which is unheard of. Since the forum went online, there's not been a single calendar-week in which he's not posted until the past two. So, with that, i wish you all Happy Hacking and good night! |
Beta Was this translation helpful? Give feedback.
-
I think you for your work.
One thing you may never know, however, is how many promising new experts have been or could be driven away.
I think most of those don't have the |
Beta Was this translation helpful? Give feedback.
-
Okay, I've actually read your forum thread now, and I don't really see the distinct requirement Javascript imposes either. The ability to make asynchronous calls does give you more ways to create erroneous code, and the SQLite library doesn't protect you from that, but as a low-level library I don't see that as necessarily its responsibility. So I'm mainly looking for things you just can't do without library support but I'm mostly not seeing them, not yet anyway. You say:
I don't see how it follows that queuing means not exposing step(). What prevents you from accessing cached statements in a transaction lambda? In the DB::tx example, why can't prepare() and finalize() be replaced with acquireCached() and releaseCached() or have that integrated into tx with something like: // earlier
db.cache("keyA", "SELECT blah1, blah2 FROM foo");
db.cache("keyB", "SELECT blah3 FROM bar");
await db.tx(async (tx) => {
const stmt1 = tx.cached("keyA");
const rows = await stmt.run(tx, []);
// or row by row with an async generator
for await (const row of stmt.iterateRows(tx, [])) {
// use row
}
}, ["keyA"]); |
Beta Was this translation helpful? Give feedback.
-
Eh. Here's another pretty famous one:
And one that again was both wrong and denigrating:
Not that I don't understand it. It's hard being front-line support for the software you develop; it makes it personal. I'm sure It's also frustrating to endure the constant stream of users who want you to fix their bugs and won't RTFM. Anyone could get a little testy (and apologies were made for both these posts), and you can probably cherry-pick cases from any popular support channel. Still, I would agree that the SQLite forum is not the most welcoming place even for serious would-be contributors, and that might be part of the reason for lack of feedback and engagement. Maybe it's actually a good strategy to keep traffic to a manageable level, not entirely joking. |
Beta Was this translation helpful? Give feedback.
-
FWIW, i agree that that was undeniably technically wrong, in the sense that it took a position based on an incorrect premise, but not denigrating. Perhaps my definition of that word is too narrow, and that's something for me to take into consideration in future posts.
You're not wrong, certainly, and i have made a conscious effort the past year-ish to curb my instinct to automatically reply to All The Posts. It's an ongoing process, with "good days" and "bad days." |
Beta Was this translation helpful? Give feedback.
-
|
TL;DR The retry idea works, but AccessHandlePoolVFS still seems like a better choice. I wrote a limited proof-of-concept RetryVFS and an accompanying demo for this idea. This initial implementation has these restrictions:
I'll come back to these restrictions further down. What will this VFS do?
Basically, if you want OPFS storage, like with AccessHandlePoolVFS, with multiple simultaneous connections (unlike AccessHandlePoolVFS) then this is a possibility. AccessHandlePoolVFS supports ATTACHing additional databases and RetryVFS currently doesn't, but it seems likely that most web applications can get by with a single database. How does it work?Whenever SQLite tries to lock the database (which it does by calling the VFS method xLock), if RetryVFS does not already have a lock then:
When the application receives the SQLITE_BUSY error, it waits on RetryVFS to complete all its pending asynchronous operations, then retries what it was doing. On the second attempt, the VFS will already have the lock and the access handles to do anything SQLite wants to do within the lock scope. There is one extra detail to take care of. When SQLite first opens a database file, it will read its file header outside the scope of any lock. For this reason, when a RetryVFS instance is constructed, it acquires an access handle for its database file. This handle is closed at the end of any read without a lock. How does the application need to cooperate?Whenever that SQLITE_BUSY error comes back to application code, the application needs to handle it. This can happen in preparing a statement or in stepping a statement. Here's the demo code that handles prepare. It's quite straightforward: try the prepare, handle the error, wait for the VFS, try again. If you're wondering why preparing a statement needs to read the database, it happens at application start when SQLite hasn't yet loaded the schema. Stepping a statement is slightly more complicated; here's that code. It's more complicated because you can't just repeat a call to step() after an error; you have to restart the statement. Otherwise the concept is the same. You promised to talk about the restrictions. Were you lying?The requirement to change the journal mode from the default is pretty minor. I don't think anyone would have trouble with that. The requirement to keep temporary tables and indices in memory can be avoided by using a pool of OPFS files to associate with temporary files, as AccessHandlePoolVFS does. Each VFS instance would need its own distinct pool of files. So this restriction can be lifted with existing and proven techniques. That leaves the single database restriction. Allowing multiple databases might be technically possible but it will be more complicated and slower. The concept doesn't scale cleanly. There are a number of problems here. The underlying issue is that when the VFS returns an error code, SQLite wants to undo a lot of the state it built to get to that point. For example, let's say you have two databases dbX and dbY and want to read from both in the same SELECT. That means you will need a read lock on both. Something like this happens:
Now you're back to where you started and have accomplished nothing. You can make this work by adding VFS logic to detect when this happens and tell SQLite dbX is successfully unlocked while secretly keeping it locked. That should work, but it adds complexity and these round trips for every asynchronous operation take time. RetryVFS needs to do three asynchronous operations - acquiring a Web Lock, getting a database file access handle, and getting a journal file access handle - but it only requires one retry round trip because it always does all of these operations together. You could extend this to multiple databases by getting all the access handles on all the databases and journals no matter what. There are other state problems, though. For example, a transaction that writes to multiple databases needs to create a super-journal file in the filesystem. You can asynchronously create that file and return SQLITE_BUSY, but when the retry comes back the super-journal file will have a different name because the filename is generated randomly. I think there are workarounds for that, too, but it is another bit of added complexity for multiple databases. Is it worth using?Well, the biggest (maybe only) advantage RetryVFS has over AccessHandlePoolVFS is that RetryVFS supports multiple connections. However, RetryVFS requires the application to cooperate by adding retry code. It's possible for multiple contexts to share a single connection with AccessHandlePoolVFS by adding sharing code. Given that, the RetryVFS advantage doesn't seem like a knockout punch. I'm not seeing a strong case to make RetryVFS an official example so it will stay just a demo for now. I think it's a fine idea and it's cool that it actually works, even with its restrictions. It might be more attractive if applied to a different WASM library or application, like one with less state accumulated in a single application call. |
Beta Was this translation helpful? Give feedback.
-
I came across this post on the SQLite forum:
So I wondered: am I really that silly?
How else can you make a callback from WASM to an asynchronous Javascript function (e.g. OPFS createSyncAccessHandle) using a mechanism like Asyncify or JSPI (the Asyncify killer currently at the prototype stage)? Is there some way to create a VFS with asynchronous methods that won't then require an asynchronous sqlite3_step()?
Well, I failed to come up with one so I suppose I am that silly. However, in the process of failing I did think of a different way to make OPFS access handles appear synchronous to SQLite that doesn't need Asyncify or JSPI, so sqlite3_step() can be synchronous, too.
The basic idea is this: When the VFS receives a method call that requires it to something asynchronous, e.g. open a file or obtain a lock, it remembers what it needs to do (by saving a lambda), and then returns an error. SQLite will return the error to the application, and the application error handling code should call the VFS utility isRetryNeeded(). This function will execute the saved lambda and return a Promise-wrapped boolean that indicates whether it executed a lambda. Now the application can retry the SQLite operation that produced the original error, and this time the VFS will have what it needs to continue synchronously. In concept it's very much like how Asyncify unwinds and rewinds the stack, just done with actual calls.
The basic idea can't work exactly like that in practice. There are complications, and most of them are because after unwinding and rewinding this way, as opposed to Asyncify, the SQLite state isn't always the same as it was. Here are some of the problem cases:
For OPFS the key asynchronous operations are locking, testing if a file exists, opening a file, and deleting a file. I have workarounds or mitigations for all the cases involving these operations that I've thought of. Of course, that doesn't guarantee those workarounds or mitigations actually work, nor does it preclude other problem cases. There's no proof of concept implementation so nothing to verify anything I say. But if there aren't any nasty surprises, here's what I think can be done:
Advantages
Disadvantages
Beta Was this translation helpful? Give feedback.
All reactions