The Amazon Redshift console and the SQL client response indicate that a COPY command was completed successfully, but no data is copied to the Amazon Redshift table. No errors are logged in the STL_LOAD_ERRORS, STL_LOADERROR_DETAIL, or STL_ERROR tables.

This happens when the COPY command excecutes successfully but is subsequently rolled back because of an uncommitted SQL transaction. To ensure transactional integrity, the COPY command is executed in the context of a SQL transaction. When a SQL transaction is not committed, any commands run in the context of the transaction are rolled back. This behavior is by design, and as a result, no errors are apparent in the Amazon Redshift console or error log tables.

Follow these steps to evaluate and correct this issue:

  1. Using the query ID of the COPY command, execute the following query against the STL_COMMIT_STATS table:
         SELECT q.query, cs.xid, cs.endtime
         FROM stl_query q, stl_commit_stats cs
         WHERE q.query = QUERY ID
         AND q.xid = cs.xid;
    If this query returns no results, the COPY command transaction was not committed, initiating a rollback. This typically occurs because several SQL clients disable transaction autocommit by default and will not commit transactions unless explicitly declared.
  2. Review the documentation for the SQL client to determine the default transaction behavior.
  3. If the SQL client’s default behavior is to disable autocommit, you can resolve the issue in either of these ways:
    • Enable autocommit for the SQL client – This is not always an appropriate solution, but it immediately addresses the issue without requiring modification of existing SQL commands. In some cases, however, such as when performing multiple ad hoc data loads, this may be the most practical solution.
    • Explicitly commit the transaction after the COPY command with the SQL statements COMMIT or END. If the command is executed programmatically, the command syntax should be similar to the Python connection class conn.commit() statement as described in the Psycopg 2.6 documentation.

AWS, Amazon Redshift, COPY command, SQL, transaction, autocommit, no records

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.