middle-pgsql.cpp

/* Implements the mid-layer processing for osm2pgsql
 * using several PostgreSQL tables
 *
 * This layer stores data read in from the planet.osm file
 * and is then read by the backend processing code to
 * emit the final geometry-enabled output formats
*/

#include "config.h"

#ifdef _WIN32
using namespace std;
#endif

#ifdef _MSC_VER
#define alloca _alloca
#endif

#include <stdexcept>
#include <unordered_map>

#include <cassert>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <ctime>
#include <future>

#include <boost/format.hpp>

#include <libpq-fe.h>

#include "middle-pgsql.hpp"
#include "node-persistent-cache.hpp"
#include "node-ram-cache.hpp"
#include "options.hpp"
#include "osmtypes.hpp"
#include "output-pgsql.hpp"
#include "pgsql.hpp"
#include "util.hpp"

enum table_id {
    t_node, t_way, t_rel
} ;

middle_pgsql_t::table_desc::table_desc(const char *name_,
                                       const char *start_,
                                       const char *create_,
                                       const char *create_index_,
                                       const char *prepare_,
                                       const char *prepare_intarray_,
                                       const char *copy_,
                                       const char *analyze_,
                                       const char *stop_,
                                       const char *array_indexes_)
    : name(name_),
      start(start_),
      create(create_),
      create_index(create_index_),
      prepare(prepare_),
      prepare_intarray(prepare_intarray_),
      copy(copy_),
      analyze(analyze_),
      stop(stop_),
      array_indexes(array_indexes_),
      copyMode(0),
      transactionMode(0),
      sql_conn(nullptr)
{}

namespace {
// Decodes a portion of an array literal from postgres */
// Argument should point to beginning of literal, on return points to delimiter */
inline const char *decode_upto( const char *src, char *dst )
{
  int quoted = (*src == '"');
  if( quoted ) src++;

  while( quoted ? (*src != '"') : (*src != ',' && *src != '}') )
  {
    if( *src == '\\' )
    {
      switch( src[1] )
      {
        case 'n': *dst++ = '\n'; break;
        case 't': *dst++ = '\t'; break;
        default: *dst++ = src[1]; break;
      }
      src+=2;
    }
    else
      *dst++ = *src++;
  }
  if( quoted ) src++;
  *dst = 0;
  return src;
}

void pgsql_parse_tags(const char *string, taglist_t &tags)
{
  char key[1024];
  char val[1024];

  if( *string == '\0' )
    return;

  if( *string++ != '{' )
    return;
  while( *string != '}' )
  {
    string = decode_upto( string, key );
    // String points to the comma */
    string++;
    string = decode_upto( string, val );
    // String points to the comma or closing '}' */
    tags.push_back(tag_t(key, val));
    if( *string == ',' )
      string++;
  }
}

// Parses an array of integers */
void pgsql_parse_nodes(const char *string, idlist_t &nds)
{
  if( *string++ != '{' )
    return;

  while( *string != '}' )
  {
    char *ptr;
    nds.push_back(strtoosmid( string, &ptr, 10 ));
    string = ptr;
    if( *string == ',' )
      string++;
  }
}

int pgsql_endCopy(middle_pgsql_t::table_desc *table)
{
    // Terminate any pending COPY */
    if (table->copyMode) {
        PGconn *sql_conn = table->sql_conn;
        int stop = PQputCopyEnd(sql_conn, nullptr);
        if (stop != 1) {
            fprintf(stderr, "COPY_END for %s failed: %s\n", table->copy, PQerrorMessage(sql_conn));
            util::exit_nicely();
        }

        PGresult *res = PQgetResult(sql_conn);
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            fprintf(stderr, "COPY_END for %s failed: %s\n", table->copy, PQerrorMessage(sql_conn));
            PQclear(res);
            util::exit_nicely();
        }
        PQclear(res);
        table->copyMode = 0;
    }
    return 0;
}
} // anonymous namespace


void middle_pgsql_t::buffer_store_nodes(idlist_t const &nds)
{
    if (nds.size() == 0) {
        copy_buffer += "{}";
        return;
    }

    copy_buffer += "{";

    for (auto const &it : nds) {
        copy_buffer += std::to_string(it);
        copy_buffer += ',';
    }

    copy_buffer[copy_buffer.size() - 1] = '}';
}

void middle_pgsql_t::buffer_store_string(std::string const &in, bool escape)
{
    for (char const c: in) {
        switch (c) {
            case '"':
                if (escape) copy_buffer += "\\";
                copy_buffer += "\\\"";
                break;
            case '\\':
                if (escape) copy_buffer += "\\\\";
                copy_buffer += "\\\\";
                break;
            case '\n':
                if (escape) copy_buffer += "\\";
                copy_buffer += "\\n";
                break;
            case '\r':
                if (escape) copy_buffer += "\\";
                copy_buffer += "\\r";
                break;
            case '\t':
                if (escape) copy_buffer += "\\";
                copy_buffer += "\\t";
                break;
            default:
                copy_buffer += c;
                break;
        }
    }
}

// escape means we return '\N' for copy mode, otherwise we return just nullptr
void middle_pgsql_t::buffer_store_tags(taglist_t const &tags, bool escape)
{
    copy_buffer += "{";

    bool first = true;
    for (auto const &it : tags) {
        if (!first) {
            copy_buffer += ',';
        }
        copy_buffer += "\"";
        buffer_store_string(it.key, escape);
        copy_buffer += "\",\"";
        buffer_store_string(it.value, escape);
        copy_buffer += '"';

        first = false;
    }

    copy_buffer += "}";
}

