[feature] support more architectures

[nikkicoon]

Is your feature request related to a problem ?

I'm maintaining the package for gotosocial in pkgsrc. It is limited to amd64/x86_64 (at least for NetBSD and FreeBSD, https://gitlab.com/cznic/libc/-/issues/15 , https://gitlab.com/cznic/libc/-/issues/12 , https://gitlab.com/cznic/libc/-/issues/11 ) because of the sqlite implementation being used, if I remember this correctly.
Would moving away from this implementation and supporting more architectures something you'd consider (or alternatively, what else is blocking you)?

[NyaaaWhatsUpDoc]

I don't think we would consider moving away. It's the only Go-only SQLite implementation I'm aware of, and having tried using one with CGO bindings (issues with CGO aside) there's definitely differences between the two causing issues with the way we use the connection concurrently.

However one thing we could consider is supporting build-tags that build a binary with SQLite + PostgreSQL support, SQLite only, PostgreSQL only. Which might solve this situation in some way.

[daenney]

Looking at the cznic/libc repo, it seems support for most of the *BSDs has landed in some initial form, but it got stuck about a year ago. One potential alternative could be something like sqld. It does require running sqld but it allows you to access the SQLite DB using the pg wire protocol. That might be enough so that we can use pg-only in GtS. It would also allow moving away from the current ORM if that's a thing we still want.

That way, we could drop the dependency on modernc.org/sqlite and their C-to-Go compiler, which would potentially allow for supporting all GOOS/GOARCH combinations Go itself supports.

[VyrCossont]

Would the new WASM SQLite option fix this? See #2863, #2938, #2942

[daenney]

Heh, almost. The WASM build doesn't run on the BSDs yet because a locking primitive that's needed there (for SQLite, not GtS) isn't implemented yet.

But if someone gets around to ncruces/go-sqlite3#85 then yes, I think that would solve the alternative architectures. Wazero (the WASM runtime) is a CGo-free implementation in Go and as such should work for any supported architecture by the Go compiler.

[daenney]

Oh actually, it's fine as long as you run with db-sqlite-journal-mode: "TRUNCATE" when using that build. We default to WAL mode for the journal because it tends to be the one with the least performance impact. But in the mean time that's a way to go about it if you want to package it for the BSDs and not be limited to x86 either. It might be good to carry a patch in pkgsrc if you do that to patch the config default to be on the safe side.

[daenney]

@igalic I think this may also be of interest to you?

[tsmethurst]

Once we switch permanently to wasm sqlite3 we can update our CI stuff to build for additional architectures :)

[daenney]

It's worth noting that the WASM SQLite build, by virtue of going through the wazero runtime, only officially supports 32-bit and 64-bit Intel/AMD and ARM. It's a CGo-free runtime, so it works on anything the Go compiler supports, but performance might not be part of the game.

That's not really a problem for us since we don't support stuff like linux/s390, but this also means that something like openbsd/ppc64 might not work well. The same for any RISCv64 or MIPS targets.

That's no different from using the modernc build which comes with more constraints, so in that sense we're not regressing here.

[ncruces]

Hopefully this helps. Or not. Maybe it's even more confusing. But I tried!

[ncruces]

So, I just released v0.20.2.

The new sqlite3_dotlk build tag should, in principle, build and run anywhere that Go builds and runs.

It's single process only (only a single process should access a database file at a time), but coordination between sqlite3_dotlk processes only requires an atomic os.Mkdir. WAL mode also works. Performance won't be amazing, but everything should work.

Caution: you will corrupt your database if use other processes to access databases concurrently with a sqlite3_dotlk process (e.g.: use SQLite3 shell to perform maintenance work, like a vacuum).

In terms of deployment issues, it should be noted that, a sqlite3_dotlk process crash, or hard reboots, power failures, etc, will leave "lock files" around that need to be cleared "manually": dbname.ext.lock and dbname.ext-shm.
Deleting these files (actually: empty directories) when the process is not running is safe, and recommended.

---

That said…

I do NOT recommend the sqlite3_dotlk build tag for platforms that don't need it.

These are better off using vanilla builds of the package:

• Linux, macOS, Windows, FreeBSD, OpenBSD, NetBSD, DragonFlyBSD, and illumos
• on 386, arm, amd64, arm64, riscv64 and ppc64le

All of the above support file locking and WAL mode out-of-the-box.

[ncruces]

Hey! I'd like to understand if there's a specific issue makes you recommend moderncsqlite on FreeBSD amd64?

Nothing against it, whatever works best, I'd just like to understand if there's anything I can fix on my end.

[ncruces]

Just FYI, I've patched wazero to enable the compiler in a bunch of different platforms, and my test suite runs fine almost everywhere except OpenBSD (not sure if that's fixable).

There are some challenges to getting this change submitted to wazero, though.

[tsmethurst]

Oh nice nice nice! That's great news :)

Hey! I'd like to understand if there's a specific issue makes you recommend moderncsqlite on FreeBSD amd64?

At the time we had some reports that people were struggling with backups + file locks or something along those lines, so we recommended moderncsqlite because it seemed to not present those issues. But in future it would be nice if we can just use moderncsqlite for the "nowasm" builds and have everything that can use wasm compiler mode use go-sqlite3 instead, as it makes things simpler.

[ncruces]

Ah, yes. Backing up a WAL database (Litestream, sqlite3_rsync, sqlite3 shell) with a running GoToSocial instance won't work on BSD, sadly.

I'll keep poking on the wazero compiler.

[ncruces]

First step: tetratelabs/wazero#2338

[tsmethurst]

Oooooh hell yeah that's exciting!!! You've been working your arse off, looks like!

[ncruces]

Final step: tetratelabs/wazero#2343
Pretty confident this will be available in the next release.

Anyone who wants to test, we'd welcome the feedback.

[ncruces]

tetratelabs/[email protected]

I'll be making a release shortly, also integrating SQLite 3.47.1.

ncruces/[email protected]