Redshift中如何修改存在依赖关系表的排序键与分布键？

Short Answer #

You can modify the SORTKEY and DISTKEY of a table with dependencies—your issue is just that the final DROP TABLE step is blocked by dependent objects, not that the modification itself is impossible.

What's Happening With Your Current Workflow #

Your table-swap approach is a standard pattern for Redshift (since you can't alter a table's DISTKEY directly), but it fails at the last step because:

1. You renamed the original dummy table to old_dummy
2. This old_dummy table still has dependent objects (like views, foreign keys, functions, or materialized views) referencing it
3. Redshift blocks dropping tables with active dependencies to avoid breaking dependent objects

Solutions to Fix This #

1. Identify and Resolve Dependencies First

First, find exactly which objects depend on old_dummy using this query:

SELECT * FROM pg_depend WHERE refobjid = 'old_dummy'::regclass;

Then handle dependencies based on their type:

• Views: Update the view to reference the new dummy table, or drop the view temporarily if it's non-critical
• Foreign keys: Drop the foreign key constraint from the dependent table first
• Functions/Stored Procedures: Modify the code to point to the new dummy table

Once all dependencies are cleared, you can safely run DROP TABLE old_dummy;

2. Use CASCADE to Force Drop (Use With Caution)

If you're absolutely sure all dependent objects are disposable or can be recreated later, modify your drop command to:

DROP TABLE old_dummy CASCADE;

Warning: This will automatically delete all objects that depend on old_dummy (like views, dependent foreign keys, etc.). Only use this if you've verified none of these objects are critical to your system.

3. Alter SORTKEY Directly (If You Only Need to Change SORTKEY)

Redshift lets you modify the SORTKEY directly with ALTER TABLE (no need to create a new table) in most cases:

ALTER TABLE dummy ALTER SORTKEY (account_id, created_at);

Note that this only works for SORTKEY—you can't alter a table's DISTKEY directly; you still need the table-swap method for changing DISTKEY.

内容的提问来源于stack exchange，提问作者untitled