void middle_pgsql_t::buffer_correct_params(char const **param, size_t size)
{
    if (copy_buffer.c_str() != param[0]) {
        auto diff = copy_buffer.c_str() - param[0];
        for (size_t i = 0; i < size; ++i) {
            if (param[i]) {
                param[i] += diff;
            }
        }
    }
}

void middle_pgsql_t::local_nodes_set(osmid_t id, double lat, double lon,
                                     const taglist_t &tags)
{
    copy_buffer.reserve(tags.size() * 24 + 64);

    bool copy = node_table->copyMode;
    char delim = copy ? '\t' : '\0';
    const char *paramValues[4] = { copy_buffer.c_str(), };

    copy_buffer = std::to_string(id);
    copy_buffer += delim;

#ifdef FIXED_POINT
    ramNode n(lon, lat);
    paramValues[1] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(n.int_lat());
    copy_buffer += delim;

    paramValues[2] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(n.int_lon());
    copy_buffer += delim;
#else
    paramValues[1] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(lat);
    copy_buffer += delim;

    paramValues[2] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(lon);
    copy_buffer += delim;
#endif

    if (tags.size() == 0) {
        paramValues[3] = nullptr;
        copy_buffer += "\\N";
    } else {
        paramValues[3] = paramValues[0] + copy_buffer.size();
        buffer_store_tags(tags, copy);
    }

    if (copy) {
        copy_buffer += '\n';
        pgsql_CopyData(__FUNCTION__, node_table->sql_conn, copy_buffer);
    } else {
        buffer_correct_params(paramValues, 4);
        pgsql_execPrepared(node_table->sql_conn, "insert_node", 4,
                           (const char * const *)paramValues, PGRES_COMMAND_OK);
    }
}

// This should be made more efficient by using an IN(ARRAY[]) construct */
size_t middle_pgsql_t::local_nodes_get_list(nodelist_t &out, const idlist_t nds) const
{
    assert(out.empty());

    char tmp[16];

    char *tmp2 = static_cast<char *>(malloc(sizeof(char) * nds.size() * 16));
    if (tmp2 == nullptr) return 0; //failed to allocate memory, return */


    // create a list of ids in tmp2 to query the database  */
    sprintf(tmp2, "{");
    int countDB = 0;
    for(idlist_t::const_iterator it = nds.begin(); it != nds.end(); ++it) {
        // Check cache first */
        osmNode loc;
        if (cache->get(&loc, *it) == 0) {
            out.push_back(loc);
            continue;
        }

        countDB++;
        // Mark nodes as needing to be fetched from the DB */
        out.push_back(osmNode());

        snprintf(tmp, sizeof(tmp), "%" PRIdOSMID ",", *it);
        strncat(tmp2, tmp, sizeof(char)*(nds.size()*16 - 2));
    }
    tmp2[strlen(tmp2) - 1] = '}'; // replace last , with } to complete list of ids*/

    if (countDB == 0) {
        free(tmp2);
        return nds.size(); // All ids where in cache, so nothing more to do */
    }

    pgsql_endCopy(node_table);

    PGconn *sql_conn = node_table->sql_conn;

    char const *paramValues[1];
    paramValues[0] = tmp2;
    PGresult *res = pgsql_execPrepared(sql_conn, "get_node_list", 1, paramValues, PGRES_TUPLES_OK);
    int countPG = PQntuples(res);

    //store the pg results in a hashmap and telling it how many we expect
    std::unordered_map<osmid_t, osmNode> pg_nodes(countPG);

    for (int i = 0; i < countPG; i++) {
        osmid_t id = strtoosmid(PQgetvalue(res, i, 0), nullptr, 10);
        osmNode node;
#ifdef FIXED_POINT
        ramNode n((int) strtol(PQgetvalue(res, i, 2), nullptr, 10),
                  (int) strtol(PQgetvalue(res, i, 1), nullptr, 10));

        node.lat = n.lat();
        node.lon = n.lon();
#else
        node.lat = strtod(PQgetvalue(res, i, 1), nullptr);
        node.lon = strtod(PQgetvalue(res, i, 2), nullptr);
#endif
        pg_nodes.emplace(id, node);
    }

    PQclear(res);
    free(tmp2);

    // If some of the nodes in the way don't exist, the returning list has holes.
    // Merge the two lists removing any holes.
    size_t wrtidx = 0;
    for (size_t i = 0; i < nds.size(); ++i) {
        if (std::isnan(out[i].lat)) {
            std::unordered_map<osmid_t, osmNode>::iterator found = pg_nodes.find(nds[i]);
            if(found != pg_nodes.end()) {
                out[wrtidx] = found->second;
                ++wrtidx;
            }
        } else {
            if (wrtidx < i)
                out[wrtidx] = out[i];
            ++wrtidx;
        }
    }
    out.resize(wrtidx);

    return wrtidx;
}


void middle_pgsql_t::nodes_set(osmid_t id, double lat, double lon, const taglist_t &tags) {
    cache->set( id, lat, lon, tags );

    if (out_options->flat_node_cache_enabled) {
        persistent_cache->set(id, lat, lon);
    } else {
        local_nodes_set(id, lat, lon, tags);
    }
}

size_t middle_pgsql_t::nodes_get_list(nodelist_t &out, const idlist_t nds) const
{
    return (out_options->flat_node_cache_enabled)
             ? persistent_cache->get_list(out, nds)
             : local_nodes_get_list(out, nds);
}

