Can Flex styles run arbitrary SQL against the database?

[rustprooflabs]

With the flex output is it possible to run arbitrary SQL queries from the Lua styles? I have wondered this a few times already and previously looked around through the docs and did not find anything obvious. This week I started experimenting with partitioning schemes with the data and think this ability would be very helpful for this idea as well.

The planned partitioning scheme is to use the the region of the data (e.g. north-america--us-west) and the date the data is from. In order to use Postgres partitions (Pg10+) those values need to be in the table and in a primary key. In a non-partitioned scheme having the date and region on every row would just waste a bunch of space for no real benefit.

Normal (no partitioning) would create the simple PK.

ALTER TABLE osm.road_line
    ADD CONSTRAINT pk_osm_road_line_osm_id
    PRIMARY KEY (osm_id)
;

When the partitioning switch was enabled it would run different SQL to add necessary columns, comments and the more complicated PK. This sets the stage to load new data and only have to script basic ATTACH PARTITION commands.

ALTER TABLE osm.road_line ADD COLUMN osm_region TEXT NOT NULL DEFAULT { osm_region } ;
ALTER TABLE osm.road_line ADD COLUMN osm_date DATE NOT NULL DEFAULT { osm_date };

ALTER TABLE osm.road_line
    ADD CONSTRAINT pk_osm_road_line_osm_id_region_date
    PRIMARY KEY (osm_date, osm_region, osm_id)
;

Other ideas for running arbitrary SQL based on logic within Lua is to run appropriate post-import commands to create generated columns, comments, etc.

Thanks!

[pnorman]

What I'd do here is to load two different tables and create a view that combines them. Have osm2pgsql create and update the underlying tables and have your application use the combined view.

[rustprooflabs]

I'm using views for other goals but that won't work for partitioning.

[joto]

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.

More specifically to your question: I believe you want to change the tables after they have been created by osm2pgsql but before they have been filled. But that doesn't work (even if you did that from Lua after the table creation commands and before the callbacks are called) because osm2pgsql normally creates tables twice: It creates a temporary table, adds the data, then creates the final table and copies the sorted data over. This is exactly one of those cases where you need to have very specific knowledge of the inner workings of osm2pgsql to do something.

[rustprooflabs]

I'm knowingly using the "experimental" feature, doesn't that implicitly sign some liability waiver?! Jokes aside, I do not want to use osm2pgsql in a way that causes avoidable frustration in the future. I have no intention of running arbitrary SQL while osm2pgsql is processing, my goal is running post processing code. The osm2pgsql command has all the details needed to determine (in Lua) which post-processing SQL script(s) should be ran.

Does the connection string used by osm2pgsql get exposed in a way that lua-dbi could reuse the connection string? If so, is there a way to run a Lua function only after osm2pgsql processing has finished? I couldn't find any way to only run a function post-processing.

I currently use psql to run the post-processing scripts to create views, generated columns, etc., it sounds like continuing on that path is the best option right now. I already load a meta-table to Pg from Lua with information about the data loaded, I can add more information to that table to drive the logic within the database instead of Lua.

[joto]

No, you can't access the database connection information from Lua. But osm2pgsql uses the libpq library internally that psql and basically all other database tools use to access a PostgreSQL database. And that library understands certain environment variables that specify the database you want to connect to. So if you run your processing from a script that sets these first, all tools should just magically connect to the same database.

And no, there is no way to trigger some Lua code to be run after processing. But it would be easy to have a setup where you have a Lua script run after osm2pgsql and from that Lua script include a Lua "settings" file that is also included by the Lua osm2pgsql config file. This way you can share any settings you want. You can also read environment variables from Lua using normal Lua methods. This gives you another way of parameterizing Lua config files if needed.

[mmd-osm]

FWIW, we also discussed those topics back in #1212 and #1211 with pretty much the same conclusions.

[amandasaurus]

I think I have hit this issue while looking at postpass setup. There are [several SQL views])(https://github.com/woodpeck/postpass-ops/blob/main/SCHEMA.md#views) on the production server, and I think they should be created by osm2pgsql.

I attempted hack something like this, but it didn't work.

function osm2pgsql.after_relations()
  osm2pgsql.run_sql({
    description = "Create view",
    sql = "create or replace view postpass_... AS ..."
  })
end

While I understand the concern about some SQL modifying the data that osm2pgsql inserts, there is an easier use case of allowing people to create views.

How about a osm2pgsql.define_view{ name = "whatever", sql_def = "create view blah …" }? Afte all tables are created, osm2pgsql will see if a view named that exists, if so, it'll run the sql_def, otherwise not. If you change the sql_def, then no change is made. Would this be a safer intermediate step?

[joto]

The osm2psql.run_sql() only works in osm2pgsql-gen. There is no generic mechanism to run SQL code in osm2pgsql itself. This will be integrated at some point, but currently the generalization stuff is still in beta and lives in its own command.

I don't think we'll want to support a create_view command or so. That's a deep rabbit hole, we can not support everything that SQL supports, we only have special suport for stuff that osm2pgsql needs to unerstand to do the right thing. For instance the define_table() command in Lua do much more than just creating the table in the database.

So: If you want to run arbitrary SQL after osm2pgsql is done, either use osm2pgsql-gen and the run_sql() command or just use psql or any other way of running the SQL.