From: Sarah Hoffmann Date: Mon, 19 Jul 2021 08:24:57 +0000 (+0200) Subject: remove special code for pre9.5 postgresql X-Git-Tag: v4.0.0~49^2 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/2c8242c8dfccb74861eb599ecc1064e764bdec99 remove special code for pre9.5 postgresql 9.5 is now the minimum requirement. --- diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index 81299544..62bae94c 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -1,62 +1,62 @@ -- Indices used only during search and update. -- These indices are created only after the indexing process is done. -CREATE INDEX {{sql.if_index_not_exists}} idx_place_addressline_address_place_id +CREATE INDEX IF NOT EXISTS idx_place_addressline_address_place_id ON place_addressline USING BTREE (address_place_id) {{db.tablespace.search_index}}; -CREATE INDEX {{sql.if_index_not_exists}} idx_placex_rank_search +CREATE INDEX IF NOT EXISTS idx_placex_rank_search ON placex USING BTREE (rank_search) {{db.tablespace.search_index}}; -CREATE INDEX {{sql.if_index_not_exists}} idx_placex_rank_address +CREATE INDEX IF NOT EXISTS idx_placex_rank_address ON placex USING BTREE (rank_address) {{db.tablespace.search_index}}; -CREATE INDEX {{sql.if_index_not_exists}} idx_placex_parent_place_id +CREATE INDEX IF NOT EXISTS idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) {{db.tablespace.search_index}} WHERE parent_place_id IS NOT NULL; -CREATE INDEX {{sql.if_index_not_exists}} idx_placex_geometry_reverse_lookupPolygon +CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPolygon ON placex USING gist (geometry) {{db.tablespace.search_index}} WHERE St_GeometryType(geometry) in ('ST_Polygon', 'ST_MultiPolygon') AND rank_address between 4 and 25 AND type != 'postcode' AND name is not null AND indexed_status = 0 AND linked_place_id is null; -CREATE INDEX {{sql.if_index_not_exists}} idx_osmline_parent_place_id +CREATE INDEX IF NOT EXISTS idx_osmline_parent_place_id ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}}; -CREATE INDEX {{sql.if_index_not_exists}} idx_osmline_parent_osm_id +CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id ON location_property_osmline USING BTREE (osm_id) {{db.tablespace.search_index}}; -CREATE INDEX {{sql.if_index_not_exists}} idx_postcode_postcode +CREATE INDEX IF NOT EXISTS idx_postcode_postcode ON location_postcode USING BTREE (postcode) {{db.tablespace.search_index}}; -- Indices only needed for updating. {% if not drop %} - CREATE INDEX {{sql.if_index_not_exists}} idx_placex_pendingsector + CREATE INDEX IF NOT EXISTS idx_placex_pendingsector ON placex USING BTREE (rank_address,geometry_sector) {{db.tablespace.address_index}} WHERE indexed_status > 0; - CREATE INDEX {{sql.if_index_not_exists}} idx_location_area_country_place_id + CREATE INDEX IF NOT EXISTS idx_location_area_country_place_id ON location_area_country USING BTREE (place_id) {{db.tablespace.address_index}}; - CREATE UNIQUE INDEX {{sql.if_index_not_exists}} idx_place_osm_unique + CREATE UNIQUE INDEX IF NOT EXISTS idx_place_osm_unique ON place USING btree(osm_id, osm_type, class, type) {{db.tablespace.address_index}}; {% endif %} -- Indices only needed for search. {% if 'search_name' in db.tables %} - CREATE INDEX {{sql.if_index_not_exists}} idx_search_name_nameaddress_vector + CREATE INDEX IF NOT EXISTS idx_search_name_nameaddress_vector ON search_name USING GIN (nameaddress_vector) WITH (fastupdate = off) {{db.tablespace.search_index}}; - CREATE INDEX {{sql.if_index_not_exists}} idx_search_name_name_vector + CREATE INDEX IF NOT EXISTS idx_search_name_name_vector ON search_name USING GIN (name_vector) WITH (fastupdate = off) {{db.tablespace.search_index}}; - CREATE INDEX {{sql.if_index_not_exists}} idx_search_name_centroid + CREATE INDEX IF NOT EXISTS idx_search_name_centroid ON search_name USING GIST (centroid) {{db.tablespace.search_index}}; {% if postgres.has_index_non_key_column %} - CREATE INDEX {{sql.if_index_not_exists}} idx_placex_housenumber + CREATE INDEX IF NOT EXISTS idx_placex_housenumber ON placex USING btree (parent_place_id) INCLUDE (housenumber) WHERE housenumber is not null; - CREATE INDEX {{sql.if_index_not_exists}} idx_osmline_parent_osm_id_with_hnr + CREATE INDEX IF NOT EXISTS idx_osmline_parent_osm_id_with_hnr ON location_property_osmline USING btree(parent_place_id) INCLUDE (startnumber, endnumber); {% endif %} {% endif %} diff --git a/lib-sql/tiger_import_finish.sql b/lib-sql/tiger_import_finish.sql index a084a2e2..1a9dc2dd 100644 --- a/lib-sql/tiger_import_finish.sql +++ b/lib-sql/tiger_import_finish.sql @@ -1,7 +1,7 @@ --index only on parent_place_id -CREATE INDEX {{sql.if_index_not_exists}} idx_location_property_tiger_parent_place_id_imp +CREATE INDEX IF NOT EXISTS idx_location_property_tiger_parent_place_id_imp ON location_property_tiger_import (parent_place_id) {{db.tablespace.aux_index}}; -CREATE UNIQUE INDEX {{sql.if_index_not_exists}} idx_location_property_tiger_place_id_imp +CREATE UNIQUE INDEX IF NOT EXISTS idx_location_property_tiger_place_id_imp ON location_property_tiger_import (place_id) {{db.tablespace.aux_index}}; GRANT SELECT ON location_property_tiger_import TO "{{config.DATABASE_WEBUSER}}"; diff --git a/lib-sql/tokenizer/legacy_tokenizer_indices.sql b/lib-sql/tokenizer/legacy_tokenizer_indices.sql index 44a2909c..b21f29d7 100644 --- a/lib-sql/tokenizer/legacy_tokenizer_indices.sql +++ b/lib-sql/tokenizer/legacy_tokenizer_indices.sql @@ -1,2 +1,2 @@ -CREATE INDEX {{sql.if_index_not_exists}} idx_word_word_id +CREATE INDEX IF NOT EXISTS idx_word_word_id ON word USING BTREE (word_id) {{db.tablespace.search_index}}; diff --git a/nominatim/db/sql_preprocessor.py b/nominatim/db/sql_preprocessor.py index d756a215..80b89c57 100644 --- a/nominatim/db/sql_preprocessor.py +++ b/nominatim/db/sql_preprocessor.py @@ -41,20 +41,6 @@ def _setup_tablespace_sql(config): return out -def _setup_postgres_sql(conn): - """ Set up a dictionary with various Postgresql/Postgis SQL terms which - are dependent on the database version in use. - """ - out = {} - pg_version = conn.server_version_tuple() - # CREATE INDEX IF NOT EXISTS was introduced in PG9.5. - # Note that you need to ignore failures on older versions when - # using this construct. - out['if_index_not_exists'] = ' IF NOT EXISTS ' if pg_version >= (9, 5, 0) else '' - - return out - - def _setup_postgresql_features(conn): """ Set up a dictionary with various optional Postgresql/Postgis features that depend on the database version. @@ -87,7 +73,6 @@ class SQLPreprocessor: self.env.globals['config'] = config self.env.globals['db'] = db_info - self.env.globals['sql'] = _setup_postgres_sql(conn) self.env.globals['postgres'] = _setup_postgresql_features(conn)