void middle_pgsql_t::local_nodes_delete(osmid_t osm_id)
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( node_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;
    pgsql_execPrepared(node_table->sql_conn, "delete_node", 1, paramValues, PGRES_COMMAND_OK );
}

void middle_pgsql_t::nodes_delete(osmid_t osm_id)
{
    if (out_options->flat_node_cache_enabled) {
        persistent_cache->set(osm_id, NAN, NAN);
    } else {
        local_nodes_delete(osm_id);
    }
}

void middle_pgsql_t::node_changed(osmid_t osm_id)
{
    if (!mark_pending) {
        return;
    }

    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( way_table );
    pgsql_endCopy( rel_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;

    //keep track of whatever ways and rels these nodes intersect
    //TODO: dont need to stop the copy above since we are only reading?
    PGresult* res = pgsql_execPrepared(way_table->sql_conn, "mark_ways_by_node", 1, paramValues, PGRES_TUPLES_OK );
    for(int i = 0; i < PQntuples(res); ++i)
    {
        char *end;
        osmid_t marked = strtoosmid(PQgetvalue(res, i, 0), &end, 10);
        ways_pending_tracker->mark(marked);
    }
    PQclear(res);

    //do the rels too
    res = pgsql_execPrepared(rel_table->sql_conn, "mark_rels_by_node", 1, paramValues, PGRES_TUPLES_OK );
    for(int i = 0; i < PQntuples(res); ++i)
    {
        char *end;
        osmid_t marked = strtoosmid(PQgetvalue(res, i, 0), &end, 10);
        rels_pending_tracker->mark(marked);
    }
    PQclear(res);
}

void middle_pgsql_t::ways_set(osmid_t way_id, const idlist_t &nds, const taglist_t &tags)
{
    copy_buffer.reserve(nds.size() * 10 + tags.size() * 24 + 64);
    bool copy = way_table->copyMode;
    char delim = copy ? '\t' : '\0';
    // Three params: id, nodes, tags */
    const char *paramValues[4] = { copy_buffer.c_str(), };

    copy_buffer = std::to_string(way_id);
    copy_buffer += delim;

    paramValues[1] = paramValues[0] + copy_buffer.size();
    buffer_store_nodes(nds);
    copy_buffer += delim;

    if (tags.size() == 0) {
        paramValues[2] = nullptr;
        copy_buffer += "\\N";
    } else {
        paramValues[2] = paramValues[0] + copy_buffer.size();
        buffer_store_tags(tags, copy);
    }

    if (copy) {
        copy_buffer += '\n';
        pgsql_CopyData(__FUNCTION__, way_table->sql_conn, copy_buffer);
    } else {
        buffer_correct_params(paramValues, 3);
        pgsql_execPrepared(way_table->sql_conn, "insert_way", 3,
                           (const char * const *)paramValues, PGRES_COMMAND_OK);
    }
}

bool middle_pgsql_t::ways_get(osmid_t id, taglist_t &tags, nodelist_t &nodes) const
{
    char const *paramValues[1];
    PGconn *sql_conn = way_table->sql_conn;

    // Make sure we're out of copy mode */
    pgsql_endCopy( way_table );

    char tmp[16];
    snprintf(tmp, sizeof(tmp), "%" PRIdOSMID, id);
    paramValues[0] = tmp;

    PGresult *res = pgsql_execPrepared(sql_conn, "get_way", 1, paramValues, PGRES_TUPLES_OK);

    if (PQntuples(res) != 1) {
        PQclear(res);
        return false;
    }

    pgsql_parse_tags( PQgetvalue(res, 0, 1), tags );

    size_t num_nodes = strtoul(PQgetvalue(res, 0, 2), nullptr, 10);
    idlist_t list;
    pgsql_parse_nodes( PQgetvalue(res, 0, 0), list);
    if (num_nodes != list.size()) {
        fprintf(stderr, "parse_nodes problem for way %s: expected nodes %zu got %zu\n",
                tmp, num_nodes, list.size());
        util::exit_nicely();
    }
    PQclear(res);

    nodes_get_list(nodes, list);
    return true;
}

size_t middle_pgsql_t::ways_get_list(const idlist_t &ids, idlist_t &way_ids,
                                  multitaglist_t &tags, multinodelist_t &nodes) const {
    if (ids.empty())
        return 0;

    char tmp[16];
    std::unique_ptr<char[]> tmp2(new (std::nothrow) char[ids.size() * 16]);
    char const *paramValues[1];

    if (tmp2 == nullptr) return 0; //failed to allocate memory, return */

    // create a list of ids in tmp2 to query the database  */
    sprintf(tmp2.get(), "{");
    for(idlist_t::const_iterator it = ids.begin(); it != ids.end(); ++it) {
        snprintf(tmp, sizeof(tmp), "%" PRIdOSMID ",", *it);
        strncat(tmp2.get(), tmp, sizeof(char)*(ids.size()*16 - 2));
    }
    tmp2[strlen(tmp2.get()) - 1] = '}'; // replace last , with } to complete list of ids*/

    pgsql_endCopy(way_table);

    PGconn *sql_conn = way_table->sql_conn;

    paramValues[0] = tmp2.get();
    PGresult *res = pgsql_execPrepared(sql_conn, "get_way_list", 1, paramValues, PGRES_TUPLES_OK);
    int countPG = PQntuples(res);

    idlist_t wayidspg;

    for (int i = 0; i < countPG; i++) {
        wayidspg.push_back(strtoosmid(PQgetvalue(res, i, 0), nullptr, 10));
    }


    // Match the list of ways coming from postgres in a different order
    //   back to the list of ways given by the caller */
    for(idlist_t::const_iterator it = ids.begin(); it != ids.end(); ++it) {
        for (int j = 0; j < countPG; j++) {
            if (*it == wayidspg[j]) {
                way_ids.push_back(*it);
                tags.push_back(taglist_t());
                pgsql_parse_tags(PQgetvalue(res, j, 2), tags.back());

                size_t num_nodes = strtoul(PQgetvalue(res, j, 3), nullptr, 10);
                idlist_t list;
                pgsql_parse_nodes( PQgetvalue(res, j, 1), list);
                if (num_nodes != list.size()) {
                    fprintf(stderr, "parse_nodes problem for way %s: expected nodes %zu got %zu\n",
                            tmp, num_nodes, list.size());
                    util::exit_nicely();
                }

                nodes.push_back(nodelist_t());
                nodes_get_list(nodes.back(), list);

                break;
            }
        }
    }

    assert(way_ids.size() <= ids.size());

    PQclear(res);

    return way_ids.size();
}


void middle_pgsql_t::ways_delete(osmid_t osm_id)
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( way_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;
    pgsql_execPrepared(way_table->sql_conn, "delete_way", 1, paramValues, PGRES_COMMAND_OK );
}

void middle_pgsql_t::iterate_ways(middle_t::pending_processor& pf)
{

    // Make sure we're out of copy mode */
    pgsql_endCopy( way_table );

    // enqueue the jobs
    osmid_t id;
    while(id_tracker::is_valid(id = ways_pending_tracker->pop_mark()))
    {
        pf.enqueue_ways(id);
    }
    // in case we had higher ones than the middle
    pf.enqueue_ways(id_tracker::max());

    //let the threads work on them
    pf.process_ways();
}

void middle_pgsql_t::way_changed(osmid_t osm_id)
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( rel_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;

    //keep track of whatever rels this way intersects
    //TODO: dont need to stop the copy above since we are only reading?
    PGresult* res = pgsql_execPrepared(rel_table->sql_conn, "mark_rels_by_way", 1, paramValues, PGRES_TUPLES_OK );
    for(int i = 0; i < PQntuples(res); ++i)
    {
        char *end;
        osmid_t marked = strtoosmid(PQgetvalue(res, i, 0), &end, 10);
        rels_pending_tracker->mark(marked);
    }
    PQclear(res);
}

void middle_pgsql_t::relations_set(osmid_t id, const memberlist_t &members, const taglist_t &tags)
{
    taglist_t member_list;
    char buf[64];

    idlist_t all_parts, node_parts, way_parts, rel_parts;
    all_parts.reserve(members.size());
    node_parts.reserve(members.size());
    way_parts.reserve(members.size());
    rel_parts.reserve(members.size());

    for (memberlist_t::const_iterator it = members.begin(); it != members.end(); ++it) {
        char type = 0;
        switch (it->type)
        {
            case OSMTYPE_NODE:     node_parts.push_back(it->id); type = 'n'; break;
            case OSMTYPE_WAY:      way_parts.push_back(it->id); type = 'w'; break;
            case OSMTYPE_RELATION: rel_parts.push_back(it->id); type = 'r'; break;
            default:
                fprintf(stderr, "Internal error: Unknown member type %d\n", it->type);
                util::exit_nicely();
        }
        sprintf( buf, "%c%" PRIdOSMID, type, it->id );
        member_list.push_back(tag_t(buf, it->role));
    }

    all_parts.insert(all_parts.end(), node_parts.begin(), node_parts.end());
    all_parts.insert(all_parts.end(), way_parts.begin(), way_parts.end());
    all_parts.insert(all_parts.end(), rel_parts.begin(), rel_parts.end());

    copy_buffer.reserve(all_parts.size() * 10 + member_list.size() * 24
                        + tags.size() * 24 + 64);

    // Params: id, way_off, rel_off, parts, members, tags */
    const char *paramValues[6] = { copy_buffer.c_str(), };
    bool copy = rel_table->copyMode;
    char delim = copy ? '\t' : '\0';

    copy_buffer = std::to_string(id);
    copy_buffer+= delim;

    paramValues[1] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(node_parts.size());
    copy_buffer+= delim;

    paramValues[2] = paramValues[0] + copy_buffer.size();
    copy_buffer += std::to_string(node_parts.size() + way_parts.size());
    copy_buffer+= delim;

    paramValues[3] = paramValues[0] + copy_buffer.size();
    buffer_store_nodes(all_parts);
    copy_buffer+= delim;

    if (member_list.size() == 0) {
        paramValues[4] = nullptr;
        copy_buffer += "\\N";
    } else {
        paramValues[4] = paramValues[0] + copy_buffer.size();
        buffer_store_tags(member_list, copy);
    }
    copy_buffer+= delim;

    if (tags.size() == 0) {
        paramValues[5] = nullptr;
        copy_buffer += "\\N";
    } else {
        paramValues[5] = paramValues[0] + copy_buffer.size();
        buffer_store_tags(tags, copy);
    }

    if (copy) {
        copy_buffer+= '\n';
        pgsql_CopyData(__FUNCTION__, rel_table->sql_conn, copy_buffer);
    } else {
        buffer_correct_params(paramValues, 6);
        pgsql_execPrepared(rel_table->sql_conn, "insert_rel", 6,
                           (const char * const *)paramValues, PGRES_COMMAND_OK);
    }
}

bool middle_pgsql_t::relations_get(osmid_t id, memberlist_t &members, taglist_t &tags) const
{
    char tmp[16];
    char const *paramValues[1];
    PGconn *sql_conn = rel_table->sql_conn;
    taglist_t member_temp;

    // Make sure we're out of copy mode */
    pgsql_endCopy( rel_table );

    snprintf(tmp, sizeof(tmp), "%" PRIdOSMID, id);
    paramValues[0] = tmp;

    PGresult *res = pgsql_execPrepared(sql_conn, "get_rel", 1, paramValues, PGRES_TUPLES_OK);
    // Fields are: members, tags, member_count */

    if (PQntuples(res) != 1) {
        PQclear(res);
        return false;
    }

    pgsql_parse_tags(PQgetvalue(res, 0, 1), tags);
    pgsql_parse_tags(PQgetvalue(res, 0, 0), member_temp);

    if (member_temp.size() != strtoul(PQgetvalue(res, 0, 2), nullptr, 10)) {
        fprintf(stderr, "Unexpected member_count reading relation %" PRIdOSMID "\n", id);
        util::exit_nicely();
    }

    PQclear(res);

    for (taglist_t::const_iterator it = member_temp.begin(); it != member_temp.end(); ++it) {
        char tag = it->key[0];
        OsmType type = (tag == 'n')?OSMTYPE_NODE:(tag == 'w')?OSMTYPE_WAY:(tag == 'r')?OSMTYPE_RELATION:((OsmType)-1);
        members.push_back(member(type,
                                 strtoosmid(it->key.c_str()+1, nullptr, 10 ),
                                 it->value));
    }
    return true;
}

void middle_pgsql_t::relations_delete(osmid_t osm_id)
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( way_table );
    pgsql_endCopy( rel_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;
    pgsql_execPrepared(rel_table->sql_conn, "delete_rel", 1, paramValues, PGRES_COMMAND_OK );

    //keep track of whatever ways this relation interesects
    //TODO: dont need to stop the copy above since we are only reading?
    PGresult* res = pgsql_execPrepared(way_table->sql_conn, "mark_ways_by_rel", 1, paramValues, PGRES_TUPLES_OK );
    for(int i = 0; i < PQntuples(res); ++i)
    {
        char *end;
        osmid_t marked = strtoosmid(PQgetvalue(res, i, 0), &end, 10);
        ways_pending_tracker->mark(marked);
    }
    PQclear(res);
}

void middle_pgsql_t::iterate_relations(pending_processor& pf)
{
    // Make sure we're out of copy mode */
    pgsql_endCopy( rel_table );

    // enqueue the jobs
    osmid_t id;
    while(id_tracker::is_valid(id = rels_pending_tracker->pop_mark()))
    {
        pf.enqueue_relations(id);
    }
    // in case we had higher ones than the middle
    pf.enqueue_relations(id_tracker::max());

    //let the threads work on them
    pf.process_relations();
}

void middle_pgsql_t::relation_changed(osmid_t osm_id)
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( rel_table );

    sprintf( buffer, "%" PRIdOSMID, osm_id );
    paramValues[0] = buffer;

    //keep track of whatever ways and rels these nodes intersect
    //TODO: dont need to stop the copy above since we are only reading?
    //TODO: can we just mark the id without querying? the where clause seems intersect reltable.parts with the id
    PGresult* res = pgsql_execPrepared(rel_table->sql_conn, "mark_rels", 1, paramValues, PGRES_TUPLES_OK );
    for(int i = 0; i < PQntuples(res); ++i)
    {
        char *end;
        osmid_t marked = strtoosmid(PQgetvalue(res, i, 0), &end, 10);
        rels_pending_tracker->mark(marked);
    }
    PQclear(res);
}

