Skip to content

ERROR: Recursion detected in view expansion

ISSUE:
You may see this error when trying to query a view or create a view on another view.

ERROR: Recursion detected in view expansion

CAUSE:
Possible issue is that your view is broken and is pointing to itself. Just generate a ddl of this view and you will find something like below i.e. a view pointing to itself.

create or replace view MyView1
as select * from MyView1

RESOLUTION:
Netezza do not allow creating a view pointing to itself or any object with same name in a database. But there is a bug in Netezza which allows you to create a view pointing to itself if view already exist.

To resolve the issue you have to recreate a view with correct defination for the query to run successfully.
MORE EXPLANATION:
1) If you create a brand new view pointing to itself you will get error:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1;
ERROR: relation does not exist TESTDB.ADMIN.MYVIEW1

2) Now, if you create this view as valid view it will run fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from T1;
CREATE VIEW

3) Run select on view and it will run fine:
TESTDB.ADMIN(ADMIN)=> select * from MyView1;
<Runs Successfully>

4) Now, again try to recreate this view as we tried to create in step 1. It will work fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1;
CREATE VIEW

5) Run select on view and it will throw error:
TESTDB.ADMIN(ADMIN)=> select * from MyView1;
ERROR: Recursion detected in view expansion