search

qwat / qwat-data-model

TEKSI Water module (project QWAT) - PostgreSQL / postgis Datamodel
https://www.teksi.ch
23 stars 25 forks source link

1.2.8 update fail and weird deltas #195

Closed ponceta closed 6 years ago

ponceta commented 7 years ago

@3nids, @haubourg 1.2.8 Can't be updated automatically.

UPDATE 4468
psql:/home/pulmexa/qwat-data-model-original/update/delta/delta_1.2.8_schema_visible_perf.sql:90: ERREUR:  n'a pas pu supprimer table qwat_od.pipe colonne schema_force_visible_old car d'autres objets en dépendent
DETAIL:  vue qwat_od.vw_export_pipe dépend de table qwat_od.pipe colonne schema_force_visible_old
HINT:  Utilisez DROP ... CASCADE pour supprimer aussi les objets dépendants.

This can be fixed by removing and recreating the vw_export_pipe as in https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L312-L318

I can't understand why https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql is creating some views since update process is rewriting views at https://github.com/qwat/qwat-data-model/blob/master/update/upgrade_db.sh#L178

@sylvainbeo is there a reason why views are not dropped before update?

If not, I would advise avoiding "create view" or "comment on view" statements in the deltas section and keep these in the view creation scripts.

Last, I get some warnings on 1.2.8 : psql:/home/pulmexa/qwat-data-model-original/update/delta/delta_1.2.8_schema_visible_perf.sql:88: ATTENTION: [qwat_sys.if_modified_func] - Trigger detected NULL hstore. ending Can these be ignored?

ponceta commented 7 years ago

OK.

If we drop the views, before updating datamodel, this will cause some issues in:

In delta 1.0.1 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.0.1_06062016.sql#L50 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.0.1_06062016.sql#L70

In delta 1.1.0 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L273 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L279 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L285 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L291

https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L294 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L312-L315

In delta 1.2.2 https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.2.2_new_audit_system.sql#L285

In delta 1.2.3, this : https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.2.3_printmap_add_scale_field.sql#L1 won't be useful

same on delta 1.2.7 : https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.2.7_auto_printmapview.sql#L1

EDIT : Set actions to be taken

m-kuhn commented 7 years ago

Is this list exhaustive?

Issues like this: https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.0.1_06062016.sql#L50

Issues like this: https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.1.0.sql#L294

ponceta commented 7 years ago

I tried so.

I can't garanty it for plpsql functions like in https://github.com/qwat/qwat-data-model/blob/master/update/delta/delta_1.0.0_12092016.sql (as I understand it, it should not be the case)

3nids commented 7 years ago

it should be fine for the functions. just as @m-kuhn suggested, you have to modify the deltas a bit. can you do a PR?

ponceta commented 7 years ago

Removing views creations is not a problem.

But how do you integrate the CREATE VIEW qwat_od.vw_qwat_network_element? Since it is generated with python?

Should I use the existing version?

-- View: qwat_od.vw_qwat_network_element

-- DROP VIEW qwat_od.vw_qwat_network_element;

