Production crashed after adding a view !

Problem

There was an existing CronJob (data refresh + dump) :

• drop a table
• create table
• dump new data in table (Insert)

The code was already there in a deployed application.

The issue: recently I had to create a view which depended on this table.

Hence I now got an error:

some additional context: I am using PostgreSQL

ERROR : <file name> : [functionname] ERROR dumping into table <table name>
[time stamp] ERROR : <file name> : cannot drop table <table name> because other objects depend on it
DETAIL:  view <view name> depends on table <table name>
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Traceback (most recent call last):
<stack trace>

---

Possible solutions

I had a few routes I could explore to resolve this, keep in mind, race conditions were to be avoided in this refactor + critical issue affecting data refresh.

Solutions possible:

• DROP TABLE ... CASCADE - Drops view, breaks other pods
• DROP TABLE without CASCADE - Original failing approach (DependentObjectsStillExist)
• DELETE FROM - Slower, preserves structure
• TRUNCATE TABLE - Current implementation (fast, transaction-based)
• Temporary table + atomic swap - View sees old data until swap

---

Solution implemented

Transaction based Truncate + Insert

Why?

Preserves view dependencies

• No CASCADE needed
• view stays intact
• Other pods unaffected (service deployed in k8s, multiple pods)

Performance

• Much faster than DELETE (deallocates pages vs logging each row)
• Handles 100k+ rows efficiently

Transaction safety

• TRUNCATE + INSERT in single transaction
• View never sees empty data (MVCC isolation)
• Atomic operation (all-or-nothing)

Simplicity

• No temp tables needed
• No complex swap logic
• Minimal code changes
• Easy to maintain

Best balance

• Solves the original problem (no DependentObjectsStillExist error)
• Fast enough for data refresh
• Safe for concurrent access
• Simple implementation

def dump_data_to_table(data: pd.DataFrame, table_name: str):
    """
    Updated approach: TRUNCATE + INSERT in transaction
    Preserves view dependencies and ensures data consistency
    """
    conn = psycopg2.connect(...)
    cur = conn.cursor()

    # TRUNCATE preserves table structure & views (no DROP needed)
    cur.execute(f"TRUNCATE TABLE {table_name}")

    # Insert new data
    cols = ', '.join([f'"{col}"' for col in data.columns])
    placeholders = ', '.join(['%s'] * len(data.columns))
    insert_sql = f"INSERT INTO {table_name} ({cols}) VALUES ({placeholders})"

    values = [tuple(row) for row in data.values]
    execute_batch(cur, insert_sql, values, page_size=5000)

    # Single transaction commit (atomic operation)
    conn.commit()

---

Conclusion

Always implement transaction based, atomic operations from get go. Consider possible future edge cases such as dependent views/objects. Handle data dump accordingly.