idlist_t middle_pgsql_t::relations_using_way(osmid_t way_id) const
{
    char const *paramValues[1];
    char buffer[64];
    // Make sure we're out of copy mode */
    pgsql_endCopy( rel_table );

    sprintf(buffer, "%" PRIdOSMID, way_id);
    paramValues[0] = buffer;

    PGresult *result = pgsql_execPrepared(rel_table->sql_conn, "rels_using_way",
                                          1, paramValues, PGRES_TUPLES_OK );
    const int ntuples = PQntuples(result);
    idlist_t rel_ids(ntuples);
    for (int i = 0; i < ntuples; ++i) {
        rel_ids[i] = strtoosmid(PQgetvalue(result, i, 0), nullptr, 10);
    }
    PQclear(result);

    return rel_ids;
}

void middle_pgsql_t::analyze(void)
{
    for (auto& table: tables) {
        PGconn *sql_conn = table.sql_conn;

        if (table.analyze) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.analyze);
        }
    }
}

void middle_pgsql_t::end(void)
{
    for (auto& table: tables) {
        PGconn *sql_conn = table.sql_conn;

        // Commit transaction */
        if (table.stop && table.transactionMode) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.stop);
            table.transactionMode = 0;
        }

    }
}

/**
 * Helper to create SQL queries.
 *
 * The input string is mangled as follows:
 * %p replaced by the content of the "prefix" option
 * %i replaced by the content of the "tblsslim_data" option
 * %t replaced by the content of the "tblssslim_index" option
 * %m replaced by "UNLOGGED" if the "unlogged" option is set
 * other occurrences of the "%" char are treated normally.
 * any occurrence of { or } will be ignored (not copied to output string);
 * anything inside {} is only copied if it contained at least one of
 * %p, %i, %t, %m that was not NULL.
 *
 * So, the input string
 *    Hello{ dear %i}!
 * will, if i is set to "John", translate to
 *    Hello dear John!
 * but if i is unset, translate to
 *    Hello!
 *
 * This is used for constructing SQL queries with proper tablespace settings.
 */
