From: Tom Hughes <tom@compton.nu>
Date: Tue, 22 Oct 2019 19:29:00 +0000 (+0100)
Subject: Merge remote-tracking branch 'upstream/pull/2408'
X-Git-Tag: live~3035
X-Git-Url: https://git.openstreetmap.org./rails.git/commitdiff_plain/18c5e1622577374cdd4535d2f06e9d51226ee974?hp=30accae458c747dee3d8c64553004f79c4e27be0

Merge remote-tracking branch 'upstream/pull/2408'
---

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;
diff --git a/db/structure.sql b/db/structure.sql
index 03950502c..6370eb95a 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -184,6 +184,30 @@ END;
 $$;
 
 
+--
+-- Name: xid_to_int4(xid); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION public.xid_to_int4(t xid) RETURNS integer
+    LANGUAGE plpgsql STRICT
+    AS $$
+DECLARE
+  tl bigint;
+  ti int;
+BEGIN
+  tl := t;
+
+  IF tl >= 2147483648 THEN
+    tl := tl - 4294967296;
+  END IF;
+
+  ti := tl;
+
+  RETURN ti;
+END;
+$$;
+
+
 SET default_tablespace = '';
 
 SET default_with_oids = false;