Issue

The following error appears when trying to execute the VACUUM command in a transformation:

[Amazon](500310) Invalid operation: VACUUM cannot run inside a transaction block;

Environment

Transformations (running against Redshift Destinations)

Resolution

As the VACUUM command is not yet supported by Fivetran, you will need to set up a scheduled query in your Redshift warehouse:

1. Log in to Amazon Redshift.
2. Follow the steps provided here to create a scheduled SQL Script.

Cause

Because of how Fivetran's Transformation layer is designed, there is no ability to run the VACUUM command, even with any of the following scripts:

-- Script 1
COMMIT; VACUUM <table>; COMMIT;

-- Script 2
END TRANSACTION; VACUUM <table>;

-- Script 3
SET autocommit on;
VACUUM <table>;
SET autocommit off;