Define SQLite compatibility policy

[richvdh]

We don't appear to have a defined compatibility policy for SQLite. https://matrix-org.github.io/synapse/latest/deprecation_policy.html#deprecation-policy-for-platform-dependencies doesn't mention it.

I don't think we can apply the "upstream support life cycle" for SQLite, since AFAIK the upstream SQLite developers do not provide bug fixes for anything except the most recent minor version. (Currently, that is 3.38.5, so we would require SQLite 3.38.x, which is clearly impractical.)

Typically, your SQLite version is determined by your distribution. (The sqlite3 python module is part of the stdlib, which is provided by the distribution, and cannot be upgraded via a virtualenv. Normally the _sqlite3.so is dynamically linked against the distribution's libsqlite3.)

Hence, "which version of SQLite do we need to support" tends to be defined by "which distributions do we aim to support", which itself doesn't have a clear answer (normally: "anything that has a sufficiently modern python".)

[richvdh]

For a long time, our minimum sqlite version was 3.7, for compatibility with CentOS 7 - and AFAIK, we have never explicitly announced that we're going to drop compatibility with that distribution.

However, I think it would be entirely reasonable to do so, and I strongly suspect that we've already done so in practice.

Another historical constraint was Ubuntu 18.04 LTS, which has 3.22.0, but also has an unsupported Python version (3.6.5).

[richvdh]

In terms of what is tested in CI, we run sytest on buster (which has sqlite 3.27), but only in postgres mode 🤦. Our oldest platform where we use sqlite is (for both trial and sytest) Ubuntu Focal 20.04LTS, which has SQLite 3.31, so there's a strong argument that that is the oldest version we support in practice.

A survey of distros named in https://matrix-org.github.io/synapse/latest/setup/installation.html#installing-synapse:

• Arch has 3.38.5
• Debian 10 (Buster, oldstable) has 3.27.2
• Debian 11 (Bullseye, stable) has 3.34.1
• Fedora 35 has 3.36
• FreeBSD 12 has 3.37.2
• NetBSD 8.2 has 3.35.2
• openSUSE Leap 15.3 has 3.28.0.
• Ubuntu 18.04LTS has 3.22.0, but also an unsupported python version.
• Ubuntu 22.04LTS has 3.31.1.

(I've not checked that their python distros are dynamically linked against libsqlite: it's possible they are statically linked against a different version.)

Accordingly I think it would be entirely reasonable to declare 3.27.2 to be our minimum requirement. However, I'd love for us to define this in a less backwards way, somehow.

[DMRobertson]

However, I'd love for us to define this in a less backwards way, somehow.

FWIW my first thought was "we support the version of sqlite in Debian stable", and rely on Debian's conservativeness to ensure we don't screw people over. I think that's consistent with the fact that we produce Debs but not e.g. RPMs for Fedora et al.

[richvdh]

FWIW my first thought was "we support the version of sqlite in Debian stable"

... which implies we don't support oldstable? That's kinda ok once stable has been, uh, stable for a while, but gives a bit of a sharp cutoff at debian release time.

I think that's consistent with the fact that we produce Debs but not e.g. RPMs for Fedora et al.

It's worth noting that we do produce debs for oldstable (currently, at least; it's defined more by "does it have a recent enough python" than anything more explicit).

"We support the version of sqlite in Debian oldstable" sounds like a not-terrible definition.

[DMRobertson]

Oldstable rather than stable, yes, sorry.

[erikjohnston]

We can also check the phone home stats to see what SQLite versions are currently in use: https://grafana.matrix.org/d/7A0xoAMnk/homeserver-versions?orgId=1&from=now-7d&to=now&viewPanel=6, we could also filter that by Synapse version. It currently shows a small minority on 3.22.0 and earlier.

Though another thing to note here is that we don't heavily recommend for production use that you use SQLite, so I think we might get away with being a bit stricter/looser (depending on how you view things) with our compatibility policy for SQLite

[richvdh]

oh HELLO: https://github.com/matrix-org/synapse/pull/9766/files#diff-c8a83d17fa29e88528380dc6514c58da23572f93dab57bfc3fbdb9adca7fbd1fR71

So, our current minimum version turns out to be 3.22, though I don't think we've tested with that since #11683, which moved our CI from bionic to focal.

I think we should bump this to 3.27, and try and actually test with that version. (One big advantage is that SQLite 3.23 fixes the "FALSE is truthy" footgun.)

[erikjohnston]

Plus it lets us use native upserts:

synapse/synapse/storage/engines/sqlite.py

Lines 51 to 57 in 24c3974

	@property
	def can_native_upsert(self) -> bool:
	"""
	Do we support native UPSERTs? This requires SQLite3 3.24+, plus some
	more work we haven't done yet to tell what was inserted vs updated.
	"""
	return sqlite3.sqlite_version_info >= (3, 24, 0)

[richvdh]

"We support the version of sqlite in Debian oldstable" sounds like a not-terrible definition.

As a point of reference, that would have meant dropping support for Ubuntu 18.04 rather earlier than we did. Following the suggested scheme, we would have supported SQLite 3.16.2 (as used in Debian 9/Stretch) until Bullseye was released in August 2021, at which point the constraint would have been 3.27 (as used in Debian 10/Buster), which postdates Ubuntu 18.04. As it was, we dropped support for Ubuntu 18.04 when Python 3.6 went out of support in January 2022.

