Preventing Infinite Recursion Due To Cyclic or Acyclic Data | Teradata Vantage - Preventing Infinite Recursion Due To Cyclic Data - Advanced SQL Engine - Teradata Database

Recursive views can recurse infinitely when there are cycles in the underlying data they are processing, as they can with acyclic data. See Preventing Infinite Recursion With Acyclic Data. For example, there are likely to be many different routes one could take by means of a transportation system. Such cycles are very unlikely to appear in a bill of material problem, but the example below is presented as means of illustrating the issues.

For example, suppose you have the following parts table.

You want to write a recursive view to process the data in this table, so you write the following recursive view definition.

CREATE RECURSIVE VIEW px (major, minor) AS (
  SELECT major, minor
  FROM parts
  WHERE major = 'p1'
UNION ALL
  SELECT px.major, parts.minor
  FROM px, parts
  WHERE parts.major = px.minor);

Suppose you then ran the following SELECT request against the recursive view you have just defined, px.

SELECT major, minor
FROM px;

The cycles are perhaps easier to visualize when presented symbolically as follows.

      p1 p2 p3 p1 p2 p3 and so on.

Similarly, there is a second cycle, as follows.

      p1 p4 p2 p3 p1 p4 p2 and so on.

A graph of the hierarchy looks like this.

Consider the following informal definitions.

It is only cyclic graphs that present the possibility for infinite recursion; acyclic graphs, by definition, cannot recurse infinitely.

In this example, node p3 feeds back onto node p1 and node p4 feeds back onto node p1 by traversing nodes p2 and p3, so the graph is cyclic.

The cyclic relationships among these parts would cause an infinite recursion to occur, and, ignoring spool overflow, the query would loop forever if not stopped. Harkening back to The Concept of Recursion, it could be said that the fixpoint of this recursion is.

So, what can be done to ensure that a runaway infinite recursion does not happen? After all, attempting to determine whether there are cycles in base table data would often require an extraordinary effort, and to little end.

One possible change to the definition for the example definition for the recursive view px is the following.

CREATE RECURSIVE VIEW px (major, minor, mycount) AS (
  SELECT major, minor, 0 AS mycount
  FROM parts
  WHERE major = 'p1'
UNION ALL
  SELECT px.major, parts.minor, px.mycount + 1
  FROM px, parts
  WHERE parts.major = px.minor
  AND   px.mycount <= 40);

The AND condition px.mycount <= 40 limits the recursion to 41 or fewer cycles. Forty-one because the condition is applied on px.mycount, which is initialized to 0, not 1.

Although not mandatory, good programming practice dictates that you always specify a constraint in the recursive query portion of a recursive view definition that limits the depth of recursion.

Without a limiting condition, runaway recursion is limited only by the quantity of spool space specified for the user who performs the query against the recursive view (see CREATE USER for information about assigning spool space to a user). Keep in mind that specifying a limiting condition does not eliminate the maximum quantity of spool space allotted to a user, so if you specify too large a value for the condition, a user can overflow its spool maximum without ever reaching the terminal value specified by that condition.