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.