Posted On: May 23, 2023

Amazon Redshift announces support for enhanced transaction controls inside stored procedures which enables you to automatically commit the statements inside the procedure. This new NONATOMIC mode can be used for those applications that would like to handle exceptions inside a stored procedure more smoothly. In addition, the NONATOMIC mode helps prevent disruptions in applications due to an error inside a stored procedure.

Amazon Redshift customers use stored procedures to encapsulate logic for data transformation, data validation, and business-specific logic. These stored procedures often contain a series of statements that are executed in a single transaction, unless otherwise committed by an explicit commit. Now you can use NONATOMIC mode where the statements inside the stored procedures are automatically committed. Additionally you can use the new PL/pgSQL statement RAISE to programmatically raise the exception, when needed, to help prevent disruptions to the application flow.

The enhanced transaction controls in stored procedures is available in all commercial and AWS GovCloud (US) Regions where Amazon Redshift is available. To learn more, read the Amazon Redshift database developer guide.