Error upon import IV

[meierrom]

Hi folks,

I'm getting the following error message upon issuing an import command:

python -m qgepqwat2ili qgep import data/heit/heitsia405export_20230505.xtf --log

[...]
psycopg2.errors.ForeignKeyViolation: insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure"
DETAIL: Key (fk_wastewater_structure)=(bauwer0000400074) is not present in table "wastewater_structure".
[...]
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)=(bauwer0000400074) 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': 'netzel0000403284', 'identifier': 'ak2783', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400074'},
{'obj_id': 'netzel0000402944', 'identifier': 'ak463', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000399734'},
{'obj_id': 'netzel0000403526', 'identifier': 'ak2229', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400316'},
{'obj_id': 'netzel0000403422', 'identifier': 'ak2279', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400212'},
{'obj_id': 'netzel0000403423', 'identifier': 'ak2168', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400213'},
{'obj_id': 'netzel0000403424', 'identifier': 'ak2175', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400214'},
{'obj_id': 'netzel0000403049', 'identifier': 'ak262', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000399839'},
{'obj_id': 'netzel0000403425', 'identifier': 'ak2173', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000400215'}

... displaying 10 of 4285 total bound parameter sets ...
{'obj_id': 'netzel0000409713', 'identifier': 'ha2529', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000407032'},
{'obj_id': 'netzel0000409727', 'identifier': 'ha2543', 'remark': None, 'last_modification': datetime.date(2023, 4, 27), 'fk_dataowner': None, 'fk_provider': None, 'fk_wastewater_structure': 'bauwer0000407046'}
)]
[...]

Any ideas what's going wrong?

Kind regards,
Roman

[sjib]

There was another issue with the same type of violation: #118

[meierrom]

Hi @sjib,

There was another issue with the same type of violation: #118
Yeah, thanks! It's not really helping me though.

Anyway, I had a closer look at my issue and I was somehow able to reproduce things with a single sql statement, e.g.

INSERT INTO qgep_od.wastewater_networkelement (obj_id, identifier, remark, last_modification, fk_dataowner, fk_provider, fk_wastewater_structure)
VALUES ('netzel0000403284','ak2783','00000000OG000000', '2023-04-27', '00000000OG000000', '00000000OG000000', 'bauwer0000400074')
;

ERROR: insert or update on table "wastewater_networkelement" violates foreign key constraint "rel_wastewater_networkelement_wastewater_structure"
DETAIL: Key (fk_wastewater_structure)=(bauwer0000400074) is not present in table "wastewater_structure".

Fact is that in my case no data was ever imported into table gep_od.wastewater_structure. This should have been done in an earlier step, right? Therefore it doesn't come as a surprise that qgepqwat2ili is complaining about a missing key related to table gep_od.wastewater_structure.

The question here may be, why qgepqwat2ili failed to import any data into table wastewater_structure and then moving on to table gep_od.wastewater_networkelement without throwing any error message.

Kind regards,
Roman

[meierrom]

Hi folks,

I was trying to narrow down my issue by reducing the xtf file to just two records, one "Normschacht" and one "Abwasserknoten".

Surprisingly the import is failing as well issuing the same error message as above.

What's wrong with my data?

my_export_sia405_ns_knoten_min1x.xtf.gz

Kind regards,
Roman

[sjib]

follow up see also #126

[sjib]

ALTER CONSTRAINTS is possible since Postgres 9.4 and https://www.postgresql.org/docs/current/sql-altertable.html
So we could test if we could set

ALTER TABLE table2
    ALTER CONSTRAINT table2_t1_id_fkey
        DEFERRABLE INITIALLY IMMEDIATE;

in qgep to not have these errors and speed up INTERLIS Import

[sjib]

See also QGEP/QGEP#852

[sjib]

Should be solved with QGEP/datamodel#235