static void set_prefix_and_tbls(const struct options_t *options, const char **string)
{
    char buffer[1024];
    const char *source;
    char *dest;
    char *openbrace = nullptr;
    int copied = 0;

    if (*string == nullptr) {
        return;
    }
    source = *string;
    dest = buffer;

    while (*source) {
        if (*source == '{') {
            openbrace = dest;
            copied = 0;
            source++;
            continue;
        } else if (*source == '}') {
            if (!copied && openbrace) dest = openbrace;
            source++;
            continue;
        } else if (*source == '%') {
            if (*(source+1) == 'p') {
                if (!options->prefix.empty()) {
                    strcpy(dest, options->prefix.c_str());
                    dest += strlen(options->prefix.c_str());
                    copied = 1;
                }
                source+=2;
                continue;
            } else if (*(source+1) == 't') {
                if (options->tblsslim_data) {
                    strcpy(dest, options->tblsslim_data->c_str());
                    dest += strlen(options->tblsslim_data->c_str());
                    copied = 1;
                }
                source+=2;
                continue;
            } else if (*(source+1) == 'i') {
                if (options->tblsslim_index) {
                    strcpy(dest, options->tblsslim_index->c_str());
                    dest += strlen(options->tblsslim_index->c_str());
                    copied = 1;
                }
                source+=2;
                continue;
            } else if (*(source+1) == 'm') {
                if (options->unlogged) {
                    strcpy(dest, "UNLOGGED");
                    dest += 8;
                    copied = 1;
                }
                source+=2;
                continue;
            }
        }
        *(dest++) = *(source++);
    }
    *dest = 0;
    *string = strdup(buffer);
}

