Problem:
Custom reports which are scheduled for daily is failing
AMP : 8.2.x
Diagnostics:
When we open the failed reports we would see the following error message :
“ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. CONTEXT: SQL statement "SELECT ap.upstream_device_id FROM ap LEFT JOIN ap upstream on ap.upstream_device_id = upstream.id WHERE ap.id = ap_id AND ap.upstream_device_id IS NOT NULL AND upstream.is_up = 0" PL/pgSQL function root_cause_device_id_for_ap_id(bigint,boolean) line 7 at SQL statement SQL statement "SELECT”.
We see this error 'stack depth limit exceeded' because there is circular references for upstream device in the database. And stored procedure root_cause_device_id_for_ap_id is recursive and will reach the stack limit.
The following Query will provide us the database status:
# dbc 'select id, upstream_device_id from ap where upstream_device_id is not null;'
id | upstream_device_id
------+--------------------
4718 | 831
4721 | 831
4720 | 831
3787 | 3787
From the above output we see that the ID - 3787 is circular references for upstream device in the database.
Solution
We need to manually set 3787's upstream_device_id to null to fix the error by executing the following Query;
root@mercury]# dbc "update ap SET upstream_device_id = NULL where id = 3787;"