In a redshift transaction block every statement after truncate is auto committing even if auto commit is disabled

[prakashsoma-acc]

We are encountering an issue with psycopg2 when running transaction blocks that include a TRUNCATE statement. Our expectation, based on Redshift’s behaviour with auto-commit disabled, is that when a TRUNCATE statement is encountered within a transaction block, all statements up to the TRUNCATE should be committed, and a new transaction should begin for the subsequent statements until the COMMIT; statement. However, we are unable to achieve this expected behaviour using psycopg2.

Consider the following example:

BEGIN;
--stmt1
CREATE TEMPORARY TABLE ps_trunc_test_008 (
    col1 VARCHAR(20)
);
--stmt2
INSERT INTO ps_trunc_test_008 VALUES ('1234567890');
--stmt3
TRUNCATE TABLE ps_trunc_test_008;
--stmt4
INSERT INTO ps_trunc_test_008 VALUES ('1234567890');
--stmt5
INSERT INTO ps_trunc_test_008 VALUES ('1234567890');
--stmt6
INSERT INTO ps_trunc_test_008 VALUES ('1234567890');
COMMIT;

In this example, the first three statements (stmt1, stmt2, and stmt3) share the same transaction ID (xid) of 123456788. However, the remaining statements (stmt4, stmt5, and stmt6) have their own xids. The expected behaviour is for all statements after the TRUNCATE to have the same xid.

If we add a BEGIN; statement after the TRUNCATE, the behaviour is as expected. However, we are looking for a solution to achieve the desired behaviour without needing to add a BEGIN; statement after each TRUNCATE.

Could you provide guidance on how to achieve this behaviour with psycopg2?

import psycopg2
conn = psycopg2.connect(
    host=host,
    database=database,
    port=port,
    user=user,
    password=password
)
conn.autocommit = False
cursor = conn.cursor()
cursor.execute("BEGIN;")
cursor.execute("CREATE TEMPORARY TABLE psy_trunc_test_008 (col1 VARCHAR(20));")
cursor.execute("INSERT INTO psy_trunc_test_008 VALUES ('1234567890');")
cursor.execute("TRUNCATE TABLE psy_trunc_test_008;")
cursor.execute("INSERT INTO psy_trunc_test_008 VALUES ('1234567890');")
cursor.execute("INSERT INTO psy_trunc_test_008 VALUES ('1234567890');")
cursor.execute("INSERT INTO psy_trunc_test_008 VALUES ('1234567890');")
cursor.execute("COMMIT;")

https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html#r_TRUNCATE_usage_notes

https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-transaction-management.html#:~:text=The%20following%20example%20issues%20a%20TRUNCATE%20statement%20after%20inserting%20into%20test_table_a.%20The%20TRUNCATE%20statement%20issues%20an%20implicit%20commit%20that%20commits%20the%20current%20transaction%20(3335)%20and%20starts%20a%20new%20one%20(3336).%20The%20new%20transaction%20is%20committed%20when%20the%20procedure%20exits.

[dvarrazzo]

This has nothing to do with pyscopg2. Psycopg2 is only used to send commands to the database; it's up to the database to behave the way it does.

Truncate, in PostgreSQL has a specific behaviour that is not transactional. You can find documentation for that. Redshift is not a supported database and it is not free software, therefore PostgreSQL behaviour cannot necessarily be extended to it. As you are a Redshift customer I invite you to get in touch with Amazon representatives to investigate why the behaviour is not what you expect.

I cannot give consultancy for free on a commercial product. For a professional consultancy please feel free to reach out privately.