[Tile-serving] [openstreetmap/osm2pgsql] Introduce separate COPY threads (#914)

[Sarah Hoffmann]

This PR reorganizes the way PostgreSQL tables are filled. So far we had a mixture of COPY and INSERT to add and update data. This PR introduces a common class for COPYing all data into the database and gets rid of INSERT entirely.

Data that should be inserted is collected in copy buffers which are COPYed into the database by a separate thread. To make this work with updates, the copy buffer also saves rows to be deleted. When such rows are available, the copy thread shortly stops the COPY, issues a single DELETE statement deleting all pending rows and then continues COPYing the buffered data. If the copy buffers are large enough, this should still be more efficient than the current DELETE/INSERT approach with prepared statements.

Copy threads can be used for multiple tables at the same time. When they detect that the next pending buffer is for a different table then it stops the ongoing copy and starts a new one for the new table.

The current implementation starts two copy threads, one for middle and one for all outputs. Each output table has its own db_copy_mgr, so that each table can queue data for copying independently. The processing step spawns one copy thread per processing thread serving the output tables. Middle is not written at this stage, so there is no copy thread needed for it.

This PR only provides a functional implementation. I haven't looked into optimising anything yet. In particular, the size of the copy buffers is currently fixed and we can now optimise the number of connections to the database for reading to reduce the number of DB connections to a maximum of 2 * parallel threads.
You can view, comment on, or merge this pull request online at:

  https://github.com/openstreetmap/osm2pgsql/pull/914

-- Commit Summary --

  * introduce copy object responsible for database loading
  * middle-pgsql: adapt table setup and teardown to db-copy use
  * initial implementation of buffer filling functions in db-copy
  * middle-pgsql: fix member entries in relation table
  * remove exec sql function from db_copy class
  * convert output-gazetteer to use db copy manager
  * move table to db_copy classes
  * avoid unnecessary convertion of way_area
  * delete unused stuff
  * add missing virtual desctructor

-- File Changes --

    M CMakeLists.txt (2)
    A db-copy.cpp (238)
    A db-copy.hpp (353)
    M expire-tiles.cpp (2)
    M gazetteer-style.cpp (146)
    M gazetteer-style.hpp (8)
    M middle-pgsql.cpp (527)
    M middle-pgsql.hpp (52)
    M osm2pgsql.cpp (2)
    M osmdata.cpp (5)
    M output-gazetteer.cpp (194)
    M output-gazetteer.hpp (77)
    M output-multi.cpp (28)
    M output-multi.hpp (9)
    M output-null.cpp (14)
    M output-null.hpp (6)
    M output-pgsql.cpp (67)
    M output-pgsql.hpp (10)
    M output.cpp (27)
    M output.hpp (4)
    M pgsql.cpp (16)
    M pgsql.hpp (1)
    M table.cpp (455)
    M table.hpp (34)
    M taginfo.cpp (12)
    M tests/CMakeLists.txt (2)
    M tests/common.hpp (13)
    M tests/middle-tests.hpp (22)
    M tests/test-hstore-match-only.cpp (3)
    M tests/test-middle-flat.cpp (56)
    M tests/test-middle-pgsql.cpp (71)
    M tests/test-middle-ram.cpp (7)
    M tests/test-options-projection.cpp (5)
    M tests/test-output-multi-line-storage.cpp (2)
    M tests/test-output-multi-line.cpp (4)
    M tests/test-output-multi-point-multi-table.cpp (4)
    M tests/test-output-multi-point.cpp (4)
    M tests/test-output-multi-poly-trivial.cpp (4)
    M tests/test-output-multi-polygon.cpp (4)
    M tests/test-output-multi-tags.cpp (4)
    M tests/test-output-pgsql-area.cpp (4)
    M tests/test-output-pgsql-schema.cpp (5)
    M tests/test-output-pgsql-tablespace.cpp (4)
    M tests/test-output-pgsql-validgeom.cpp (5)
    M tests/test-output-pgsql-z_order.cpp (5)
    M tests/test-output-pgsql.cpp (32)
    M tests/test-parse-diff.cpp (3)
    M tests/test-parse-extra-args.cpp (3)
    M tests/test-parse-xml2.cpp (3)
    D tests/test-pgsql-escape.cpp (24)
    M wkb.hpp (10)

-- Patch Links --

https://github.com/openstreetmap/osm2pgsql/pull/914.patch
https://github.com/openstreetmap/osm2pgsql/pull/914.diff

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/914
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20190223/cbe8d2fa/attachment.html>