Describe the bug

Issue Description:

• Unable to delete a column of table from redshift database using CDK update. 


Expected Behavior

Newly created column in redshift has to be deleted when updating cdk code, but it's not happened with the same.

Current Behavior

Following are the error message:

Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: column "groupingkeywithitexxxxxxxx" of relation "orders_lv2_bd_producxxxxx” already exists

Logs: /aws/lambda/SmartPOS-Redshift-Tables--QueryRedshiftDatabase3de-xxxxxx

at waitForStatementComplete (/var/task/redshift-data.js:34:15)
at processTicksAndRejections (internal/process/task_queues.js:97:5)
at async Object.executeStatement (/var/task/redshift-data.js:18:5)
at async Promise.all (index 0)
at async updateTable (/var/task/table.js:111:5)
at async handler (/var/task/table.js:21:27) (RequestId: 513c5214-6e68-xxxx-xxxx-e1b641820183)

Reproduction Steps

• Using CDK redshift cluster and it's tabled deployed with few columns.

• Updated redshift table by adding new column into the existing table, new column is added fine after deploying cdk.

• when removing same column from cdk code and deployed cdk. it's failed with following error message.


Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: column "groupingkeywithitexxxxxxxx" of relation "orders_lv2_bd_producxxxxx” already exists

Logs: /aws/lambda/SmartPOS-Redshift-Tables--QueryRedshiftDatabase3de-xxxxxx

at waitForStatementComplete (/var/task/redshift-data.js:34:15)
at processTicksAndRejections (internal/process/task_queues.js:97:5)
at async Object.executeStatement (/var/task/redshift-data.js:18:5)
at async Promise.all (index 0)
at async updateTable (/var/task/table.js:111:5)
at async handler (/var/task/table.js:21:27) (RequestId: 513c5214-6e68-xxxx-xxxx-e1b641820183)

• Further checking in QueryRedshiftDatabase lambda funciton could not find the dropping of column statement when updating a table ( Verified in the table.js) in lambda function.
• Sample code used :

import * as cdk from 'aws-cdk-lib';
import { Construct } from 'constructs';
import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as redshift from '@aws-cdk/aws-redshift-alpha';

import * as core from 'aws-cdk-lib/core';

// import * as sqs from 'aws-cdk-lib/aws-sqs';

export class HelloCdkStack extends cdk.Stack {
constructor(scope: Construct, id: string, props?: cdk.StackProps) {
super(scope, id, props);

const vpc = new ec2.Vpc(this, 'Vpc');

const databaseName = 'testdatabase';
const cluster = new redshift.Cluster(this, databaseName, {
  clusterName: 'testcluster',
  
  defaultDatabaseName: databaseName,
  masterUser: {
    masterUsername: ‘xxxxxx’,
  },
  publiclyAccessible: false,
  removalPolicy: cdk.RemovalPolicy.DESTROY,
  vpc: vpc,
});

  new redshift.Table(this, 'TestTable', {
  removalPolicy: cdk.RemovalPolicy.DESTROY,
  cluster,
  databaseName,
  tableName: 'public.test',
  tableColumns: [
    {
      name: 'column_1',
      dataType: 'TEXT',
    }
    ,
    {
      name: 'groupingkeywithitexxxxxxxx',
      dataType:'varchar(max)',
    }
    
    /* , 
    {
      name: 'column3',
      dataType:'varchar(max)',
    }
    */

 // ,
  //  {
  //    name: 'column_3',
   //   dataType:'varchar(max)',
  //  }

  ],
});
// example resource
// const queue = new sqs.Queue(this, 'HelloCdkQueue', {
//   visibilityTimeout: cdk.Duration.seconds(300)   
// });

}
}

========================================


• Dropping a column worked manually running in the redshift query editor, but same not working with CDK and it's update is getting failed with error.

  ALTER TABLE test DROP COLUMN ;

Possible Solution

Cdk code should support / update to get this behaviour.

Additional Information/Context

No response

CDK CLI Version

2.42.1 (build 48a95f1)

Framework Version

No response

Node.js Version

v16.17.0

OS

Amazon Linux release 2 (Karoo)

Language

Typescript

Language Version

No response

Other information

No response