search

rustprooflabs / pgosm-flex

PgOSM Flex provides high quality OpenStreetMap datasets in PostGIS (Postgres) using the osm2pgsql Flex output.
MIT License
101 stars 20 forks source link

FR: support of GreenPlum #287

Closed SailorMax closed 1 year ago

SailorMax commented 1 year ago

Can you try to reduce minimal version of Postgres to 9.4? The problem in supporting of GreenPlum. Unfortunately it's latest stable version based on Postgres 9.4. Only next version (currently in beta stage) based on Postgres 12. There is no so much unsupported features, which I saw while tried to import data to GP:

  1. unsupported procedures. You can use functions instead!
  2. unsupported "GENERATED ALWAYS AS" column definitions. You can move this part to insert queries.
  3. hope nothing missed :)

thank you.

rustprooflabs commented 1 year ago

@SailorMax Going backwards with support isn't a path I'm interested in, especially 4 majors versions past EOL. I agree with your assessment that the two obvious blocking issues from Postgres functionality are procedures and generated columns. However, those are two features I'm not willing to undo the use of and I have broader concerns with the idea. This train of thought leads into the PostGIS minimum which is listed at 3.0. Their version matrix doesn't even list Postgres versions older than Pg11. I'm 99% sure that PostGIS 3.0 support only went back to Postgres 9.6. To get Pg9.4 support I think that would require PostGIS 2.4 or earlier.

I have no idea how long GP's beta / release cycle is, but I'd say hold out for their upgrade based on Pg12.

1) The use of a procedure to calculate the nested polygon structure cannot be simply replaced with functions. The key procedure (osm.build_nested_admin_polygons) uses internal transaction control, which makes that process reasonable to run on large regions and enables running that in batches over time without losing the progress made.

2) Looking at community Postgres, there's less than a year left of support for the last version w/out generated columns (Pg11). Considering this project has used generated columns since v0.0.4 (https://github.com/rustprooflabs/pgosm-flex/pull/13) it seems silly to revert that now.

3) Trying to support 3rd party forks and EOL versions of Postgres/PostGIS is a rabbit hole. Leaving Postgres/PostGIS aside, here's an example of osm2pgsql's stance on the topic per joto: "osm2pgsql is a PostgreSQL/PostGIS importer. Other databases are not supported or tested officially." With that being the stance of a critical component for this project that has to be the stance of this project too.

A possible (untested!) workaround would be to fork the project and:

That seems like it would avoid the issues of GP based on Pg9.4. Another option would be to run the processing in pure Postgres, adjust the results (drop generated columns and procedures) then dump/load the processed data into GP. That means the replication and upcoming --update append options won't be available, but should make it so the data can be manually loaded.