Error upon import V - fk_wastewater_structure not present in table wastewater_structure

[meierrom]

Hi folks,

This issue seems to be related to a bug in qgepqwat2ili.

This is the xtf-file containing just two entries:

my_export_sia405_ns_knoten_min1x.xtf.zip

I'm getting the following output when issuing the following import command:

python -m qgepqwat2ili qgep import arbon/my_export_sia405_ns_knoten_min1x.xtf --log

roman@tjener:~/qgisfolder$ python -m qgepqwat2ili qgep import arbon/my_export_sia405_ns_knoten_min1x.xtf --log
INFO VALIDATING XTF DATA...
INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ilivalidator-1.11.9/ilivalidator-1.11.9.jar" --modeldir "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili" --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093057.ilivalidate.log" "arbon/my_export_sia405_ns_knoten_min1x.xtf"
INFO CONNECTING TO DATABASE...
INFO DROPPING THE SCHEMA pg2ili_abwasser...
INFO CREATING THE SCHEMA pg2ili_abwasser...
INFO ILIDB SCHEMAIMPORT INTO pg2ili_abwasser...
INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ili2pg-4.5.0-bindist/ili2pg-4.5.0.jar" --schemaimport --dbhost "localhost" --dbport "5434" --dbusr "" --dbpwd "" --dbdatabase "" --dbschema pg2ili_abwasser --setupPgExt --createGeomIdx --createFk --createFkIdx --createTidCol --importTid --noSmartMapping --defaultSrsCode 2056 --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093101.ilicreate.log" --nameLang de /skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili/VSA_KEK_2019_2_d_LV95-20210120.ili
INFO IMPORTING XTF DATA...
INFO EXECUTING: "java" -jar "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/bin/ili2pg-4.5.0-bindist/ili2pg-4.5.0.jar" --import --deleteData --dbhost "localhost" --dbport "5434" --dbusr "" --dbpwd "" --dbdatabase "" --dbschema "pg2ili_abwasser" --modeldir "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/ili" --disableValidation --skipReferenceErrors --createTidCol --noSmartMapping --defaultSrsCode 2056 --log "arbon/my_export_sia405_ns_knoten_min1x.xtf.230517093120.iliimport.log" "arbon/my_export_sia405_ns_knoten_min1x.xtf"
INFO Importing ABWASSER.organisation, ABWASSER.metaattribute -> QGEP.organisation
INFO done
INFO Importing ABWASSER.kanal, ABWASSER.metaattribute -> QGEP.channel
INFO done
INFO Importing ABWASSER.normschacht, ABWASSER.metaattribute -> QGEP.manhole
WARNING Could not find value None in value list "qgep_vl.wastewater_structure_accessibility". Setting to None instead.
WARNING Could not find value None in value list "qgep_vl.wastewater_structure_financing". Setting to None instead.
WARNING Could not find value None in value list "qgep_vl.wastewater_structure_rv_construction_type". Setting to None instead.
WARNING Could not find value None in value list "qgep_vl.wastewater_structure_structure_condition". Setting to None instead.
WARNING Could not find value None in value list "qgep_vl.manhole_material". Setting to None instead.
WARNING Could not find value None in value list "qgep_vl.manhole_surface_inflow". Setting to None instead.
INFO done
INFO Importing ABWASSER.einleitstelle, ABWASSER.metaattribute -> QGEP.discharge_point
INFO done
INFO Importing ABWASSER.spezialbauwerk, ABWASSER.metaattribute -> QGEP.special_structure
INFO done
INFO Importing ABWASSER.versickerungsanlage, ABWASSER.metaattribute -> QGEP.infiltration_installation
INFO done
INFO Importing ABWASSER.rohrprofil, ABWASSER.metaattribute -> QGEP.pipe_profile
INFO done
INFO Importing ABWASSER.haltungspunkt, ABWASSER.metaattribute -> QGEP.reach_point
INFO done
INFO Importing ABWASSER.abwasserknoten, ABWASSER.metaattribute -> QGEP.wastewater_node
INFO done
INFO Importing ABWASSER.haltung, ABWASSER.metaattribute -> QGEP.reach
INFO done
INFO Importing ABWASSER.trockenwetterfallrohr, ABWASSER.metaattribute -> QGEP.dryweather_downspout
INFO done
INFO Importing ABWASSER.einstiegshilfe, ABWASSER.metaattribute -> QGEP.access_aid
INFO done
INFO Importing ABWASSER.trockenwetterrinne, ABWASSER.metaattribute -> QGEP.dryweather_flume
INFO done
INFO Importing ABWASSER.deckel, ABWASSER.metaattribute -> QGEP.cover
INFO done
INFO Importing ABWASSER.bankett, ABWASSER.metaattribute -> QGEP.benching
INFO done
INFO Importing ABWASSER.untersuchung, ABWASSER.metaattribute -> QGEP.examination
INFO done
INFO Importing ABWASSER.normschachtschaden, ABWASSER.metaattribute -> QGEP.damage_manhole
INFO done
INFO Importing ABWASSER.kanalschaden, ABWASSER.metaattribute -> QGEP.damage_channel
INFO done
INFO Importing ABWASSER.datentraeger, ABWASSER.metaattribute -> QGEP.data_media
INFO done
INFO Importing ABWASSER.datei, ABWASSER.metaattribute -> QGEP.file
INFO done
Traceback (most recent call last):
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
cursor, statement, parameters, context
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure"
DETAIL: Key (fk_wastewater_structure)=(ch13p7mzMA002320) is not present in table "wastewater_structure".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/lib/python3.7/runpy.py", line 193, in _run_module_as_main
"main", mod_spec)
File "/usr/lib/python3.7/runpy.py", line 85, in run_code
exec(code, run_globals)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/main.py", line 8, in
main(sys.argv[1:])
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/init.py", line 175, in main
qgep_import()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/qgepqwat2ili/qgep/import.py", line 1103, in qgep_import
qgep_session.commit()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
self.transaction.commit()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 504, in commit
self._prepare_impl()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
self.session.flush()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
self._flush(objects)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
transaction.rollback(capture_exception=True)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
with_traceback=exc_tb,
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise
raise exception
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2642, in flush
flush_context.execute()
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 419, in execute
n.execute_aggregate(self, set)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 698, in execute_aggregate
mapper, [self.state] + [r.state for r in our_recs], uow
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
insert,
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1083, in _emit_insert_statements
c = cached_connections[connection].execute(statement, multiparams)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
distilled_params,
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in execute_context
e, statement, parameters, cursor, context
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise
raise exception
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
cursor, statement, parameters, context
File "/skole/tjener/home0/roman/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure"
DETAIL: Key (fk_wastewater_structure)=(ch13p7mzMA002320) is not present in table "wastewater_structure".