void middle_pgsql_t::connect(table_desc& table) {
    PGconn *sql_conn;

    set_prefix_and_tbls(out_options, &(table.name));
    set_prefix_and_tbls(out_options, &(table.start));
    set_prefix_and_tbls(out_options, &(table.create));
    set_prefix_and_tbls(out_options, &(table.create_index));
    set_prefix_and_tbls(out_options, &(table.prepare));
    set_prefix_and_tbls(out_options, &(table.prepare_intarray));
    set_prefix_and_tbls(out_options, &(table.copy));
    set_prefix_and_tbls(out_options, &(table.analyze));
    set_prefix_and_tbls(out_options, &(table.stop));
    set_prefix_and_tbls(out_options, &(table.array_indexes));

    fprintf(stderr, "Setting up table: %s\n", table.name);
    sql_conn = PQconnectdb(out_options->database_options.conninfo().c_str());

    // Check to see that the backend connection was successfully made, and if not, exit */
    if (PQstatus(sql_conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(sql_conn));
        util::exit_nicely();
    }
    table.sql_conn = sql_conn;
}

void middle_pgsql_t::start(const options_t *out_options_)
{
    out_options = out_options_;
    bool dropcreate = !out_options->append; ///< If tables need to be dropped and created anew

    ways_pending_tracker.reset(new id_tracker());
    rels_pending_tracker.reset(new id_tracker());

    // Gazetter doesn't use mark-pending processing and consequently
    // needs no way-node index.
    // TODO Currently, set here to keep the impact on the code small.
    // We actually should have the output plugins report their needs
    // and pass that via the constructor to middle_t, so that middle_t
    // itself doesn't need to know about details of the output.
    if (out_options->output_backend == "gazetteer") {
        way_table->array_indexes = nullptr;
        mark_pending = false;
    }

    append = out_options->append;
    // reset this on every start to avoid options from last run
    // staying set for the second.
    build_indexes = !append && !out_options->droptemp;

    cache.reset(new node_ram_cache( out_options->alloc_chunkwise | ALLOC_LOSSY, out_options->cache, out_options->scale));
    if (out_options->flat_node_cache_enabled) persistent_cache.reset(new node_persistent_cache(out_options, out_options->append, false, cache));

    fprintf(stderr, "Mid: pgsql, scale=%d cache=%d\n", out_options->scale, out_options->cache);

    // We use a connection per table to enable the use of COPY */
    for (auto& table: tables) {
        connect(table);
        PGconn* sql_conn = table.sql_conn;

        /*
         * To allow for parallelisation, the second phase (iterate_ways), cannot be run
         * in an extended transaction and each update statement is its own transaction.
         * Therefore commit rate of postgresql is very important to ensure high speed.
         * If fsync is enabled to ensure safe transactions, the commit rate can be very low.
         * To compensate for this, one can set the postgresql parameter synchronous_commit
         * to off. This means an update statement returns to the client as success before the
         * transaction is saved to disk via fsync, which in return allows to bunch up multiple
         * transactions into a single fsync. This may result in some data loss in the case of a
         * database crash. However, as we don't currently have the ability to restart a full osm2pgsql
         * import session anyway, this is fine. Diff imports are also not effected, as the next
         * diff import would simply deal with all pending ways that were not previously finished.
         * This parameter does not effect safety from data corruption on the back-end.
         */
        pgsql_exec(sql_conn, PGRES_COMMAND_OK, "SET synchronous_commit TO off;");

        pgsql_exec(sql_conn, PGRES_COMMAND_OK, "SET client_min_messages = WARNING");
        if (dropcreate) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "DROP TABLE IF EXISTS %s", table.name);
        }

        if (table.start) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.start);
            table.transactionMode = 1;
        }

        if (dropcreate && table.create) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.create);
            if (table.create_index) {
              pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.create_index);
            }
        }
        pgsql_exec(sql_conn, PGRES_COMMAND_OK, "RESET client_min_messages");

        if (table.prepare) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.prepare);
        }

        if (append && table.prepare_intarray) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.prepare_intarray);
        }

        if (table.copy) {
            pgsql_exec(sql_conn, PGRES_COPY_IN, "%s", table.copy);
            table.copyMode = 1;
        }
    }
}

