How to Fix Cannot Alter Column Used by A View Errors

How to Fix “Cannot Alter Column Used by a View” Errors

There are a handful of tables where CopyStorm uses database views rather than tables. A good example is ActivityHistory (a view combining the Task and Event tables). If Salesforce makes a change which alters the type of length of a column in a table and the column is used in an existing view, PostgreSQL will throw an error like the following when CopyStorm tries to make the change in the database.

• org.postgresql.util.PSQLException: ERROR: cannot alter type of a column used by a view or rule Detail: rule _RETURN on view openactivity depends on column “status”

The process to fix the issue is to:

• Drop the views that depend on the table that needs modification

• Run CopyStorm — it will make the table modification and automatically recreate the views.

Example From Summer’20

In Summer’20 Salesforce increased then length of the column Task.Status. Since the Task.Status column is used in CopyStorm views the update to Task.Status failed with:

• org.postgresql.util.PSQLException: ERROR: cannot alter type of a column used by a view or rule Detail: rule _RETURN on view openactivity depends on column “status”

Here are the steps to fix this problem.

Step 1: Drop the Views Which Depend on Task.Status

Step 2: Run CopyStorm

The next time CopyStorm runs it will:

• Successfully ALTER the Task.Status column because the dependent views no longer exist.

• Recreate the views after detecting they have been deleted.