CREATE OR REPLACE VIEW qwat_od.vw_qwat_network_element AS 
 SELECT
        CASE
            WHEN subscriber.id IS NOT NULL THEN 'subscriber'::qwat_od.element_type
            WHEN part.id IS NOT NULL THEN 'part'::qwat_od.element_type
            WHEN installation.id IS NOT NULL THEN 'installation'::qwat_od.element_type
            WHEN hydrant.id IS NOT NULL THEN 'hydrant'::qwat_od.element_type
            WHEN samplingpoint.id IS NOT NULL THEN 'samplingpoint'::qwat_od.element_type
            WHEN valve.id IS NOT NULL THEN 'valve'::qwat_od.element_type
            WHEN meter.id IS NOT NULL THEN 'meter'::qwat_od.element_type
            ELSE 'element'::qwat_od.element_type
        END AS element_type,
    element.id,
    element.fk_district,
    element.fk_pressurezone,
    element.fk_printmap,
    element._printmaps,
    element._geometry_alt1_used,
    element._geometry_alt2_used,
    element._pipe_node_type,
    element._pipe_orientation,
    element._pipe_schema_visible,
    element.geometry,
    element.geometry_alt1,
    element.geometry_alt2,
    element.update_geometry_alt1,
    element.update_geometry_alt2,
    element.identification,
    element.fk_distributor,
    element.fk_status,
    element.fk_folder,
    element.fk_locationtype,
    element.fk_precision,
    element.fk_precisionalti,
    element.fk_object_reference,
    element.altitude,
    element.year,
    element.year_end,
    element.orientation,
    element.remark,
    element.schema_force_visible,
    element.label_1_visible,
    element.label_1_x,
    element.label_1_y,
    element.label_1_rotation,
    element.label_1_text,
    element.label_2_visible,
    element.label_2_x,
    element.label_2_y,
    element.label_2_rotation,
    element.label_2_text,
        CASE
            WHEN valve.id IS NOT NULL THEN valve.networkseparation
            WHEN installation.id IS NOT NULL THEN installation.networkseparation
            ELSE NULL::boolean
        END AS networkseparation,
        CASE
            WHEN subscriber.id IS NOT NULL THEN subscriber.parcel
            WHEN installation.id IS NOT NULL THEN installation.parcel
            WHEN meter.id IS NOT NULL THEN meter.parcel
            ELSE NULL::character varying
        END AS parcel,
        CASE
            WHEN subscriber.id IS NOT NULL THEN subscriber.fk_pipe
            WHEN part.id IS NOT NULL THEN part.fk_pipe
            WHEN valve.id IS NOT NULL THEN valve.fk_pipe
            WHEN meter.id IS NOT NULL THEN meter.fk_pipe
            ELSE NULL::integer
        END AS fk_pipe,
    subscriber.fk_subscriber_type,
    subscriber.flow_current,
    subscriber.flow_planned,
    part.fk_part_type,
    installation.installation_type,
    installation.name,
    installation.fk_parent,
    installation.fk_remote,
    installation.fk_watertype,
    installation.eca,
    installation.open_water_surface,
    installation.geometry_polygon,
    installation.fk_source_type,
    installation.fk_source_quality,
    installation.flow_lowest,
    installation.flow_average,
    installation.flow_concession,
    installation.contract_end,
    installation.gathering_chamber,
    installation.fk_pump_type,
    installation.fk_pipe_in,
    installation.fk_pipe_out,
    installation.fk_pump_operating,
    installation.no_pumps,
    installation.rejected_flow,
    installation.manometric_height,
    installation.fk_overflow,
    installation.fk_tank_firestorage,
    installation.storage_total,
    installation.storage_supply,
    installation.storage_fire,
    installation.altitude_overflow,
    installation.altitude_apron,
    installation.height_max,
    installation.fire_valve,
    installation.fire_remote,
    installation._litrepercm,
    installation.cistern1_fk_type,
    installation.cistern1_dimension_1,
    installation.cistern1_dimension_2,
    installation.cistern1_storage,
    installation._cistern1_litrepercm,
    installation.cistern2_fk_type,
    installation.cistern2_dimension_1,
    installation.cistern2_dimension_2,
    installation.cistern2_storage,
    installation._cistern2_litrepercm,
    installation.sanitization_uv,
    installation.sanitization_chlorine_liquid,
    installation.sanitization_chlorine_gazeous,
    installation.sanitization_ozone,
    installation.filtration_membrane,
    installation.filtration_sandorgravel,
    installation.flocculation,
    installation.activatedcharcoal,
    installation.settling,
    installation.treatment_capacity,
    installation.flow_meter,
    installation.water_meter,
    installation.manometer,
    installation.depth,
    installation.no_valves,
    installation.fk_pressurecontrol_type,
    hydrant.fk_provider,
    hydrant.fk_model_sup,
    hydrant.fk_model_inf,
    hydrant.fk_material,
    hydrant.fk_output,
    hydrant.underground,
    hydrant.marked,
    hydrant.pressure_static,
    hydrant.pressure_dynamic,
    hydrant.flow,
    hydrant.observation_date,
    hydrant.observation_source,
    valve.fk_valve_type,
    valve.fk_valve_function,
    valve.fk_valve_actuation,
    valve.fk_handle_precision,
    valve.fk_handle_precisionalti,
    valve.fk_maintenance,
    valve.diameter_nominal,
    valve.closed,
    valve.handle_altitude,
    valve.handle_geometry
   FROM qwat_od.vw_node_element element
     LEFT JOIN qwat_od.subscriber subscriber ON element.id = subscriber.id
     LEFT JOIN qwat_od.part part ON element.id = part.id
     LEFT JOIN qwat_od.vw_qwat_installation installation ON element.id = installation.id
     LEFT JOIN qwat_od.hydrant hydrant ON element.id = hydrant.id
     LEFT JOIN qwat_od.samplingpoint samplingpoint ON element.id = samplingpoint.id
     LEFT JOIN qwat_od.valve valve ON element.id = valve.id
     LEFT JOIN qwat_od.meter meter ON element.id = meter.id;

