Missing tables

[domupls]

According to REAME.md, if importing was done with pipeline.lua style file, we should run install_sql.py afterwards.
However, I'm getting this error:

Adding FOREIGN KEY constraints to table nodes...
psql:SQL/after_import.sql:42: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table edges...
psql:SQL/after_import.sql:47: ERROR:  relation "edges" does not exist
psql:SQL/after_import.sql:48: ERROR:  relation "edges" does not exist
Adding FOREIGN KEY constraints to table trip_location...
psql:SQL/after_import.sql:53: ERROR:  relation "trip_locations" does not exist
psql:SQL/after_import.sql:54: ERROR:  relation "trip_locations" does not exist
Adding FOREIGN KEY constraints to table nodes_ways...
psql:SQL/after_import.sql:59: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table nodes_ways_speeds...
psql:SQL/after_import.sql:64: ERROR:  relation "nodes_ways_speeds" does not exist
psql:SQL/after_import.sql:65: ERROR:  relation "nodes_ways_speeds" does not exist

There are indeed missing tables. Can someone answer, whether they should be present in DB or not?

[zlochina]

Hey, @domupls. I think you’ve misread the manual. ‘install_sql.py’ should be run before importing, as for post-processing, file ‘after_import.sql’ should be run instead

[domupls]

Apologies @zlochina, I meant SQL/after_import.sql. install_sql.py was run before, then data was imported with pipeline.lua.

[zlochina]

More likely, you’ve not initialised database with tables. Its done either by executing install_sql.py by hand, or executing this file with ‘py scripts/main.py -i’

[zlochina]

Sorry, i’ve read your message wrongly. You’ve initialised the database. The only thing, which comes to mind, that could be the problem is that you’ve got another schema with the same tables, thus an issue “relation … does not exist” is raised because of ambiguity of the query.

can you tell me if that’s the case, and if so show the output of query ‘show search_path;’

[domupls]

Yes, it turned out that it was in different schema. I got another problem though:

Adding FOREIGN KEY constraints to table ways...
psql:SQL/after_import.sql:35: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_from"
DETAIL:  Key (from)=(1097188755) is not present in table "nodes".
psql:SQL/after_import.sql:36: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_to"
DETAIL:  Key (to)=(1097188755) is not present in table "nodes".

I tried to replicate the problem on my local database with smaller dataset and ran into problems with install_sql.py - one of the problems was that I has search path set to <custom_schema>, public (so all extensions were installed into <custom_schema> beforehand) which resulted in errors like:
type "public.hstore" does not exist
type "public.geometry" does not exist
So mentioning that it expects all extensions to be installed in public schema would be beneficial in README.md.
I'd also suggest mentioning that the database should be empty - or at least specifying in which format should the tables be. In main.sql table is created if it doesn't exist yet but that suggests it's not always the case.

[zlochina]

• So mentioning that it expects all extensions to be installed in public schema would be beneficial in README.md. - For now our main.sql, which sets up so-called basic tables in db, was automatically generated script, which specifies schema (in our case it is public). I guess we could for the time being mention that our operations are public oriented now, but I would like to open a new issue, which would remove this featrue and instead suggest a dynamic version of setup. UPD: opened issue Update SQL/main.sql static schema definition to dynamic one. #88
• About the last part, if exists statements were created automatically once again, it does not have relation to potential pre setup of the database.

Also, I would like to talk about the first issue mentioned in your last message. I do not see how it is related to the problems you've mentioned below, do you still have issues with that? If yes, please notify me so we could resolve this together

[domupls]

I agree that making the project more flexible in terms of schema would be a great improvement. I've already implemented an option to specify the desired schema during data import.

Regarding the first error I mentioned earlier:

psql:SQL/after_import.sql:42: ERROR:  relation "areas" does not exist
Adding FOREIGN KEY constraints to table edges...
psql:SQL/after_import.sql:47: ERROR:  relation "edges" does not exist
psql:SQL/after_import.sql:48: ERROR:  relation "edges" does not exist

This issue was resolved based on your suggestion about the different schema. However, after fixing that, I encountered a new error (I still need help with that):

Adding FOREIGN KEY constraints to table ways...
psql:SQL/after_import.sql:35: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_from"
DETAIL:  Key (from)=(1097188755) is not present in table "nodes".
psql:SQL/after_import.sql:36: ERROR:  insert or update on table "ways" violates foreign key constraint "fk_ways_to"
DETAIL:  Key (to)=(1097188755) is not present in table "nodes".

This occurred on a remote database. When I tried replicating the issue locally, I encountered the errors I mentioned earlier. While these additional errors don't seem directly connected to the main issue (as far as I can tell), I thought it was worth noting since they appeared during my debugging process.

[zlochina]

@domupls, I cannot come up with the straightforward solution, I would need to look at the flow you use, which raised this issue, dataset also could be the factor. Do you wanna discuss it personally?

[domupls]

@zlochina and I resolved the issue by identifying that some nodes were no longer being imported due to changes in osm2pgsql version 2.0.0. Simply using the -x flag is no longer sufficient to import untagged objects. Now, the process_untagged_*() functions must be defined in Lua style files to import all objects. The necessary updates to the files were made in the importer branch