Saved process enhancements in Amazon Redshift

Amazon Redshift is a totally managed, petabyte-scale knowledge warehouse service within the cloud. With Amazon Redshift, you’ll be able to analyze all of your knowledge to derive holistic insights about what you are promoting and your prospects. It helps saved procedures the place ready SQL code is saved and the code could be reused again and again.

Saved procedures are generally used to encapsulate logic for knowledge transformation, knowledge validation, and business-specific logic. By combining a number of SQL steps right into a saved process, you’ll be able to create reusable code blocks that may run collectively as a single transaction or a number of particular person transactions. You too can schedule saved procedures to automate knowledge processing on Amazon Redshift. For extra data, consult with Bringing your saved procedures to Amazon Redshift.

Within the Redshift saved process default atomic transaction mode, a name to a Redshift saved process will create its personal transaction when the decision begins or is a part of the prevailing transaction if an express transaction is opened earlier than the saved process is named. All of the statements inside a process behave as if they’re in a single transaction block that ends when the saved process name finishes. A nested name to a different process is handled like every other SQL assertion and operates throughout the context of the identical transaction because the caller. Statements for TRUNCATE, COMMIT, and ROLLBACK and the exception dealing with block with arbitrary SQL statements shut the present transaction and begin a brand new transaction implicitly. This conduct may cause challenges in migration to Amazon Redshift from different programs like Teradata.

On this publish, we focus on the enhancements to Amazon Redshift saved procedures for non-atomic transaction mode. This mode gives enhanced transaction controls that allow you to robotically commit the statements contained in the saved process.

Non-atomic transaction mode

The brand new non-atomic transaction mode characteristic gives three enhancements on saved procedures in Amazon Redshift:

• Until the DML or DDL statements are a part of an express open transaction, every assertion within the saved process will run in its personal implicit transaction and a brand new transaction will likely be opened to deal with following statements. If an express transaction is opened, then all subsequent statements are run and stay un-committed till an express transaction management command (COMMIT or ROLLBACK) is run to finish the transaction.
• Amazon Redshift won’t re-raise the exception after the exception handler statements are full. Subsequently, a brand new RAISE assertion with none INFO or EXCEPTION has been offered to re-throw the exception caught by the exception dealing with block. This RAISE assertion with none INFO or EXCEPTION will solely be allowed within the exception dealing with block.
• Additionally, the brand new START TRANSACTION assertion begins an express transaction contained in the non-atomic transaction mode saved process. Use the prevailing transaction management command (COMMIT or ROLLBACK) to finish the explicitly began transaction.
  • Amazon Redshift doesn’t help sub-transactions so if there may be already an open transaction, then calling this assertion once more will do nothing, and no error is raised.
  • If an express transaction remains to be open when the nonatomic transaction mode saved process name ends, then the specific transaction stays open till a transaction management command is run within the session.
  • If the session disconnects earlier than operating a transaction management command, the entire transaction is robotically rolled again.

Further restrictions

Some restrictions have additionally been launched for Redshift saved procedures:

• For nesting saved process calls, all of the procedures have to be created in the identical transaction mode, irrespective of if it’s in atomic (default) transaction mode or the brand new non-atomic transaction mode
• You’ll be able to’t nest saved procedures throughout the 2 transaction modes (atomic and non-atomic)
• You’ll be able to’t set the SECURITY DEFINER possibility or SET configuration_parameter possibility for non-atomic transaction mode saved procedures

Impression to cursors

Cursors in non-atomic transaction mode saved procedures will behave in another way in comparison with the default atomic transaction mode:

• Cursor statements will want an express transaction block earlier than starting the cursor to make sure that every iteration of the cursor loop just isn’t auto-committed.
• To return a cursor from non-atomic transaction mode saved process, you’ll need an express transaction block earlier than starting the cursor. In any other case, the cursor will likely be closed when the SQL assertion contained in the loop is robotically dedicated.

Benefits

The next are key benefits of this characteristic from a consumer perspective:

• It gives the aptitude to elevate and shift Teradata saved procedures to run in Teradata session mode. This helps in seamless migrations from knowledge warehouses like Teradata and SQL Server.
• It allows Amazon Redshift to offer extra versatile operations within saved procedures when encountering errors and exceptions. Amazon Redshift can now protect earlier motion’s state earlier than reaching an exception.

Syntax

