- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
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 :
- Ensure that the cdp.email_send_mv materialized view exists and is accessible. Confirm the table name and schema.
- Verify that the user executing the DELETE statement has the necessary permissions to perform the delete operation on the specified table.
- 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;
- Ensure that there are no open transactions that might be preventing access to the table. If there are, either commit or rollback those transactions.
- 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
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren