topic Re: Distributaion key error while using green plum(postgresql as target) in Qlik Replicate

Distributaion key error while using green plum(postgresql as target)

Pranita123 — Tue, 10 Sep 2024 07:21:17 GMT

Hello Community,

we are using Microsoft SQL server as source and PostgreSQL as target(green plum). we have one table which has primary key customer_id no other key is define in that tables but while replicating this table we are getting below error

"Handling new table 'dbo'.'ORDERS_Test2' failed
execute create primary key failed, statement ALTER TABLE "dbo"."ORDERS_Test2" ADD PRIMARY KEY ( "customer_id" )
RetCode: SQL_ERROR SqlState: 23505 NativeError: 1 Message: ERROR: PRIMARY KEY must contain all columns in the distribution key of relation "ORDERS_Test2";
Error while executing the query
Failed (retcode -1) to execute statement: ALTER TABLE "dbo"."ORDERS_Test2" ADD PRIMARY KEY ( "customer_id" )"

Any help or guidance on addressing this issue would be greatly appreciated.

Thank you,

Pranita

Re: Distributaion key error while using green plum(postgresql as target)

SachinB — Tue, 10 Sep 2024 07:59:10 GMT

Hello @Pranita123 ,

Thanks for reaching out to Qlik community forum.

The primary key you're trying to create Customer_ID does not include all the columns in the distribution key for the ORDERTest2 table. The database enforces that a primary key must cover all columns that are part of the distribution key

The underlying distribution strategy of the table conflicts with the primary key constraint you’re trying to add. For instance, if the table was distributed based on multiple columns (say,Order_ID and Customer_ID), then the primary key must include both of these columns.

If possible, you can alter the distribution key of the table to include only the Customer_ID column, so it aligns with the primary key you want to set.

Else you create table manually and use "Truncate and load" options.

Regards,

Sachin B

Re: Distributaion key error while using green plum(postgresql as target)

Pranita123 — Tue, 10 Sep 2024 08:22:03 GMT

Hii @SachinB ,

Thanks for response, but at source end there is no distribution key or any dependency on other columns still getting this error,

I also try to replicate other table which has pk defined which works fine.

Thank you,

Pranita

Re: Distributaion key error while using green plum(postgresql as target)

SachinB — Tue, 10 Sep 2024 08:28:13 GMT

Hello @Pranita123 ,

The task is failing while altering statement at the target endpoint, I would request you to create a case and attach the required diagnostic package and we can work together on this.

Hope this helps for you!

Regards,

Sachin B

Re: Distributaion key error while using green plum(postgresql as target)

Pranita123 — Tue, 10 Sep 2024 09:35:55 GMT

Hi @SachinB ,

I’d like to point out that for the same table, I defined a different task in the Qlik Replicate console where I used the `Id` as the key. This approach resolved the issue and I received the correct data at the target end with proper CDC.

Is this approach correct for addressing the issue?

Thank You

Re: Distributaion key error while using green plum(postgresql as target)

SachinB — Tue, 10 Sep 2024 09:51:11 GMT

Hello @Pranita123 ,

You have defined the primary key for ID column, and which exactly matches with distribution key and hence this has passed this time. Yes, this looks fine for us.

Regards,

Sachin B

Re: Distributaion key error while using green plum(postgresql as target)

DesmondWOO — Tue, 10 Sep 2024 09:53:43 GMT

Hi @Pranita123 ,

Do you mean Pivotal Greenplum? Please be aware that Qlik Replicate has not supported Greenplum since the 2023 version.

https://community.qlik.com/t5/Release-Notes/Qlik-Replicate-Release-Notes-November-2023-Initial-Release-until/ta-p/2110765

The PS team may need to be involved in this issue. Please contact your account manager for further details.

Regards,
Desmond

Re: Distributaion key error while using green plum(postgresql as target)

Pranita123 — Tue, 10 Sep 2024 10:22:11 GMT

Hi @DesmondWOO Thanks for response,

We using PostgreSQL as target endpoint , all other task are working fine and we getting expected outcome from them.

Thank you

Pranita