Already have an account? Sign in

Redshift View Dependency Errors

ERROR: cannot drop table [schema_name].[table_name] column [column_name] because other objects depend on it

Hint: Use DROP … CASCADE to drop the dependent objects too.

Typically, this error - along with missing views and incorrect data in views - are a result of how Stitch handles altered table structures and views with dependencies in Redshift.

Structural Changes in Tables

A table’s structure can change for a few reasons:

• A new column has been added to the source table
• A new column has been added to the table as a result ofcolumn/data type splitting
• A new column has been added to the table as a result ofVARCHAR widening

When a table’s structure changes, dependent views must be temporarilydropped so Stitch can re-create the underlying table.

Because we don’t want to affect your work without your say-so, Stitch will not automatically drop views with dependencies.

Refer to theTable structural changes guide for more info and examples.

Workarounds

Depending on your workflow and needs, there are two ways you can approach this issue:

• Option 1: UseRedshift’s late binding views to “detach” the dependent view from the underlying table, thus preventing future dependency errors. Late binding views are views that don’t check underlying tables until the view is queried.
• Option 2: Manually locate and drop cascade the dependent views when dependency errors arise.

Solution Pros & Cons

Before you pick a solution, you should be aware of the pros and cons of each one:

Option 1: Late Binding Views

Option 2: Manual Drop & Cascade

Next Steps

Option 1: Re-Create Views as Late Binding Views

When a view is created, you can add theWITH NO SCHEMA BINDING clause to the query to indicate that the view should not be bound to the underlying database objects. This will eliminate the dependency between the view and the object(s) it references:

CREATEVIEWsales_orders_viewASSELECT*FROMstitch_mysql.ordersWITHNOSCHEMABINDING;

Note: You can’t update, insert into, or delete from a view. This means that if you want to add or remove columns, you need to re-create the view.

If you chose this option to resolve an error after a column was split and renamed, remember to include all the subsequent split columns when you re-create the view. For example: ifsales_order ‘split’ intosales_order__int andsales_order__st, you’d want to include both columns to ensure all values are captured in the view.

Option 2: Manually Locate & Temporarily Drop Dependent Views

Step 1: Create a View of Tables and Dependencies

First, you’ll create a view calledview_dependencies that lists the tables and view dependencies in your data warehouse. You will only need to perform this step once.

Using a SQL or command line tool, login to your Redshift database as an administrator and execute the following command. Our view will be created in the root of the database, but you can create it in a specific schema if you prefer:

CREATEVIEWview_dependenciesASSELECTDISTINCTsource_class.oidASsource_table_id,source_namespace.nspNameAS source_table_schema,source_class.relNameAS source_table_name,dependent_class.oidAS dependent_view_id,dependent_namespace.nspNameAS dependent_view_schema,dependent_class.relNameAS dependent_view_nameFROMpg_class source_classJOINpg_dependsource_dependONsource_class.relFileNode=source_depend.refObjIdJOINpg_depend dependent_dependONsource_depend.objId=dependent_depend.objIdJOINpg_classdependent_classONdependent_depend.refObjId=dependent_class.relFileNodeLEFTJOINpg_namespace source_namespaceONsource_class.relNameSpace=source_namespace.oidLEFTJOINpg_namespace dependent_namespaceONdependent_class.relNameSpace=dependent_namespace.oidWHEREdependent_depend.depType='i'::"char"ANDdependent_class.relKind='v'::"char"

The above command only selectsdependencies with a type ofi, or those that can only be dropped by runningDROP...CASCADE on the dependent object itself. Additionally, onlydependent relations that are views (relKind = 'v') are included in the results.

Step 2: Query the View to Locate Dependencies

Next, you’ll query theview_dependencies view you created in Step 1 to locate the objects you need to drop. If the notification referenced thecloseio.closeio_leads table, the query would look like this:

SELECTsource_table_schema,source_table_name,dependent_view_schema,dependent_view_nameFROMview_dependenciesWHEREsource_table_schema='closeio'ANDsource_table_name='closeio_leads'

And in the results:

Which indicates that thelead_addresses view in thedbt schema is the dependent object that’s causing issues.

Step 3: Drop the Dependent View

Now that you’ve found the dependent view, you can run a command to drop it. Remember to save the view’s definition somewhere before continuing if you want to re-create it later.

To ensure all dependent views are dropped, use theCASCADE option and replace the schema and view names as needed:

DROPVIEWdbt.lead_addressesCASCADE;

After Stitch has completed its replication cycle, you can re-create your views. If you opted not to initiallyre-create your views as late binding views, this may be a good time to do so.

Note: The amount of time required to perform table alterations depends on the size of the table in question. While dropping dependent views for an hour or two is typically sufficient to complete the process, some very large tables may require more time.

Contact Support

If new data still hasn’t entered your warehouse after converting to late binding views or dropping dependent views overnight, reach out to support.

Did this article help? If you have questions or feedback, feel free tosubmit a pull request with your suggestions,open an issue on GitHub, orreach out to us.