Timeout issue when moving a structure connected to 3 reaches

[rbovard]

I'm facing an error when I try to move a structure connected to 3 reaches:

Error:

CRITICAL    Layer Ouvrages : PostGIS error while changing geometry values: ERROR: canceling statement due to statement timeout
             CONTEXT: SQL statement "UPDATE qgep_od.wastewater_structure ws
              SET _depth = depth
              FROM (
              SELECT WS.obj_id, CO.level - COALESCE(MIN(NO.bottom_level), MIN(RP.level)) as depth
              FROM qgep_od.wastewater_structure WS
              LEFT JOIN qgep_od.cover CO on WS.fk_main_cover = CO.obj_id
              LEFT JOIN qgep_od.wastewater_networkelement NE ON NE.fk_wastewater_structure = WS.obj_id
              RIGHT JOIN qgep_od.wastewater_node NO on NO.obj_id = NE.obj_id
              LEFT JOIN qgep_od.reach_point RP ON RP.fk_wastewater_networkelement = NE.obj_id
              WHERE _all OR WS.obj_id = _obj_id
              GROUP BY WS.obj_id, CO.level
              ) ws_depths
              where ws.obj_id = ws_depths.obj_id"
             PL/pgSQL function qgep_od.update_depth(text,boolean) line 6 at SQL statement
             PL/pgSQL function qgep_od.on_wasterwaternode_change() line 19 at EXECUTE
             SQL statement "UPDATE qgep_od.wastewater_node n
             SET
              _function_hierarchic = function_hierarchic,
              _usage_current = usage_current
             FROM(
              SELECT DISTINCT ON (ne.obj_id) ne.obj_id AS ne_obj_id,
              COALESCE(first_value(CH_from.function_hierarchic) OVER w, first_value(CH_to.function_hierarchic) OVER w) AS function_hierarchic,
              COALESCE(first_value(CH_from.usage_current) OVER w, first_value(CH_to.usage_current) OVER w) AS usage_current,
              rank() OVER w AS hierarchy_rank
              FROM
              qgep_od.wastewater_networkelement ne
              LEFT JOIN qgep_od.reach_point rp ON ne.obj_id = rp.fk_wastewater_networkelement
              LEFT JOIN qgep_od.reach re_from ON re_from.fk_reach_point_from = rp.obj_id
              LEFT JOIN qgep_od.wastewater_networkelement ne_from ON ne_from.obj_id = re_from.obj_id
              LEFT JOIN qgep_od.channel CH_from ON CH_from.obj_id = ne_from.fk_wastewater_structure
              LEFT JOIN qgep_vl.channel_function_hierarchic vl_fct_hier_from ON CH_from.function_hierarchic = vl_fct_hier_from.code
              LEFT JOIN qgep_vl.channel_usage_current vl_usg_curr_from ON CH_from.usage_current = vl_usg_curr_from.code
              LEFT JOIN qgep_od.reach re_to ON re_to.fk_reach_point_to = rp.obj_id
              LEFT JOIN qgep_od.wastewater_networkelement ne_to ON ne_to.obj_id = re_to.obj_id
              LEFT JOIN qgep_od.channel CH_to ON CH_to.obj_id = ne_to.fk_wastewater_structure
              LEFT JOIN qgep_vl.channel_function_hierarchic vl_fct_hier_to ON CH_to.function_hierarchic = vl_fct_hier_to.code
              LEFT JOIN qgep_vl.channel_usage_current vl_usg_curr_to ON CH_to.usage_current = vl_usg_curr_to.code
              WHERE _all OR ne.obj_id = _obj_id
              WINDOW w AS ( PARTITION BY ne.obj_id ORDER BY vl_fct_hier_from.order_fct_hierarchic ASC NULLS LAST, vl_fct_hier_to.order_fct_hierarchic ASC NULLS LAST,
              vl_usg_curr_from.order_usage_current ASC NULLS LAST, vl_usg_curr_to.order_usage_current ASC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
             ) symbology_ne
             WHERE symbology_ne.ne_obj_id = n.obj_id"
             PL/pgSQL function qgep_od.update_wastewater_node_symbology(text,boolean) line 11 at SQL statement
             PL/pgSQL function qgep_od.ws_symbology_update_by_reach() line 27 at EXECUTE
             SQL statement "UPDATE qgep_od.reach RE
              SET progression_geometry =
              ST_ForceCurve( ST_SetPoint(
              ST_CurveToLine( RE.progression_geometry ),
              ST_NumPoints(RE.progression_geometry) - 1,
              ST_SetSRID( ST_MakePoint(
              ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression_geometry)), ST_Y(ST_EndPoint(RE.progression_geometry))), dx, dy )),
              ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression_geometry)), ST_Y(ST_EndPoint(RE.progression_geometry))), dx, dy )),
              ST_Z(ST_PointN(RE.progression_geometry, 1))), 2056 )
              ) )
              WHERE fk_reach_point_to IN
              (
              SELECT RP.obj_id FROM qgep_od.reach_point RP
              LEFT JOIN qgep_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
              WHERE NE.fk_wastewater_structure = NEW.obj_id
              )"
             PL/pgSQL function qgep_od.ft_vw_qgep_wastewater_structure_update() line 211 at SQL statement

Which is strange, because I have no timeout defined:

SELECT name, setting, unit, extra_desc FROM pg_settings WHERE name IN ('statement_timeout');

name             |setting|unit|extra_desc                         |
-----------------+-------+----+-----------------------------------+
statement_timeout|0      |ms  |A value of 0 turns off the timeout.|

When I try to move a structure connected to only 2 reaches, it works:

Any idea of what's going on?

[rbovard]

The problem was solved by making a backup and restore of my database. Really strange!

Keep this in mind if anyone encounters a similar problem...