sbailliez opened a new issue #14888: URL: https://github.com/apache/airflow/issues/14888
**Apache Airflow version**: 2.0.0 **Environment**: - **Cloud provider or hardware configuration**: AWS - **OS** (e.g. from /etc/os-release): Amazon Linux 2 **What happened**: The TRUNCATE operation has a fine print in Redshift that it is committing the transaction. See https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html ``` However, be aware that TRUNCATE commits the transaction in which it is run. ``` and ``` The TRUNCATE command commits the transaction in which it is run; therefore, you can't roll back a TRUNCATE operation, and a TRUNCATE command may commit other operations when it commits itself. ``` Currently with truncate=True, the operator would generate a statement like: ``` BEGIN; TRUNCATE TABLE schema.table; -- this commits the transaction --- the table is now empty for any readers until the end of the copy COPY .... COMMIT; ``` **What you expected to happen**: Replacing with a DELETE operation would solve the problem, in a normal database it is not considered a fast operation but with Redshift, a 1B+ rows table is deleted in less than 5 seconds on a 2-node ra3.xlplus. (not counting vacuum or analyze) and a vacuum of the empty table taking less than 3 minutes. ``` BEGIN; DELETE FROM schema.table; COPY .... COMMIT; ``` It should be mentioned in the documentation that a delete is done for the reason above and that vacuum and analyze operations are left to manage. **How often does this problem occur? Once? Every time etc?** Always. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected]