[SQL: INSERT INTO qgep_od.wastewater_networkelement (obj_id, identifier, remark, last_modification, fk_dataowner, fk_provider, fk_wastewater_structure) VALUES (%(obj_id)s, %(identifier)s, %(remark)s, %(last_modification)s, %(fk_dataowner)s, %(fk_provider)s, %(fk_wastewater_structure)s)]
[parameters: {'obj_id': 'ch13p7mzWN006889', 'identifier': 'SE5', 'remark': None, 'last_modification': datetime.date(2018, 4, 4), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'ch13p7mzMA002320'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
roman@tjener:~/qgisfolder$

Any ideas, what's going wrong?

Kind regards,
Roman

[sjib]

Thanks @meierrom for your helpful example with just one manhole and one related wastewater_node.

I was able to reproduce your error with QGIS 3.28.4 and the QGEP Plugin 1.6.1 and Datamodel 1.6.0

qgeg_imporroman_manhole_wwn_fk_wastewater_structure_error.txt

[sjib]

In the importschema pg2ili_abwasser the two datasets have been imported. The error seems to be on the step on copying data from pg2ili_abwasser to qgep_od

to do:

• check import order of classes - manhole(wastewater_structure) should be added before wastewater_node (wastewater_network_element) https://github.com/QGEP/qgepqwat2ili/blob/master/qgepqwat2ili/qgep/import_.py
• check if this works as expected - example above has no cyclic dependencies
  

  qgepqwat2ili/qgepqwat2ili/qgep/import_.py

  Line 41 in c2d6a88

  qgep_session.execute("SET CONSTRAINTS ALL DEFERRED;")

• Could this be a rights management problem?
• Side effect because there are not organisations in the data set?
• other?

[meierrom]

Hi @sjib,

Thanks for reproducing this issue!

I went through the check list and none of the points seem relevant here imho.

I think this issue is rather related to commits not issued at all or in a timely matter. After adding an additional qgep_session.commit() around line 325 in file import_.py the issue is fixed. I don't know why these commit issues occur.

[...]
material__REL=get_vl_instance(QGEP.manhole_material, row.material),
surface_inflow__REL=get_vl_instance(QGEP.manhole_surface_inflow, row.oberflaechenzulauf),
)
qgep_session.add(manhole)
print(".", end="")

    qgep_session.commit()

[...]

I hope this helps somehow.

Kind regards,
Roman

[meierrom]

Hi @sjib,

The following is another very simple xtf file. This one is however based on a single reach instead.

The import results in a similar error message just like the one above. You should be able to reproduce it just like you did above.

my_export_sia405_reach_min1x.xtf.zip

I hope this can helps to narrow down these commit issues or whatever is causing this.

Kind regards,
Roman

[sjib]

@meierrom Thanks for this testdata set.
I had no problems importing your reach dataset with the QGEP plugin.

Plugin 1.6.1, Datamodel 1.6.0. QGIS 3.28.4, postgres 12

I will dig deeper in the code and check with your suggestion above of an additional commit()

@meierrom Which postgres version are you using?

[sjib]

If I run 91_mainnode.sql I also see the manholes:

91_maincover_mainnode.zip

[meierrom]

@meierrom Which postgres version are you using?

I'm currently using v15. However, is there a recommended version? I never actually came across an official recommendation.

Kind regards,
Roman

P.S.
I noticed some time ago, that issues can arise when using the wrong version of postgresql. Even currently I have issues with two missing views, being qgep_od.vw_network_segment and qgep_od.vw_network_node resulting in missing data under Topology. I do suspect an issue with v15 compatibility. However, I'm not getting any error message in QGIS, which is rather strange.

[meierrom]

Hi @sjib,

I switched from Postgresql 15 to 12 and reinstalled the qgep plugin. The import is working now.

Thanks!

[ponceta]

@meierrom here are recommended versions :

https://github.com/teksi/Home/wiki/TEKSI-modules-releases-and-roadmap

@ponceta we should add some reference from the documentation to this matrix.

[sjib]

Should be solved with QGEP/datamodel#235