[Tile-serving] [openstreetmap/osm2pgsql] Fix: Make sure index is used for type/id indexes in flex output (PR #2007)

[Paul Norman]

@pnorman approved this pull request.

It looks like what's happening is the VALUES has types of `(text, int)` or `(text, bigint)`, and because the join condition could be done as `p.foo::text = t.osm_type` or `p.foo = t.osm_type::char(1)` it picks the former because the latter would involve potential data loss.

There are three ways to resolve this
1. coerce the first (or all?) the values as documented for [quoted literal constants](https://www.postgresql.org/docs/current/sql-values.html)
2. use a temporary table, which will automatically coerce to the type of the columns when VALUES is used in the insert statement. This would also avoid potential out-of-memory failures for very large numbers of rows
3. coerce the type, as done in this PR

I'm somewhat inclined towards the third one, but would prefer to also coerce `t.osm_id` to bigint at the same time. This is only to be explicit about the type conversion, as if the values contains only numbers that can be represented as a normal integer it will get coerced into a bigint anyways when comparing to the p table, which has type bigint.

So I'm okay with the PR as-is, but some food for thought and other options.



-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/2007#pullrequestreview-1540011745
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/pull/2007/review/1540011745 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230720/3a695d81/attachment.htm>