Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Syntax error at create index #1447

Closed
doskabouter opened this issue Apr 6, 2021 · 3 comments · Fixed by #1448
Closed

Syntax error at create index #1447

doskabouter opened this issue Apr 6, 2021 · 3 comments · Fixed by #1448

Comments

@doskabouter
Copy link

When using --middle-schema myschema, --output-pgsql-schema myschema and --middle-way-node-index-id-shift I get a
syntax error at
CREATE INDEX myschema.planet_osm_ways_nodes_bucket_idx.

According to the docs schemanames are not allowed at CREATE INDEX

https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L710
and
https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L720

Also, what am I missing in my current import (don't want to redo it, it takes over 5 days)?
I do have these messages in my log:
All postprocessing on table 'planet_osm_point' done
All postprocessing on table 'planet_osm_line' done
All postprocessing on table 'planet_osm_roads' done
Done postprocessing on table 'planet_osm_rels'
All postprocessing on table 'planet_osm_polygon' done

So it seems those tables are fully completed, is that correct?
What steps do I need to take to finalize the ways table?

osm2pgsql version 1.4.1
Database version: 11.2
PostGIS version: 2.5

joto added a commit to joto/osm2pgsql that referenced this issue Apr 6, 2021
Indexes are always created in the same schema as the table.

Fixes osm2pgsql-dev#1447
@joto
Copy link
Collaborator

joto commented Apr 6, 2021

I have added a fix for the issue in #1448. Thanks for reporting this and I am sorry you have to re-run the import, but figuring out what you may be able to do to save this is way beyond what we can do here. (Note that a 5 day import appears much too long unless you have a really small machine. On a 64GB RAM machine with SSDs you should see something more like half a day. If not, look at https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server)

@doskabouter
Copy link
Author

Thanks for the quick response.
I'll see if I can understand the code good enough to see what I'm missing.

Btw, the machine is not small, I have postgres on windows, 28 cores, 77GB, and osm2pgsql running on linux, 2 cores 45 GB.
Both of them virtual machines using spinning disks...
Running full planet though

@mboeringa
Copy link

Both of them virtual machines using spinning disks...

Honestly, you should switch to SSD. This the no.1 thing you can do to speed up your processing. The type of operations that software like osm2pgsql requires (fast random access), is going to be really problematic on spinning disks, and ever worsening the bigger the extract you try to import.

raspbian-autopush pushed a commit to raspbian-packages/osm2pgsql that referenced this issue Apr 22, 2021
Origin: osm2pgsql-dev/osm2pgsql#1436
Bug: osm2pgsql-dev/osm2pgsql#1447

Indexes are always created in the same schema as the table.

Gbp-Pq: Name 0001-Bug-fix-Remove-schema-name-from-CREATE-INDEX.patch
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants