DDL statements perform an automatic COMMIT, ROLLBACK will not work as expected
Description
In Snowflake, DDL statements perform an automatic commit after their execution, making permanent all the changes in the current transaction, meaning they can not be discarded by a ROLLBACK.
When a ROLLBACK statement is found in a procedure that also contains a DDL statement, SnowConvert will generate this FDM to inform about the DDL autocommit behavior.
Code Example
Input Code:
IN -> Redshift_01.sql
CREATE OR REPLACE PROCEDURE rollback_ddl(a int)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO transaction_values_test values (a);
CREATE TABLE someRollbackTable
(
col1 INTEGER
);
INSERT INTO someRollbackTable values (a);
ROLLBACK;
END
$$;
CALL rollback_ddl(10);
Output Code:
OUT -> Redshift_01.sql
CREATE OR REPLACE PROCEDURE rollback_ddl (a int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "03/03/2025", "domain": "test" }}'
AS $$
BEGIN
BEGIN TRANSACTION;
INSERT INTO transaction_values_test
values (:a);
CREATE TABLE someRollbackTable
(
col1 INTEGER
);
BEGIN TRANSACTION;
INSERT INTO someRollbackTable
values (:a);
--** SSC-FDM-RS0007 - DDL STATEMENTS PERFORM AN AUTOMATIC COMMIT, ROLLBACK WILL NOT WORK AS EXPECTED **
ROLLBACK;
END
$$;
CALL rollback_ddl(10);
