Delete rows from materialized view

Question

We have a materialized view from a MSK topic with auto refresh on. We would like to delete rows that were ingested > 78 hours (see delete operation below). The DELETE SQL is syntactically correct but we got an error during execution. Any ideas?

```
Summary
ERROR: could not open relation with OID 0 [ErrorId: 1-6590d202-38dc16f561cce295656723f5]
Elapsed time: 0 seconds
Result set query:
/* RQEV2-6q8Y3rdcHw */
DELETE from cdp.email_send_mv 
WHERE datediff(hour, cdp.email_send_mv.kafka_timestamp, GETDATE()) > 78
```

Answer

Regarding the DELETE query error out "ERROR:  could not open relation with OID 0".  Redshift is based on PostgreSQL 8.0.2 . Though there are huge differences and enhancements.
https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html

The above error is coming from postgres layer which has pg_catalog tables having metadata of the table/view. Before it could go to redshift for processing , metadata lookup and open operation failed at postgres level itself.

The error you're encountering, "could not open relation with OID 0," suggests that the Redshift is having trouble accessing or opening the relation (table) specified in the DELETE statement. This issue can occur due various reasons, and below are potential steps to troubleshoot and resolve the problem:

Troubleshooting steps :
===================
1. Ensure that the cdp.email_send_mv materialized view exists and is accessible. Confirm the table name and schema.
2. Verify that the user executing the DELETE statement has the necessary permissions to perform the delete operation on the specified table.
3. Check if there are any active locks on the table that might be preventing the delete operation. You can use the following query to identify locks:

```
SELECT * FROM pg_locks WHERE relation = 'cdp.email_send_mv'::regclass;
```

4. Ensure that there are no open transactions that might be preventing access to the table. If there are, either commit or rollback those transactions.
5. Ensure that the materialized view is created correctly and doesn't have any unusual characteristics as error mentioning OID 0 is unusual and might be related to internal PostgreSQL handling of system tables.

**Modified Query**
```
DELETE FROM cdp.email_send_mv
WHERE EXTRACT(HOUR FROM (CURRENT_TIMESTAMP - cdp.email_send_mv.kafka_timestamp)) > 78;
```
This modification uses EXTRACT to calculate the difference in hours between the current timestamp and cdp.email_send_mv.kafka_timestamp.

After making these checks and modifications, attempt the DELETE operation again and monitor for any further errors. 
If the issue persists, additional investigation might be required. Thus I would request you to raise a case with Our AWS Premium support Redshift team for further investigation.

[+] https://docs.aws.amazon.com/redshift/latest/dg/r_EXTRACT_function.html
[+] https://repost.aws/knowledge-center/prevent-locks-blocking-queries-redshift

Delete rows from materialized view

Troubleshooting steps :

Relevant content