DSS Import: Handling of IntegrityError: "not present in table"

[urskaufmann]

Describe the bug
Get this error when importing dss_2015_lv95 - data. But the mentioned key ch21ha8pME000029 in table maintenance_event exists.
Propably there is a wrong order when importing the data (re_maintenance_event_wastewater_structure before maintenance_event)?

Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event" DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event". [SQL: INSERT INTO qgep_od.re_maintenance_event_wastewater_structure (fk_wastewater_structure, fk_maintenance_event) VALUES (%(fk_wastewater_structure)s, %(fk_maintenance_event)s) RETURNING qgep_od.re_maintenance_event_wastewater_structure.obj_id] [parameters: {'fk_wastewater_structure': 'A001gni5bhr3714o', 'fk_maintenance_event': 'ch21ha8pME000029'}] (Background on this error at: http://sqlalche.me/e/13/gkpj)
Traceback (most recent call last):
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: insert or update on table "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event"
DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event".

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

Traceback (most recent call last):
File "C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui\gui_import.py", line 204, in commit_session
self.session.commit()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 1046, in commit
self.transaction.commit()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 504, in commit
self.prepare_impl()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 483, in _prepare_impl
self.session.flush()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2540, in flush
self.flush(objects)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2682, in _flush
transaction.rollback(_capture_exception=True)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in exit
compat.raise(
File "C:\PROGRA~1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise
raise exception
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\session.py", line 2642, in _flush
flush_context.execute()
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 419, in execute
n.execute_aggregate(self, set_)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 521, in execute_aggregate
self.execute(uow)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 586, in execute
persistence.save_obj(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 239, in save_obj
_emit_insert_statements(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 1135, in _emit_insert_statements
result = cached_connections[connection].execute(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
return meth(self, multiparams, params)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
self.handle_dbapi_exception(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in handle_dbapi_exception
util.raise(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise
raise exception
File "C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\PROGRA1\QGIS3\apps\Python39\lib\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 "re_maintenance_event_wastewater_structure" violates foreign key constraint "rel_maintenance_event_wastewater_structure_maintenance_event"
DETAIL: Key (fk_maintenance_event)=(ch21ha8pME000029) is not present in table "maintenance_event".

[SQL: INSERT INTO qgep_od.re_maintenance_event_wastewater_structure (fk_wastewater_structure, fk_maintenance_event) VALUES (%(fk_wastewater_structure)s, %(fk_maintenance_event)s) RETURNING qgep_od.re_maintenance_event_wastewater_structure.obj_id]
[parameters: {'fk_wastewater_structure': 'A001gni5bhr3714o', 'fk_maintenance_event': 'ch21ha8pME000029'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Python-Version: 3.9.5 (tags/v3.9.5:0a7dcbd, May 3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)]
QGIS-Version: 3.28.5-Firenze Firenze, 50adba36f2

Python-Pfad:
C:/PROGRA1/QGIS3/apps/qgis-ltr/./python
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins
C:/PROGRA1/QGIS3/apps/qgis-ltr/./python/plugins
C:\PROGRA1\QGIS3\apps\grass\grass82\etc\python
C:\Windows\system32
C:\Program Files\QGIS3\bin\python39.zip
C:\PROGRA1\QGIS3\apps\Python39\DLLs
C:\PROGRA1\QGIS3\apps\Python39\lib
C:\Program Files\QGIS3\bin
C:\PROGRA1\QGIS3\apps\Python39
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\win32
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\win32\lib
C:\PROGRA1\QGIS3\apps\Python39\lib\site-packages\Pythonwin
C:/Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python
//holingerag.intra/DEM/DEMProfiles/KAU/Desktop/test_dss_ie
C:\Users/KAU/AppData/Roaming/QGIS/QGIS3\profiles\dss_ie/python/plugins\qgepplugin\qgepqwat2ili\qgepqwat2ili\gui....
.
To Reproduce
Exact steps to reproduce the behavior:
Import DSS-data with maintenance_events and re_maintenace_event_wastewater_structure data

Expected behavior
no error

Screenshots / data

Desktop (please complete the following information):

• QGEP plugin version 20230411 test vsa-dss
• QGEP datamodel version 1.6.0
• QGEP project file version 1.6.0
• QGIS Version 3.28.5
• OS WIN 10

Additional context
if I delete the re_maintenance_event_wastewater_structure records, I get a similar error:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "throttle_shut_off_unit" violates foreign key constraint "rel_throttle_shut_off_unit_wastewater_node" DETAIL: Key (fk_wastewater_node)=(A001gni5btl9vhqi) is not present in table "wastewater_node". [SQL: INSERT INTO qgep_od.throttle_shut_off_unit (obj_id, actuation, adjustability, control, cross_section, effective_cross_section, gross_costs, identifier, kind, manufacturer, remark, signal_transmission, subsidies, throttle_unit_opening_current, throttle_unit_opening_current_optimized, last_modification, fk_dataowner, fk_provider, fk_wastewater_node, fk_control_center, fk_overflow) VALUES (%(obj_id)s, %(actuation)s, %(adjustability)s, %(control)s, %(cross_section)s, %(effective_cross_section)s, %(gross_costs)s, %(identifier)s, %(kind)s, %(manufacturer)s, %(remark)s, %(signal_transmission)s, %(subsidies)s, %(throttle_unit_opening_current)s, %(throttle_unit_opening_current_optimized)s, %(last_modification)s, %(fk_dataowner)s, %(fk_provider)s, %(fk_wastewater_node)s, %(fk_control_center)s, %(fk_overflow)s)] [parameters: ({'obj_id': 'ch21ha8pTS000000', 'actuation': 3157, 'adjustability': 3160, 'control': 3165, 'cross_section': None, 'effective_cross_section': None, 'gross_costs': None, 'identifier': '330 Drosselschieber', 'kind': 135, 'manufacturer': 'DN 300, Sohle 90cm unterhalb "Leapingwehr"', 'remark': None, 'signal_transmission': None, 'subsidies': None, 'throttle_unit_opening_current': 115, 'throttle_unit_opening_current_optimized': None, 'last_modification': datetime.date(2021, 11, 18), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_node': 'A001gni5btl9vhqi', 'fk_control_center': None, 'fk_overflow': None}, {'obj_id': 'ch21ha8pTS000001', 'actuation': 3157, 'adjustability': 3160, 'control': 3165, 'cross_section': None, 'effective_cross_section': None, 'gross_costs': None, 'identifier': '700 Drosselschieber', 'kind': 135, 'manufacturer': 'DN 250', 'remark': None, 'signal_transmission': None, 'subsidies': None, 'throttle_unit_opening_current': 125, 'throttle_unit_opening_current_optimized': None, 'last_modification': datetime.date(2021, 11, 18), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_node': 'ch21ha8pWN000006', 'fk_control_center': None, 'fk_overflow': None})] (Background on this error at: http://sqlalche.me/e/13/gkpj)

[urskaufmann]

This error was not a problem of dss-import. The xtf-file was wrong...
The question now is: why was this error in the xtf-file not found when validation the xtf.
Why comes such error-messages to the user (and are not found by the software before)?

[sjib]

• In VSA-DSS Import/Export the class maintenance_event was not yet configured as this needs to be treated differently than in VSA-KEK where it goes together with the subclass examination.

[sjib]

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "throttle_shut_off_unit" violates foreign key constraint "rel_throttle_shut_off_unit_wastewater_node" DETAIL: Key (fk_wastewater_node)=(A001gni5btl9vhqi) is not present in table "wastewater_node".

@urskaufmann If this was a data error - was the wastewater_node missing in your data and it did not find that in the validation process?
That would be very strange.

[sjib]

List of difference VSA-KEK 2019/ 2020 and qgep see #91 (comment)

[sjib]

Should be solved with QGEP/datamodel#235