void middle_pgsql_t::commit(void) {
    for (auto& table: tables) {
        PGconn *sql_conn = table.sql_conn;
        pgsql_endCopy(&table);
        if (table.stop && table.transactionMode) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table.stop);
            table.transactionMode = 0;
        }
    }
    // Make sure the flat nodes are committed to disk or there will be
    // surprises later.
    if (out_options->flat_node_cache_enabled) persistent_cache.reset();
}

void middle_pgsql_t::pgsql_stop_one(table_desc *table)
{
    time_t start, end;

    PGconn *sql_conn = table->sql_conn;

    fprintf(stderr, "Stopping table: %s\n", table->name);
    pgsql_endCopy(table);
    time(&start);
    if (out_options->droptemp)
    {
        pgsql_exec(sql_conn, PGRES_COMMAND_OK, "DROP TABLE %s", table->name);
    }
    else if (build_indexes && table->array_indexes)
    {
        fprintf(stderr, "Building index on table: %s\n", table->name);
        pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", table->array_indexes);
    }

    PQfinish(sql_conn);
    table->sql_conn = nullptr;
    time(&end);
    fprintf(stderr, "Stopped table: %s in %is\n", table->name, (int)(end - start));
}


void middle_pgsql_t::stop(void)
{
    cache.reset();
    if (out_options->flat_node_cache_enabled) persistent_cache.reset();

    std::vector<std::future<void>> futures;
    futures.reserve(num_tables);

    for (int i = 0; i < num_tables; ++i) {
        futures.push_back(std::async(&middle_pgsql_t::pgsql_stop_one, this, &tables[i]));
    }

    for (auto &f : futures) {
        f.get();
    }
}