The brand new elective key phrase NONATOMIC has been added to the saved process definition syntax, as proven within the following code:

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name
( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
procedure_body
$$ LANGUAGE plpgsql

This elective key phrase creates the saved process below the non-atomic transaction mode. When you don’t specify the key phrase, then the default atomic mode would be the transaction mode when creating the saved process.

NONATOMIC means every DML and DDL assertion within the process will likely be implicitly dedicated.

With out non-atomic mode, the process will create its personal transaction when the decision begins or be a part of the prevailing transaction if an express transaction is opened earlier than it’s referred to as. Each assertion throughout the saved process will belong to this one transaction.

Instance of NONATOMIC mode

Let’s take into account the shopper contact desk custcontacts, which shops buyer major and secondary contact telephone numbers:

CREATE desk custcontacts(
custid int4 not null,
primaryphone char(10),
secondaryphone char(10));

We insert three pattern buyer data with no contact values:

INSERT INTO custcontacts VALUES (101, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (102, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (103, 'xxxxxxxxxx', 'xxxxxxxxxx');

It’s worthwhile to create a saved process to replace the first and secondary telephone numbers. The requirement is to not roll again updates to the first contact quantity if updates to the secondary contact quantity fail for some purpose.

You’ll be able to obtain this by creating the saved process with the NONATOMIC key phrase. The NONATOMIC key phrase ensures that every assertion within the saved process runs in its personal implicit transaction block. Subsequently, if the UPDATE assertion for the secondary telephone fails, then it gained’t roll again the information replace made to the first telephone. See the next code:

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
END;
$$
LANGUAGE plpgsql;

Now let’s name the saved process passing the secondary telephone quantity with greater than 10 digits, which can fail within the secondaryphone UPDATE assertion attributable to incorrect size:

name sp_update_custcontacts(101,'1234567890','345443345324');

The previous process name will replace the first telephone quantity efficiently. The secondary telephone quantity replace fails. Nevertheless, the primaryphone replace won’t roll again as a result of it ran in its personal implicit transaction block because of the NONATOMIC clause within the saved process definition.

choose * from custcontacts;

custcontacts | primaryphone | secondaryphone
-------------+---------------+---------------
101 | 1234567890 | XXXXXXXXXX
102 | XXXXXXXXXX | XXXXXXXXXX
103 | XXXXXXXXXX | XXXXXXXXXX

Exception dealing with in NONATOMIC mode

Exceptions are dealt with in saved procedures in another way based mostly on the atomic or non-atomic mode:

• Atomic (default) – Exceptions are at all times re-raised
• Non-atomic – Exceptions are dealt with and you’ll select to re-raise or not

Let’s proceed with the earlier instance for instance exception dealing with in non-atomic mode.

Create the next desk to log exceptions raised by saved procedures:

CREATE TABLE procedure_log
(log_timestamp timestamp, procedure_name varchar(100), error_message varchar(255));

Now replace the sp_update_custcontacts() process to deal with exceptions. Be aware that we’re including an EXCEPTION block within the process definition. It inserts a report within the procedure_log desk within the occasion of an exception.

CREATE OR REPLACE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
END;
$$
LANGUAGE plpgsql;

Now create yet one more saved process, which can name the previous process. It additionally has an EXCEPTION block and inserts a report within the procedure_log desk within the occasion of an exception.

CREATE PROCEDURE sp_update_customer() NONATOMIC AS
$$
BEGIN
-- Allow us to assume you've got further staments right here to replace different fields. For this instance, ommitted them for simplifiction.
-- Nested name to replace contacts
name sp_update_custcontacts(101,'1234567890','345443345324');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_customer', sqlerrm);
END;
$$
LANGUAGE plpgsql;

Let’s name the father or mother process we created:

name sp_update_customer();

This in flip will name the sp_update_custcontacts() process. The inside process sp_update_custcontacts() will fail as a result of we’re updating the secondary telephone with an invalid worth. The management will enter the EXCEPTION block of the sp_update_custcontacts() process and make an insert into the procedure_log desk.

Nevertheless, it won’t re-raise the exception in non-atomic mode. Subsequently, the father or mother process sp_update_customer() won’t get the exception handed from the sp_update_custcontacts() process. The management won’t enter the EXCEPTION block of the sp_update_customer() process.

When you question the procedure_log desk, you will note an entry just for the error dealt with by the sp_update_custcontacts() process:

choose * from procedure_log;

Now redefine the sp_update_custcontacts() process with the RAISE assertion:

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
RAISE;
END;
$$
LANGUAGE plpgsql;

Let’s name the father or mother saved process sp_update_customer() once more:

name sp_update_customer();

Now the inside process sp_update_custcontacts() will re-raise the exception to the father or mother process sp_update_customer() after dealing with the exception in its personal EXCEPTION block. Then the management will attain the EXCEPTION block within the father or mother process and insert one other report into the procedure_log desk.

When you question the procedure_log desk now, you will note two entries: one by the inside process sp_update_custcontacts() and one other by the father or mother process sp_update_customer(). This demonstrates that the RAISE assertion within the inside process re-raised the exception.

choose * from procedure_log;

Specific START TRANSACTION assertion in non-atomic mode

You’ll be able to concern a START TRANSACTION assertion to start a transaction block contained in the saved process. It should open a brand new transaction contained in the saved process. For examples, consult with Nonatomic mode saved process transaction administration.

Conclusion

On this publish, we mentioned the enhancements to Redshift saved procedures for non-atomic transaction mode, which gives enhanced transaction controls to allow you to robotically commit the statements contained in the saved process. This mode additionally allows simpler migration to Amazon Redshift from different programs like Teradata. Check out these enhancements and tell us your expertise in feedback.

In regards to the Authors

Milind Oke is a Information Warehouse Specialist Options Architect based mostly out of New York. He has been constructing knowledge warehouse options for over 15 years and makes a speciality of Amazon Redshift.

Satesh Sonti is a Sr. Analytics Specialist Options Architect based mostly out of Atlanta, specialised in constructing enterprise knowledge platforms, knowledge warehousing, and analytics options. He has over 17 years of expertise in constructing knowledge property and main advanced knowledge platform packages for banking and insurance coverage purchasers throughout the globe.

Kiran Chinta is a Software program Improvement Supervisor at Amazon Redshift. He leads a robust crew in question processing, SQL language, knowledge safety, and efficiency. Kiran is obsessed with delivering merchandise that seamlessly combine with prospects’ enterprise purposes with the precise ease of use and efficiency. In his spare time, he enjoys studying and taking part in tennis.

Huichen Liu is a software program growth engineer on the Amazon Redshift question processing crew. She focuses on question optimization, statistics and SQL language options. In her spare time, she enjoys mountaineering and pictures.