A release date for Bookworm has not yet been set, but we can expect a similar problem then: it is likely to happen in Summer 2023, at which point our supported SQLite version would become 3.34 (as used by Bullseye). That is insufficient for Ubuntu 20.04, which uses Python 3.8, so would otherwise go out of support in October 2024.

We're not duty-bound to support 3-year-old Ubuntu releases, and as Erik notes: if you care about this stuff, you probably ought to be on Postgres anyway. But I wanted to record that "We support the version of sqlite in Debian oldstable", while sounding generous, is actually a rather tighter constraint than we have worked to in the past.

[richvdh]

Steps here:

• add words to https://matrix-org.github.io/synapse/latest/deprecation_policy.html stating our policy (we support the version of sqlite in Debian oldstable).
  • Define SQLite compat policy #13728
• Draw attention to that statement in a release announcement, also mentioning that the release where the announcement will be the last to support SQLite < 3.27. (Hopefully this won't be controversial, since all the distros with modern python have modern SQLite).
  • Add minimum version bump for sqlite to the release notes #13742
  • Call out changes in Synapse 1.68 requirements when announcing Synapse 1.67 #13744
• Actually bump the minimum requirement in Sqlite3Engine
• remove can_native_upsert from the codebase
  • The above covered by Require SQLite >= 3.27.0 #13760

[DMRobertson]

• remove can_native_upsert from the codebase

Being able to use sqlite upserts would mean that we don't have to insert lock values into rows in make_full_schema. See #6311 (comment) and #6394.

[DMRobertson]

https://repology.org/project/sqlite/versions

• Fedora 35: 3.36.0
• Debian 10 (Buster, current oldstable): 3.27.2
• Ubuntu 20.04 (Focal): 3.31.1. Older LTS versions exist but we don't support them.
• Arch BTW: 3.39.2
• Gentoo: 3.39.2
• OpenBSD: 3.39.2
• FreeBSD: 3.39.2
• NetBSD/pkgsrc: 3.39.2
• Alpine 3.13: 3.34.1 Older versions exist but are EOL
  I think that's representative enough.

[gdt]

While pkgsrc has recent sqlite3 as repology says, python sqlite support is linked against base system sqlite3 which is older. NetBSD 9 has 3.26.0, so if you can set that as the limit rather than 3.27.0, it saves a fair bit of pain for anyone using sqlite3 (even though they should be using postgresql anyway, which is what I do).

[erikjohnston]

For native upserts we only need 3.24, so 3.26.0 seems plausible?

[DMRobertson]

I'd be okay with 3.26, but according to https://github.com/NetBSD/pkgsrc/blob/a8c98c2eccd6f61de36bb2a2401fd25ca4dc19ac/chat/matrix-synapse/MESSAGE#L14-L16 pkgsrc expects you to use its version of sqlite. In which case I think I'd prefer to stick with 3.27: don't want to introduce a policy only to not follow it.

@gdt does that sound reasonable? (Sorry, I don't completely follow the details of pkgsrc works.)

[gdt]

Yes, I had forgotten that there are other issues. This is what happens when something becomes important enough to be in the base system, and at the same time has way too many knobs becaused it's designed to be embedded one program at a time rather than as a system library :-(. I personally lean to "sqlite3 is not reasonable for significant workloads (volume or complexity), so just us pgsq". (I don't mean to criticize sqlite as "need sql for some program and don't want the user to have to think about it".)

It might also be good to specify, if it isn't really clear, which sqlite3 features need to be turned on. The issue is that sqlite does not have a single defined API/ABI, given the ability to build with and without various options and the apparent lack of a base specification of "this is sqlite3" vs "this is sqlite3 minus a standard feature". But, it might be just NetBSD base that doesn't have what is needed.

In general, though, I think it is best to only require what needs to be required, because there are always other environments, even if there isn't CI all the way back. It's extra effort when things work to have to patch a dependency expression (similar to the faux cryptography requirement).

[DMRobertson]

It might also be good to specify, if it isn't really clear, which sqlite3 features need to be turned on.

Taking a very quick look at https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off , the only option I can see that we make use of is SQLITE_ENABLE_FTS4 for full text search, in these two places:

synapse/synapse/storage/schema/main/delta/42/user_dir.py

Lines 64 to 65 in 25f43fa

	CREATE VIRTUAL TABLE user_directory_search
	USING fts4 ( user_id, value );

synapse/synapse/storage/schema/main/delta/25/fts.py

Lines 39 to 40 in 25f43fa

	"CREATE VIRTUAL TABLE event_search"
	" USING fts4 ( event_id, room_id, sender, key, value )"

See also https://www.sqlite.org/fts3.html#compiling_and_enabling_fts3_and_fts4

In general, we're less interested in compile-time toggle-able features; we're more interested in new things we can query for and relevant bugfixes.

[DMRobertson]

@gdt does that sound reasonable? (Sorry, I don't completely follow the details of pkgsrc works.)

Yes, I had forgotten that there are other issues. [...]

To be explicit: I understand this as "yes, this sounds reasonable" with additional commentary. (And commentary that I'm very sympathetic too at that!)

[gdt]

Yes, I did mean to say that it is a reasonable approach given the situation.

And I see that that synapse is depending on a feature that sqlite documents as not enabled by default. But I can see how you got there...

[DMRobertson]

Checklist in #12983 (comment) complete; closing.