AWS CDK - Unable to delete a column of table from redshift database using CDK update)

[gurukarn]

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

[peterwoodworth]

I've been able to reproduce this, thanks for reporting @gurukarn

We'll probably need to update our logic in the updateTable function

aws-cdk/packages/@aws-cdk/aws-redshift/lib/private/database-query-provider/table.ts

Line 70 in 40ddfdd

async function updateTable(

[Rizxcviii]

I have made a PR for this change to be implemented. Just need to wait for a review. @comcalvi or @peterwoodworth would it be possible to review this at your earliest convenience?

[github-actions]

⚠️COMMENT VISIBILITY WARNING⚠️

Comments on closed issues are hard for our team to see.
If you need more assistance, please either tag a team member or open a new issue that references this one.
If you wish to keep having a conversation with other community members under this issue feel free to do so.