(redshift): upgrading from older CDK version caused This table is already SORTKEY AUTO

[mrgrain]

Describe the bug

In older version of the AWS CDK (pre 2.0.0-alpha.8) setting the sort key was not supported and a created the table did not have an explicit sort key property. When #17135 introduced support for it, the Table Handler got changed to add an ALTER TABLE tableName ALTER SORTKEY AUTO statementwhen the sort key "changed" to AUTO. [1] Since previously the property wasundefined`, the property is detected as changed and the ALTER statement is attempted to be executed but will fail because Redshift has defaulted to create the table with SORTKEY AUTO.

[1] https://github.com/aws/aws-cdk/blob/main/packages/%40aws-cdk/aws-redshift-alpha/lib/private/database-query-provider/table.ts?t=7#L177-L194

Expected Behavior

Handler should not fail in this scenario.

Current Behavior

Handler fails, resource rolls back.

Reproduction Steps

n/a

Possible Solution

Probably one of these three:

• Improve the condition for when to add the ALTER TABLE tableName SORTKEY AUTO statement to not be added when sortKey prop was previously undefined
• Catch the error for the above ALTER statement and don't fail the handler if the error is because the table already is in the desired state
• Change the handler to check the actual state of the table to decide which ALTER statements need to be executed.

Additional Information/Context

n/a

CDK CLI Version

2.88.0

Framework Version

2.88.0

Node.js Version

18

OS

MacOs

Language

Typescript

Language Version

No response

Other information

No response