middle_pgsql_t::middle_pgsql_t()
    : tables(), num_tables(0), node_table(nullptr), way_table(nullptr), rel_table(nullptr),
      append(false), mark_pending(true), cache(), persistent_cache(), build_indexes(true)
{
    /*table = t_node,*/
    tables.push_back(table_desc(
            /*name*/ "%p_nodes",
           /*start*/ "BEGIN;\n",
#ifdef FIXED_POINT
          /*create*/ "CREATE %m TABLE %p_nodes (id " POSTGRES_OSMID_TYPE " PRIMARY KEY {USING INDEX TABLESPACE %i}, lat int4 not null, lon int4 not null, tags text[]) {TABLESPACE %t};\n",
    /*create_index*/ nullptr,
         /*prepare*/ "PREPARE insert_node (" POSTGRES_OSMID_TYPE ", int4, int4, text[]) AS INSERT INTO %p_nodes VALUES ($1,$2,$3,$4);\n"
#else
          /*create*/ "CREATE %m TABLE %p_nodes (id " POSTGRES_OSMID_TYPE " PRIMARY KEY {USING INDEX TABLESPACE %i}, lat double precision not null, lon double precision not null, tags text[]) {TABLESPACE %t};\n",
    /*create_index*/ nullptr,
         /*prepare*/ "PREPARE insert_node (" POSTGRES_OSMID_TYPE ", double precision, double precision, text[]) AS INSERT INTO %p_nodes VALUES ($1,$2,$3,$4);\n"
#endif
               "PREPARE get_node (" POSTGRES_OSMID_TYPE ") AS SELECT lat,lon,tags FROM %p_nodes WHERE id = $1 LIMIT 1;\n"
               "PREPARE get_node_list(" POSTGRES_OSMID_TYPE "[]) AS SELECT id, lat, lon FROM %p_nodes WHERE id = ANY($1::" POSTGRES_OSMID_TYPE "[]);\n"
               "PREPARE delete_node (" POSTGRES_OSMID_TYPE ") AS DELETE FROM %p_nodes WHERE id = $1;\n",
/*prepare_intarray*/ nullptr,
            /*copy*/ "COPY %p_nodes FROM STDIN;\n",
         /*analyze*/ "ANALYZE %p_nodes;\n",
            /*stop*/ "COMMIT;\n"
                         ));
    tables.push_back(table_desc(
        /*table t_way,*/
            /*name*/ "%p_ways",
           /*start*/ "BEGIN;\n",
          /*create*/ "CREATE %m TABLE %p_ways (id " POSTGRES_OSMID_TYPE " PRIMARY KEY {USING INDEX TABLESPACE %i}, nodes " POSTGRES_OSMID_TYPE "[] not null, tags text[]) {TABLESPACE %t};\n",
    /*create_index*/ nullptr,
         /*prepare*/ "PREPARE insert_way (" POSTGRES_OSMID_TYPE ", " POSTGRES_OSMID_TYPE "[], text[]) AS INSERT INTO %p_ways VALUES ($1,$2,$3);\n"
               "PREPARE get_way (" POSTGRES_OSMID_TYPE ") AS SELECT nodes, tags, array_upper(nodes,1) FROM %p_ways WHERE id = $1;\n"
               "PREPARE get_way_list (" POSTGRES_OSMID_TYPE "[]) AS SELECT id, nodes, tags, array_upper(nodes,1) FROM %p_ways WHERE id = ANY($1::" POSTGRES_OSMID_TYPE "[]);\n"
               "PREPARE delete_way(" POSTGRES_OSMID_TYPE ") AS DELETE FROM %p_ways WHERE id = $1;\n",
/*prepare_intarray*/
               "PREPARE mark_ways_by_node(" POSTGRES_OSMID_TYPE ") AS select id from %p_ways WHERE nodes && ARRAY[$1];\n"
               "PREPARE mark_ways_by_rel(" POSTGRES_OSMID_TYPE ") AS select id from %p_ways WHERE id IN (SELECT unnest(parts[way_off+1:rel_off]) FROM %p_rels WHERE id = $1);\n",

            /*copy*/ "COPY %p_ways FROM STDIN;\n",
         /*analyze*/ "ANALYZE %p_ways;\n",
            /*stop*/  "COMMIT;\n",
   /*array_indexes*/ "CREATE INDEX %p_ways_nodes ON %p_ways USING gin (nodes) WITH (FASTUPDATE=OFF) {TABLESPACE %i};\n"
                         ));
    tables.push_back(table_desc(
        /*table = t_rel,*/
            /*name*/ "%p_rels",
           /*start*/ "BEGIN;\n",
          /*create*/ "CREATE %m TABLE %p_rels(id " POSTGRES_OSMID_TYPE " PRIMARY KEY {USING INDEX TABLESPACE %i}, way_off int2, rel_off int2, parts " POSTGRES_OSMID_TYPE "[], members text[], tags text[]) {TABLESPACE %t};\n",
    /*create_index*/ nullptr,
         /*prepare*/ "PREPARE insert_rel (" POSTGRES_OSMID_TYPE ", int2, int2, " POSTGRES_OSMID_TYPE "[], text[], text[]) AS INSERT INTO %p_rels VALUES ($1,$2,$3,$4,$5,$6);\n"
               "PREPARE get_rel (" POSTGRES_OSMID_TYPE ") AS SELECT members, tags, array_upper(members,1)/2 FROM %p_rels WHERE id = $1;\n"
               "PREPARE delete_rel(" POSTGRES_OSMID_TYPE ") AS DELETE FROM %p_rels WHERE id = $1;\n",
/*prepare_intarray*/
                "PREPARE rels_using_way(" POSTGRES_OSMID_TYPE ") AS SELECT id FROM %p_rels WHERE parts && ARRAY[$1] AND parts[way_off+1:rel_off] && ARRAY[$1];\n"
                "PREPARE mark_rels_by_node(" POSTGRES_OSMID_TYPE ") AS select id from %p_ways WHERE nodes && ARRAY[$1];\n"
                "PREPARE mark_rels_by_way(" POSTGRES_OSMID_TYPE ") AS select id from %p_rels WHERE parts && ARRAY[$1] AND parts[way_off+1:rel_off] && ARRAY[$1];\n"
                "PREPARE mark_rels(" POSTGRES_OSMID_TYPE ") AS select id from %p_rels WHERE parts && ARRAY[$1] AND parts[rel_off+1:array_length(parts,1)] && ARRAY[$1];\n",

            /*copy*/ "COPY %p_rels FROM STDIN;\n",
         /*analyze*/ "ANALYZE %p_rels;\n",
            /*stop*/  "COMMIT;\n",
   /*array_indexes*/ "CREATE INDEX %p_rels_parts ON %p_rels USING gin (parts) WITH (FASTUPDATE=OFF) {TABLESPACE %i};\n"
                         ));

    // set up the rest of the variables from the tables.
    num_tables = tables.size();
    assert(num_tables == 3);

    node_table = &tables[0];
    way_table = &tables[1];
    rel_table = &tables[2];
}

middle_pgsql_t::~middle_pgsql_t() {
    for (auto& table: tables) {
        if (table.sql_conn) {
            PQfinish(table.sql_conn);
        }
    }

}

std::shared_ptr<const middle_query_t> middle_pgsql_t::get_instance() const {
    middle_pgsql_t* mid = new middle_pgsql_t();
    mid->out_options = out_options;
    mid->append = out_options->append;
    mid->mark_pending = mark_pending;

    //NOTE: this is thread safe for use in pending async processing only because
    //during that process they are only read from
    mid->cache = cache;
    // The persistent cache on the other hand is not thread-safe for reading,
    // so we create one per instance.
    if (out_options->flat_node_cache_enabled)
        mid->persistent_cache.reset(new node_persistent_cache(out_options, 1, true, cache));

    // We use a connection per table to enable the use of COPY */
    for(int i=0; i<num_tables; i++) {
        mid->connect(mid->tables[i]);
        PGconn* sql_conn = mid->tables[i].sql_conn;

        if (tables[i].prepare) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", tables[i].prepare);
        }

        if (append && tables[i].prepare_intarray) {
            pgsql_exec(sql_conn, PGRES_COMMAND_OK, "%s", tables[i].prepare_intarray);
        }
    }

    return std::shared_ptr<const middle_query_t>(mid);
}

size_t middle_pgsql_t::pending_count() const {
    return ways_pending_tracker->size() + rels_pending_tracker->size();
}