ALTER TABLE qwat_od.vw_qwat_network_element
  OWNER TO postgres;
GRANT ALL ON TABLE qwat_od.vw_qwat_network_element TO postgres;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE qwat_od.vw_qwat_network_element TO qwat_viewer;
GRANT ALL ON TABLE qwat_od.vw_qwat_network_element TO qwat_user;
GRANT ALL ON TABLE qwat_od.vw_qwat_network_element TO qwat_manager;

-- Trigger: tr_vw_qwat_network_element_delete on qwat_od.vw_qwat_network_element

-- DROP TRIGGER tr_vw_qwat_network_element_delete ON qwat_od.vw_qwat_network_element;

CREATE TRIGGER tr_vw_qwat_network_element_delete
  INSTEAD OF DELETE
  ON qwat_od.vw_qwat_network_element
  FOR EACH ROW
  EXECUTE PROCEDURE qwat_od.ft_vw_qwat_network_element_delete();

-- Trigger: tr_vw_qwat_network_element_insert on qwat_od.vw_qwat_network_element

-- DROP TRIGGER tr_vw_qwat_network_element_insert ON qwat_od.vw_qwat_network_element;

CREATE TRIGGER tr_vw_qwat_network_element_insert
  INSTEAD OF INSERT
  ON qwat_od.vw_qwat_network_element
  FOR EACH ROW
  EXECUTE PROCEDURE qwat_od.ft_vw_qwat_network_element_insert();

-- Trigger: tr_vw_qwat_network_element_update on qwat_od.vw_qwat_network_element

-- DROP TRIGGER tr_vw_qwat_network_element_update ON qwat_od.vw_qwat_network_element;

CREATE TRIGGER tr_vw_qwat_network_element_update
  INSTEAD OF UPDATE
  ON qwat_od.vw_qwat_network_element
  FOR EACH ROW
  EXECUTE PROCEDURE qwat_od.ft_vw_qwat_network_element_update();
ponceta commented 7 years ago

This should be synchronously with the pre.sql statement to drop views.

m-kuhn commented 7 years ago

Not sure if the views changed meanwhile. If yes this needs to be a snapshot of the version back at this time.

Either run the appropriate script in ./ordinary_data/views/inheritance/*.py (I think?) with the old state of the db or directly copy it from pgadmin in the old state of the db.

ponceta commented 7 years ago

@m-kuhn I edited https://github.com/qwat/qwat-data-model/issues/195#issuecomment-332225528

@3nids why is installation_type deleted at every rewrite_views? (I searched for half an hour before seeing that it was not a view but a type ;) ) https://github.com/qwat/qwat-data-model/blob/master/ordinary_data/views/drop_views.sql#L53

I don't now how to cherry pick only the wished commits but I have almost what it needs to do a clean update here : https://github.com/ponceta/qwat-data-model/pull/1/commits/ade2b0f4034d736f0ed804ff19a7f0df63ac4a79

(except the qwat_init.log and the pre_sql script)

haubourg commented 6 years ago

I spend half a day on that.

I pushed a fix for 1.2.8 in a new branch here : https://github.com/qwat/qwat-data-model/commit/664ece24d3cc4ef0aed9f94d9ae5426b4ff1ef4e

But I also had to fix the upgrade_db.sh which had issues verifying version tags, and did not drop and inserted views before applying deltas. If migrations test are working ok for you, I'll need to fix also the actual migration loop.

I am now not quite comfortable on how to merge that fix int the commit history on master branch and move the 1.2.8 tag on top of it. Th whole problem is that the upgrade process makes migration fail, so just publishing a 1.2.8.1 is not working in our case. We really need to change 1.2.8 version delta file and upgrade_db.sh.

PUM will allow a more simple workflow for sure.

haubourg commented 6 years ago

Migration works now in local with changes in upgrade_db.sh in 1.2.8 @sbe can you check that on monday and help me do proper merge in master. I think we must remove old 1.2.8 tag and delta and move to 1.2.8.1 but I'm not sure how to insert the commits before new commits for for 1.2.9

haubourg commented 6 years ago

Seems all green now with QWAT PUM integration