From: Matt Amos <zerebubuth@gmail.com>
Date: Mon, 21 Oct 2019 13:47:46 +0000 (+0100)
Subject: Add pure SQL version of xid_to_int4.
X-Git-Tag: live~2406^2~1
X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/d3d4cff1cf188667cf873d7879d98d8ef86fe474

Add pure SQL version of xid_to_int4.
---

diff --git a/CONFIGURE.md b/CONFIGURE.md
index a78162b67..278d1c398 100644
--- a/CONFIGURE.md
+++ b/CONFIGURE.md
@@ -131,5 +131,5 @@ If you want to deploy The Rails Port for production use, you'll need to make a f
 * The included version of the GPX importer is slow and/or completely inoperable. You should consider using [the high-speed GPX importer](https://git.openstreetmap.org/gpx-import.git/).
 * Make sure you generate the i18n files and precompile the production assets: `RAILS_ENV=production rake i18n:js:export assets:precompile`
 * Make sure the web server user as well as the rails user can read, write and create directories in `tmp/`.
-* If you want to use diff replication then you will need to install the shared library special SQL functions for the `xid_to_int4` function, for which there is no pure SQL alternative. (See the bottom of [INSTALL.md](INSTALL.md))
+* If you want to use diff replication then you might want to consider installing the shared library special SQL functions for the `xid_to_int4` function. A pure SQL version is available, but may become a performance issue on large databases with a high rate of changes. Note that you will need a version of PostgreSQL < 9.6 (yes, _less than_) to use `xid` indexing, whether pure SQL or shared library.
 * If you expect to serve a lot of `/changes` API calls, then you might also want to install the shared library versions of the SQL functions.
diff --git a/db/functions/functions.sql b/db/functions/functions.sql
index 5ed00ea63..4f1f7c716 100644
--- a/db/functions/functions.sql
+++ b/db/functions/functions.sql
@@ -68,3 +68,33 @@ BEGIN
   RETURN (x << zoom) | y;
 END;
 $$ LANGUAGE plpgsql IMMUTABLE;
+
+-- xid_to_int4 converts a PostgreSQL transaction ID (xid) to a 32-bit integer
+-- which can then be used to efficiently find rows which have changed between
+-- two given transactions. This is currently used by Osmosis to extract a
+-- stream of edits for "diff replication" **HOWEVER** this is a pain point, as
+-- (ab)using the xid in this way is _not_ supported or recommended by Postgres
+-- devs. It is preventing us upgrading to PostgreSQL version 10+, and will
+-- hopefully be replaced Real Soon Now.
+--
+-- From the Osmosis distribution by Brett Henderson:
+-- https://github.com/openstreetmap/osmosis/blob/master/package/script/contrib/apidb_0.6_osmosis_xid_indexing.sql
+CREATE OR REPLACE FUNCTION xid_to_int4(t xid)
+  RETURNS integer
+  AS
+$$
+DECLARE
+  tl bigint;
+  ti int;
+BEGIN
+  tl := t;
+
+  IF tl >= 2147483648 THEN
+    tl := tl - 4294967296;
+  END IF;
+
+  ti := tl;
+
+  RETURN ti;
+END;
+$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;