Inventoy detail - "recursive query type" error

[niicoooo]

Hi,

I have an issue with the "Inventoy detail" screen, http://127.0.0.1:8000/data/input/operationplanmaterial/. An error is raised when a record is selected.

frePPLe version: commit aa316b3
DB version: PostgreSQL 13.4

Error:

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

Traceback (most recent call last):
  File "/workspaces/frePPLe/freppledb/input/views/utils.py", line 2402, in getData
    cursor.execute(
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/workspaces/frePPLe/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: recursive query "cte" column 5 has type numeric(20,8) in non-recursive term but type numeric overall
LINE 8:                 operationplan.quantity::numeric as pegged_y,
                        ^
HINT:  Cast the output of the non-recursive term to the correct type.

ERROR Error retrieving operationplan: recursive query "cte" column 5 has type numeric(20,8) in non-recursive term but type numeric overall
LINE 8:                 operationplan.quantity::numeric as pegged_y,
                        ^
HINT:  Cast the output of the non-recursive term to the correct type.

Solution:

--- a/freppledb/input/views/utils.py
+++ b/freppledb/input/views/utils.py
@@ -2407,7 +2407,7 @@ class OperationPlanDetail(View):
                 (to_char(operationplan.startdate,'YYYYMMDDHH24MISS')||'/'||coalesce(operationplan.item_id,'')||'/'||operationplan.reference)::varchar as path,
                 operationplan.reference::text,
                 0::numeric as pegged_x,
-                operationplan.quantity::numeric as pegged_y,
+                operationplan.quantity::numeric(20,8) as pegged_y,
                 operationplan.owner_id
                 from operationplan
                 where reference = %%s
@@ -2416,7 +2416,7 @@ class OperationPlanDetail(View):
                 cte.path||'/'||to_char(downstream_opplan.startdate,'YYYYMMDDHH24MISS')||'/'||coalesce(downstream_opplan.item_id,'')||'/'||downstream_opplan.reference,
                 t1.downstream_reference::text,
                 greatest(t1.x, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x)) as pegged_x,
-                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x)) as pegged_y,
+                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x))::numeric(20,8) as pegged_y,
                 downstream_opplan.owner_id
                 from operationplan
                 inner join cte on cte.reference = operationplan.reference
@@ -2500,7 +2500,7 @@ class OperationPlanDetail(View):
                 (to_char(operationplan.startdate,'YYYYMMDDHH24MISS')||'/'||coalesce(operationplan.item_id,'')||'/'||operationplan.reference)::varchar as path,
                 operationplan.reference::text,
                 0::numeric as pegged_x,
-                operationplan.quantity::numeric as pegged_y,
+                operationplan.quantity::numeric(20,8) as pegged_y,
                 operationplan.owner_id
                 from operationplan
                 where reference = %s
@@ -2509,7 +2509,7 @@ class OperationPlanDetail(View):
                 cte.path||'/'||to_char(upstream_opplan.startdate,'YYYYMMDDHH24MISS')||'/'||coalesce(upstream_opplan.item_id,'')||'/'||upstream_opplan.reference,
                 t1.upstream_reference::text,
                 greatest(t1.x, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x)) as pegged_x,
-                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x)) as pegged_y,
+                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x))::numeric(20,8) as pegged_y,
                 upstream_opplan.owner_id
                 from operationplan
                 inner join cte on cte.reference = operationplan.reference
diff --git a/freppledb/output/views/pegging.py b/freppledb/output/views/pegging.py
index bce004336..60f55aa58 100644
--- a/freppledb/output/views/pegging.py
+++ b/freppledb/output/views/pegging.py
@@ -155,7 +155,7 @@ class ReportByDemand(GridReport):
                         (coalesce(operationplan.item_id,'')||'/'||operationplan.reference)::varchar as path,
                         operationplan.reference::text as reference,
                         0::numeric as pegged_x,
-                        operationplan.quantity::numeric as pegged_y
+                        operationplan.quantity::numeric(20,8) as pegged_y
                         from operationplan
                         inner join demand on demand.name = %s
                             inner join lateral
@@ -167,7 +167,7 @@ class ReportByDemand(GridReport):
                         cte.path||'/'||coalesce(upstream_opplan.item_id,'')||'/'||upstream_opplan.reference,
                         t1.upstream_reference::text,
                         greatest(t1.x, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x)) as pegged_x,
-                        least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x)) as pegged_y
+                        least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x))::numeric(20,8) as pegged_y
                         from operationplan
                         inner join cte on cte.reference = operationplan.reference
                         inner join lateral
@@ -297,7 +297,7 @@ class ReportByDemand(GridReport):
                 (coalesce(operationplan.item_id,'')||'/'||operationplan.reference)::varchar as path,
                 operationplan.reference::text,
                 0::numeric as pegged_x,
-                operationplan.quantity::numeric as pegged_y,
+                operationplan.quantity::numeric(20,8) as pegged_y,
                 operationplan.owner_id
                 from operationplan
                 inner join demand on demand.name = %s
@@ -310,7 +310,7 @@ class ReportByDemand(GridReport):
                 cte.path||'/'||coalesce(upstream_opplan.item_id,'')||'/'||upstream_opplan.reference,
                 t1.upstream_reference::text,
                 greatest(t1.x, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x)) as pegged_x,
-                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x)) as pegged_y,
+                least(t1.y, t1.x + (t1.y-t1.x)/(t2.y-t2.x)*(cte.pegged_x-t2.x) + (cte.pegged_y-cte.pegged_x)*(t1.y-t1.x)/(t2.y-t2.x))::numeric(20,8) as pegged_y,
                 upstream_opplan.owner_id
                 from operationplan
                 inner join cte on cte.reference = operationplan.reference

[jdetaeye]

I'm not too keen on making fixes for soon-to-be-obsoleted postgres versions. Postgres 13 is about 5 years old and support will end in november: see https://www.postgresql.org/support/versioning/

In this case the fix is simple, but I can't guarantee we won't be using new postgres features introduced after postgres 13.

Our general policy is to follow the stack with the Ubuntu version we work with.
Currently we use Ubuntu 24, which uses postgres 16 and python 12. We try to support newer database and python versions, but support for older versions is beyond what we have capacity for.