[Tile-serving] [openstreetmap/osm2pgsql] Can Flex styles run arbitrary SQL against the database? (#1415)

[Jochen Topf]

There is a Lua module out there for connecting to databases (look for lua-dbi). You should be able to use that to connect to the database and do anything you like. You can also run SQL commands before or after running osm2pgsql using the `psql` command or using any other way to access the database.

But the larger issue here is something else: osm2pgsql does things with the database and the user doesn't always know what they are. It might change settings, create indexes, do things in transactions or not, etc. So running your code while osm2pgsql does its thing always runs the risk of "disturbing" osm2pgsql or even breaking the data in some subtle way. And even if something works today, internals of osm2pgsql might change in the next version and then your solution breaks. So this is the problem with "allowing" users to run arbitrary SQL commands. You can do it, but if something breaks, you can't complain about osm2pgsql. So if you are doing something like this I suggest you do it before running osm2pgsql or after, but not during. (You can still use the normal Lua interpreter with the lua-dbi module to do this so that you can share Lua code with the Lua run from osm2pgsql if you wish to do so.)

Okay, so this is why there is no "officially sanctioned" and easy way to run arbitrary SQL from osm2pgsql. I realize that this blocks users from doing many interesting and clever things and I'd like to see some solution here, but I am not sure what this can be. Allowing the user more flexibility here would mean restricting the flexibility that osm2pgsql has. At the moment osm2pgsql can, for instance, build an index sooner or later in the processing. It doesn't matter, because as long as it always done when osm2pgsql is done, the user will always see the index at the end of the processing. But if we "allow" access while osm2pgsql is running, users will rely on certain behaviour of osm2pgsql (like at what point an index is available or not) which we can't change any more then.

-- 
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/discussions/1415#discussioncomment-358852
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210211/4e839bc7/attachment.htm>