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

gurukarn · 2022-09-23T11:40:57Z

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

The text was updated successfully, but these errors were encountered:

peterwoodworth · 2022-09-28T00:06:12Z

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 · 2022-11-25T15:54:54Z

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?

) When attempting to a drop a column from the array, this would cause an error similar to that of the following ```Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: column "column" of relation "table” already exists``` fixes #22208 ---- ### All Submissions: * [x] Have you followed the guidelines in our [Contributing guide?](https://github.com/aws/aws-cdk/blob/main/CONTRIBUTING.md) ### Adding new Unconventional Dependencies: * [ ] This PR adds new unconventional dependencies following the process described [here](https://github.com/aws/aws-cdk/blob/main/CONTRIBUTING.md/#adding-new-unconventional-dependencies) ### New Features * [ ] Have you added the new feature to an [integration test](https://github.com/aws/aws-cdk/blob/main/INTEGRATION_TESTS.md)? * [ ] Did you use `yarn integ` to deploy the infrastructure and generate the snapshot (i.e. `yarn integ` without `--dry-run`)? *By submitting this pull request, I confirm that my contribution is made under the terms of the Apache-2.0 license*

github-actions · 2022-12-27T17:06:39Z

⚠️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.

…#23011) When attempting to a drop a column from the array, this would cause an error similar to that of the following ```Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: column "column" of relation "table” already exists``` fixes aws#22208 ---- ### All Submissions: * [x] Have you followed the guidelines in our [Contributing guide?](https://github.com/aws/aws-cdk/blob/main/CONTRIBUTING.md) ### Adding new Unconventional Dependencies: * [ ] This PR adds new unconventional dependencies following the process described [here](https://github.com/aws/aws-cdk/blob/main/CONTRIBUTING.md/#adding-new-unconventional-dependencies) ### New Features * [ ] Have you added the new feature to an [integration test](https://github.com/aws/aws-cdk/blob/main/INTEGRATION_TESTS.md)? * [ ] Did you use `yarn integ` to deploy the infrastructure and generate the snapshot (i.e. `yarn integ` without `--dry-run`)? *By submitting this pull request, I confirm that my contribution is made under the terms of the Apache-2.0 license*

gurukarn added bug This issue is a bug. needs-triage This issue or PR still needs to be triaged. labels Sep 23, 2022

github-actions bot added the @aws-cdk/aws-redshift Related to Amazon Redshift label Sep 23, 2022

github-actions bot assigned comcalvi Sep 23, 2022

gurukarn changed the title ~~(module name): (short issue description)~~ AWS CDK - Unable to delete a column of table from redshift database using CDK update) Sep 23, 2022

peterwoodworth added p2 effort/small Small work item – less than a day of effort and removed needs-triage This issue or PR still needs to be triaged. labels Sep 27, 2022

This was referenced Nov 18, 2022

fix(aws-redshift): Columns would not be deleted upon removal from the array #22974

Closed

fix(aws-redshift): Columns are not dropped on removal from array #23011

Merged

mergify bot closed this as completed in #23011 Dec 27, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

gurukarn commented Sep 23, 2022

peterwoodworth commented Sep 28, 2022

Rizxcviii commented Nov 25, 2022 •

edited

Loading

github-actions bot commented Dec 27, 2022

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

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

Comments

gurukarn commented Sep 23, 2022

Describe the bug

Expected Behavior

Current Behavior

Reproduction Steps

Possible Solution

Additional Information/Context

CDK CLI Version

Framework Version

Node.js Version

OS

Language

Language Version

Other information

peterwoodworth commented Sep 28, 2022

Rizxcviii commented Nov 25, 2022 • edited Loading

github-actions bot commented Dec 27, 2022

⚠️COMMENT VISIBILITY WARNING⚠️

Rizxcviii commented Nov 25, 2022 •

edited

Loading