Executing custom SQL after Lua insert

[FaFre]

I want to post process an added row by using a custom query, right after the insert. At this stage I need PostGIS to process the geometries, which is not possible with the limited features in osm2pgsql.

I could just use the osmid to update the table right after the insert by using e.g. a lua postgres client inside the script, but the problem here will be that no index is created yet. Doing a full table scan after each insert will take a long time for my number of rows. Since osm2pgsql is not having start/finish callbacks I also cannot create them myself at an earlier stage (when this is possible, triggers would be much better).

How I can handle this?

[joto]

Do you actually need to do this "right after the insert"? It would be much easier to do that later. You can insert an empty column (possibly with create_only) that's NULL to any table to mark that work still needs to be done and run an SQL script after osm2pgsql finishes that fills in that column. Or just create a "TODO" table with a list of ids that need post-processing.

If you really need this, you can build yourself a "start" callback by checking that the process_node() function is called for the first time, something like this:

local first_object = true
function osm2pgsql.process_node(object)
    if first_object then
        first_object = false
        process_start()
    end
    -- normal node processing
end

Note that doing anything with the data in the database inside the Lua processing functions is problematic (not only because of the missing indexes), because the inserts done by osm2pgsql are buffered, so changing a row directly after inserting it will not work. But it would be okay, to create a trigger inside that process_start() function.

[FaFre]

Yes, that would also work. What I'm actually doing is enriching the imported data with information from a nominatim database. Those queries are quite slow due to the huge amount of data (around 150ms per item). Replication will be also a topic later on, data will not be consistent for some time until my after-import script has been executed (which can take up to a few hours). With integrating it into the osm2pgsql import itself I can circumvent possible troubles (which I smell) here. But I see that this doesn't seem to work. I will investigate more what I can do with triggers. Thanks for the Input!