From 479c1575e119d93a0d239fb192ad4ed3d03cad88 Mon Sep 17 00:00:00 2001 From: Andy Allan Date: Wed, 7 Dec 2022 14:57:14 +0000 Subject: [PATCH] Remove tile_for_point SQL functions This removes both the pl/pgsql version and the shared library version of the `tile_for_point` SQL function. This function was only used in some old migrations, and is not required for production usage. Removing this function simplifies the installation and configuration for new developers. These SQL functions are separate from the `tile_for_point` ruby/C function which is part of the quad_tile gem. This function is still used when creating and updating database records. Fixes #3110. --- .github/workflows/tests.yml | 1 - CONFIGURE.md | 2 - INSTALL.md | 46 ------------------- db/functions/.gitignore | 2 - db/functions/Makefile | 25 ---------- db/functions/functions.sql | 42 ----------------- db/functions/quadtile.c | 29 ------------ db/migrate/005_tile_tracepoints.rb | 12 ++--- db/migrate/006_tile_nodes.rb | 27 ++++------- db/migrate/20180204153242_tile_users.rb | 6 +-- db/structure.sql | 35 -------------- docker/postgres/Dockerfile | 5 +- .../postgres/openstreetmap-postgres-init.sh | 3 -- script/vagrant/setup/provision.sh | 15 ------ 14 files changed, 14 insertions(+), 236 deletions(-) delete mode 100644 db/functions/.gitignore delete mode 100644 db/functions/Makefile delete mode 100644 db/functions/functions.sql delete mode 100644 db/functions/quadtile.c diff --git a/.github/workflows/tests.yml b/.github/workflows/tests.yml index aa31089c5..504bb94ab 100644 --- a/.github/workflows/tests.yml +++ b/.github/workflows/tests.yml @@ -40,7 +40,6 @@ jobs: sudo systemctl start postgresql sudo -u postgres createuser -s $(id -un) createdb openstreetmap - psql -f db/functions/functions.sql openstreetmap - name: Configure rails run: | cp config/github.database.yml config/database.yml diff --git a/CONFIGURE.md b/CONFIGURE.md index 3c46e6130..af93dcaef 100644 --- a/CONFIGURE.md +++ b/CONFIGURE.md @@ -144,8 +144,6 @@ If you want to deploy `openstreetmap-website` for production use, you'll need to * It's not recommended to use `rails server` in production. Our recommended approach is to use [Phusion Passenger](https://www.phusionpassenger.com/). Instructions are available for [setting it up with most web servers](https://www.phusionpassenger.com/documentation_and_support#documentation). * Passenger will, by design, use the Production environment and therefore the production database - make sure it contains the appropriate data and user accounts. -* Your production database will also need the extensions and functions installed - see [INSTALL.md](INSTALL.md) * The included version of the map call is quite slow and eats a lot of memory. You should consider using [CGIMap](https://github.com/zerebubuth/openstreetmap-cgimap) instead. * 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 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/INSTALL.md b/INSTALL.md index b9aa1a329..6815bab1f 100644 --- a/INSTALL.md +++ b/INSTALL.md @@ -188,14 +188,6 @@ To create the three databases - for development, testing and production - run: bundle exec rake db:create ``` -### PostgreSQL Functions - -We need to install some special functions into the PostgreSQL database: - -``` -psql -d openstreetmap -f db/functions/functions.sql -``` - ### Database structure To create all the tables, indexes and constraints, run: @@ -232,44 +224,6 @@ Note that the OSM map tiles you see aren't created from your local database - th After installing this software, you may need to carry out some [configuration steps](CONFIGURE.md), depending on your tasks. -# Installing compiled shared library database functions (optional) - -There are special database functions required by a (little-used) API call, the migrations and diff replication. The former two are provided as *either* pure SQL functions or a compiled shared library. The SQL versions are installed as part of the recommended install procedure above and the shared library versions are recommended only if you are running a production server and need the diff replication functionality. - -If you aren't sure which you need, stick with the SQL versions. - -Before installing the functions, it's necessary to install the PostgreSQL server development packages. On Ubuntu this means: - -``` -sudo apt-get install postgresql-server-dev-all -``` - -On Fedora: - -``` -sudo dnf install postgresql-devel -``` - -The library then needs compiling. - -``` -cd db/functions -make libpgosm.so -cd ../.. -``` - -If you previously installed the SQL versions of these functions, we'll need to delete those before adding the new ones: - -``` -psql -d openstreetmap -c "DROP FUNCTION IF EXISTS tile_for_point" -``` - -Then we create the functions within each database. We're using `pwd` to substitute in the current working directory, since PostgreSQL needs the full path. - -``` -psql -d openstreetmap -c "CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '`pwd`/db/functions/libpgosm', 'tile_for_point' LANGUAGE C STRICT" -``` - # Ruby development install and versions (optional) For simplicity, this document explains how to install all the website dependencies as "system" dependencies. While this is simpler, and usually faster, you might want more control over the process or the ability to install multiple different versions of software alongside eachother. For many developers, [`rbenv`](https://github.com/rbenv/rbenv) is the easiest way to manage multiple different Ruby versions on the same computer - with the added advantage that the installs are all in your home directory, so you don't need administrator permissions. diff --git a/db/functions/.gitignore b/db/functions/.gitignore deleted file mode 100644 index 9d22eb46a..000000000 --- a/db/functions/.gitignore +++ /dev/null @@ -1,2 +0,0 @@ -*.o -*.so diff --git a/db/functions/Makefile b/db/functions/Makefile deleted file mode 100644 index 9388e2d23..000000000 --- a/db/functions/Makefile +++ /dev/null @@ -1,25 +0,0 @@ -BUNDLE ?= bundle -PG_CONFIG ?= pg_config -DESTDIR ?= . - -QTDIR=$(shell ${BUNDLE} show quad_tile | tail -n 1)/ext/quad_tile - -OS=$(shell uname -s) -ifeq (${OS},Darwin) - LDFLAGS=-bundle -else - LDFLAGS=-shared -endif - -all: ${DESTDIR}/libpgosm.so - -clean: - $(RM) ${DESTDIR}/*.so ${DESTDIR}/*.o - -${DESTDIR}/libpgosm.so: ${DESTDIR}/quadtile.o - cc ${LDFLAGS} -o $@ $^ - -${DESTDIR}/%.o: %.c - cc -I `${PG_CONFIG} --includedir` -I `${PG_CONFIG} --includedir-server` -I${QTDIR} -fPIC -O3 -DUSE_PGSQL -c -o $@ $< - -${DESTDIR}/quadtile.o: ${QTDIR}/quad_tile.h diff --git a/db/functions/functions.sql b/db/functions/functions.sql deleted file mode 100644 index 97e44f0ce..000000000 --- a/db/functions/functions.sql +++ /dev/null @@ -1,42 +0,0 @@ --------------------------------------------------------------------------------- --- SQL versions of the C database functions. --- --- Pure pl/pgsql versions are *slower* than the C versions, and not recommended --- for production use. However, they are significantly easier to install, and --- require fewer dependencies. --------------------------------------------------------------------------------- - --- tile_for_point function returns a Morton-encoded integer representing a z16 --- tile which contains the given (scaled_lon, scaled_lat) coordinate. Note that --- these are passed into the function as (lat, lon) and should be scaled by --- 10^7. --- --- The Morton encoding packs two dimensions down to one with fairly good --- spatial locality, and can be used to index points without the need for a --- proper 2D index. -CREATE OR REPLACE FUNCTION tile_for_point(scaled_lat int4, scaled_lon int4) - RETURNS int8 - AS $$ -DECLARE - x int8; -- quantized x from lon, - y int8; -- quantized y from lat, -BEGIN - x := round(((scaled_lon / 10000000.0) + 180.0) * 65535.0 / 360.0); - y := round(((scaled_lat / 10000000.0) + 90.0) * 65535.0 / 180.0); - - -- these bit-masks are special numbers used in the bit interleaving algorithm. - -- see https://graphics.stanford.edu/~seander/bithacks.html#InterleaveBMN - -- for the original algorithm and more details. - x := (x | (x << 8)) & 16711935; -- 0x00FF00FF - x := (x | (x << 4)) & 252645135; -- 0x0F0F0F0F - x := (x | (x << 2)) & 858993459; -- 0x33333333 - x := (x | (x << 1)) & 1431655765; -- 0x55555555 - - y := (y | (y << 8)) & 16711935; -- 0x00FF00FF - y := (y | (y << 4)) & 252645135; -- 0x0F0F0F0F - y := (y | (y << 2)) & 858993459; -- 0x33333333 - y := (y | (y << 1)) & 1431655765; -- 0x55555555 - - RETURN (x << 1) | y; -END; -$$ LANGUAGE plpgsql IMMUTABLE; diff --git a/db/functions/quadtile.c b/db/functions/quadtile.c deleted file mode 100644 index 472e8cb1e..000000000 --- a/db/functions/quadtile.c +++ /dev/null @@ -1,29 +0,0 @@ -#include -#include -#include -#include - -Datum -tile_for_point(PG_FUNCTION_ARGS) -{ - double lat = PG_GETARG_INT32(0) / 10000000.0; - double lon = PG_GETARG_INT32(1) / 10000000.0; - - PG_RETURN_INT64(xy2tile(lon2x(lon), lat2y(lat))); -} - -PG_FUNCTION_INFO_V1(tile_for_point); - -/* - * To bind this into PGSQL, try something like: - * - * CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8 - * AS '/path/to/openstreetmap-website/db/functions/libpgosm', 'tile_for_point' - * LANGUAGE C STRICT; - * - * (without all the *s) - */ - -#ifdef PG_MODULE_MAGIC -PG_MODULE_MAGIC; -#endif diff --git a/db/migrate/005_tile_tracepoints.rb b/db/migrate/005_tile_tracepoints.rb index 0e557067d..293b235c6 100644 --- a/db/migrate/005_tile_tracepoints.rb +++ b/db/migrate/005_tile_tracepoints.rb @@ -8,14 +8,10 @@ class TileTracepoints < ActiveRecord::Migration[4.2] add_index "gps_points", ["tile"], :name => "points_tile_idx" remove_index "gps_points", :name => "points_idx" - if ENV["USE_DB_FUNCTIONS"] - Tracepoint.update_all("latitude = latitude * 10, longitude = longitude * 10, tile = tile_for_point(latitude * 10, longitude * 10)") - else - Tracepoint.all.each do |tp| - tp.latitude = tp.latitude * 10 - tp.longitude = tp.longitude * 10 - tp.save! - end + Tracepoint.all.each do |tp| + tp.latitude = tp.latitude * 10 + tp.longitude = tp.longitude * 10 + tp.save! end end diff --git a/db/migrate/006_tile_nodes.rb b/db/migrate/006_tile_nodes.rb index 7fdb34e57..a7f9b1af0 100644 --- a/db/migrate/006_tile_nodes.rb +++ b/db/migrate/006_tile_nodes.rb @@ -8,25 +8,14 @@ class TileNodes < ActiveRecord::Migration[4.2] end def self.upgrade_table(from_table, to_table, model) - if ENV["USE_DB_FUNCTIONS"] - execute <<-SQL.squish - INSERT INTO #{to_table} (id, latitude, longitude, user_id, visible, tags, timestamp, tile) - SELECT id, ROUND(latitude * 10000000), ROUND(longitude * 10000000), - user_id, visible, tags, timestamp, - tile_for_point(CAST(ROUND(latitude * 10000000) AS INTEGER), - CAST(ROUND(longitude * 10000000) AS INTEGER)) - FROM #{from_table} - SQL - else - execute <<-SQL.squish - INSERT INTO #{to_table} (id, latitude, longitude, user_id, visible, tags, timestamp, tile) - SELECT id, ROUND(latitude * 10000000), ROUND(longitude * 10000000), - user_id, visible, tags, timestamp, 0 - FROM #{from_table} - SQL - - model.all.each(&:save!) - end + execute <<-SQL.squish + INSERT INTO #{to_table} (id, latitude, longitude, user_id, visible, tags, timestamp, tile) + SELECT id, ROUND(latitude * 10000000), ROUND(longitude * 10000000), + user_id, visible, tags, timestamp, 0 + FROM #{from_table} + SQL + + model.all.each(&:save!) end def self.downgrade_table(from_table, to_table) diff --git a/db/migrate/20180204153242_tile_users.rb b/db/migrate/20180204153242_tile_users.rb index 917948d43..53cea91cf 100644 --- a/db/migrate/20180204153242_tile_users.rb +++ b/db/migrate/20180204153242_tile_users.rb @@ -6,11 +6,7 @@ class TileUsers < ActiveRecord::Migration[5.1] add_column :users, :home_tile, :bigint add_index :users, [:home_tile], :name => "users_home_idx" - if ENV["USE_DB_FUNCTIONS"] - User.update_all("home_tile = tile_for_point(cast(round(home_lat * #{GeoRecord::SCALE}) as integer), cast(round(home_lon * #{GeoRecord::SCALE}) as integer))") - else - User.all.each(&:save!) - end + User.all.each(&:save!) end def down diff --git a/db/structure.sql b/db/structure.sql index dd0165ee0..89874d779 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -107,39 +107,6 @@ CREATE TYPE public.user_status_enum AS ENUM ( 'deleted' ); - --- --- Name: tile_for_point(integer, integer); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION public.tile_for_point(scaled_lat integer, scaled_lon integer) RETURNS bigint - LANGUAGE plpgsql IMMUTABLE - AS $$ -DECLARE - x int8; -- quantized x from lon, - y int8; -- quantized y from lat, -BEGIN - x := round(((scaled_lon / 10000000.0) + 180.0) * 65535.0 / 360.0); - y := round(((scaled_lat / 10000000.0) + 90.0) * 65535.0 / 180.0); - - -- these bit-masks are special numbers used in the bit interleaving algorithm. - -- see https://graphics.stanford.edu/~seander/bithacks.html#InterleaveBMN - -- for the original algorithm and more details. - x := (x | (x << 8)) & 16711935; -- 0x00FF00FF - x := (x | (x << 4)) & 252645135; -- 0x0F0F0F0F - x := (x | (x << 2)) & 858993459; -- 0x33333333 - x := (x | (x << 1)) & 1431655765; -- 0x55555555 - - y := (y | (y << 8)) & 16711935; -- 0x00FF00FF - y := (y | (y << 4)) & 252645135; -- 0x0F0F0F0F - y := (y | (y << 2)) & 858993459; -- 0x33333333 - y := (y | (y << 1)) & 1431655765; -- 0x55555555 - - RETURN (x << 1) | y; -END; -$$; - - SET default_tablespace = ''; SET default_table_access_method = heap; @@ -3472,5 +3439,3 @@ INSERT INTO "schema_migrations" (version) VALUES ('7'), ('8'), ('9'); - - diff --git a/docker/postgres/Dockerfile b/docker/postgres/Dockerfile index 8358e6e7f..9e7479848 100644 --- a/docker/postgres/Dockerfile +++ b/docker/postgres/Dockerfile @@ -1,7 +1,4 @@ FROM postgres:11 -# Add db init script to install OSM-specific Postgres functions/extensions. +# Add db init script to install OSM-specific Postgres user. ADD docker/postgres/openstreetmap-postgres-init.sh /docker-entrypoint-initdb.d/ - -# Custom database functions are in a SQL file. -ADD db/functions/functions.sql /usr/local/share/osm-db-functions.sql diff --git a/docker/postgres/openstreetmap-postgres-init.sh b/docker/postgres/openstreetmap-postgres-init.sh index 53c0ba454..511d2d440 100755 --- a/docker/postgres/openstreetmap-postgres-init.sh +++ b/docker/postgres/openstreetmap-postgres-init.sh @@ -7,6 +7,3 @@ psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" <<-EOSQL CREATE USER openstreetmap SUPERUSER PASSWORD 'openstreetmap'; GRANT ALL PRIVILEGES ON DATABASE openstreetmap TO openstreetmap; EOSQL - -# Define custom functions -psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -f "/usr/local/share/osm-db-functions.sql" openstreetmap diff --git a/script/vagrant/setup/provision.sh b/script/vagrant/setup/provision.sh index bfe12a3c6..e10e144a8 100644 --- a/script/vagrant/setup/provision.sh +++ b/script/vagrant/setup/provision.sh @@ -38,21 +38,6 @@ if [ "$db_user_exists" != "1" ]; then sudo -u vagrant createdb -E UTF-8 -O vagrant osm_test fi - -# install PostgreSQL functions -sudo -u vagrant psql -d openstreetmap -f db/functions/functions.sql -################################################################################ -# *IF* you want a vagrant image which supports replication (or perhaps you're -# using this script to provision some other server and want replication), then -# uncomment the following lines (until popd) and comment out the one above -# (functions.sql). -################################################################################ -#pushd db/functions -#sudo -u vagrant make -#sudo -u vagrant psql openstreetmap -c "CREATE OR REPLACE FUNCTION tile_for_point(int4, int4) RETURNS int8 AS '/srv/openstreetmap-website/db/functions/libpgosm.so', 'tile_for_point' LANGUAGE C STRICT" -#popd - - # set up sample configs if [ ! -f config/database.yml ]; then sudo -u vagrant cp config/example.database.yml config/database.yml -- 2.39.5