[destination-postgres] org.postgresql.util.PSQLException: ERROR: column "properties_hs_line_..." specified more than once

[zkid18]

Connector Name

hubspot

Connector Version

0.51.0

What step the error happened?

During the sync

Relevant information

After the update, my connector got broken with a bizarre error on connecting Hubspot to Postgres:
org.postgresql.util.PSQLException: ERROR: column "properties_hs_line_item_global_term_hs_recurring_billing_period" specified more than once

I believe the issue is related to Hubspot's handling of nested data, which creates additional columns in the temporary table.
More importantly, I don't have the abovementioned property in my setup.

At least 3 people in Slack has reported the same issue over the last 20 days [1] [2] [3]

[marinBlobr]

Hello we have exactly the same error on our side when we try to synchronize Hubspot in PostgreSQL.

Caution

Synchronization fails with the following error

Failure reason: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: column > "properties_hs_line_item_global_term_hs_recurring_billing_period" specified more than once

For our part, we have identified that the issue comes from the Deals stream.

We are currently using Airbyte version 0.50.56.

We have tested different versions of the Hubspot connector. But we get the same error every time.

• 4.0.0 (current)
• 3.3.0
• 3.0.0

[marcosmarxm]

I reported this tot he connector team take a look asap. Thanks for reporting the issue @zkid18

[marcosmarxm]

@zkid18 @marinBlobr what version of Postgres Destination are you using?

[marcosmarxm]

Some user reported downgrading the Destination version to 2.0.1 worked as a workaround. I'm waiting for the connector team to check the issue.

[marinBlobr]

@zkid18 @marinBlobr what version of Postgres Destination are you using?

For our part we use version 16.1.0 of PostgreSQL

[marcosmarxm]

@marinBlobr the connector version not the Postgres version. Can you share the complete log file to analyze?

[marcosmarxm]

I was able to reproduce the issue using Sandbox credentials from Hubspot to local Postgres 2.0.4

Logfile:
default_workspace_job_93_attempt_1_txt.txt

[marinBlobr]

@marcosmarxm I tried with 2.0.4 (latest) and 2.0.1 connector version

[evantahler]

In the stream, there are 2 columns that look sus:

• properties_hs_line_item_global_term_hs_recurring_billing_period
• properties_hs_line_item_global_term_hs_recurring_billing_period_enabled

I think is the case that column names in postgres have a max of 63 chars. The first 63 chars of properties_hs_line_item_global_term_hs_recurring_billing_period_enabled would happen to be properties_hs_line_item_global_term_hs_recurring_billing_period... leading to a duplicate.

[roman-yermilov-gl]

Made some investigation and confirm what @evantahler is saying. Getting this when run the query manually:

...
NOTICE:  identifier "properties_hs_line_item_global_term_hs_discount_percentage_enabled" will be truncated to "properties_hs_line_item_global_term_hs_discount_percentage_enab"
NOTICE:  identifier "properties_hs_line_item_global_term_hs_recurring_billing_start_date" will be truncated to "properties_hs_line_item_global_term_hs_recurring_billing_start_"
NOTICE:  identifier "properties_hs_line_item_global_term_recurringbillingfrequency_enabled" will be truncated to "properties_hs_line_item_global_term_recurringbillingfrequency_e"
NOTICE:  identifier "_properties_hs_line_item_global_term_hs_recurring_billing_period_enabled" will be truncated to "_properties_hs_line_item_global_term_hs_recurring_billing_perio"
NOTICE:  identifier "properties_hs_line_item_global_term_hs_recurring_billing_start_date_enabled" will be truncated to "properties_hs_line_item_global_term_hs_recurring_billing_start_"
ERROR:  column "properties_hs_line_item_global_term_hs_recurring_billing_start_" specified more than once
...

I think this problem should be solved for postgres destination.
@edgao could you please confirm?

[zkid18]

Hey @marcosmarxm Should I create a new issue here or edit the initial topic since the problem is not with Hubspot?

[marcosmarxm]

@zkid18 I moved this to become a discussion as it is a limitation of Postgres handle large field names.

[evantahler]

I think @edgao has some ideas on how to solve this, although it will involve changing the columns names in the destination

[edgao]

I didn't realize there were two issues! Same comment as #35268 (comment) - try out destination-postgres v2.0.8, it includes the changes from #36805 to improve this behavior.