Bug/ch155055/emily mo inconsistent to carto error

Mmoncadaisla commented 3 years ago

Context

In this small PR from Support we aim to avoid hitting the Batch API 16 kb payload limit when using the _truncate_and_drop_add_columns function, which is called from to_carto (through copy_from) with if_exists='replace' collision strategy if the table structure changes used and the table has a considerable amount of columns.

Further context can be found here: https://app.clubhouse.io/cartoteam/story/155055/emily-mo-inconsistent-to-carto-error

Proposed solution

Create a temporary PostgreSQL user defined function through a standard SQL API call with the required changes
Apply this function in the transaction (after the TRUNCATE operation and before the cartodbfication process)
Drop the temporary function

Example output with the current logic

CartoException: ['Your payload is too large: 18745 bytes. Max size allowed is 16384 bytes (16kb). Are you trying to import data?. Please, check out import api http://docs.cartodb.com/cartodb-platform/import-api/']

Example output with the changes implemented

Success! Data uploaded to table "test_to_carto_truncate_drop_add_" correctly
'test_to_carto_truncate_drop_add_'

We have also verified that the temporary function is dropped as expected.

PR changes

Relevant

io/managers/context_manager.py where the logic referred above is implemented. Add helper functions for query definition, add functions to create and drop UDFs and perform necessary changes in the _truncate_and_drop_add_columns function

Minor

utils/utils.py add a function create_tmp_name to create temporary-like names using uuid
/data/services/service.py adapt the _new_temporary_table_name method to make use of the create_tmp_name function

shortcut-integration[bot] commented 3 years ago

This pull request has been linked to Clubhouse Story #155055: [emily-mo] Inconsistent to_carto error.

Mmoncadaisla commented 3 years ago

Performed different Q&A tests on both an enterprise and a free plan accounts and it seems to be working as expected. Thank you a lot for the great CR @Jesus89 @jgoizueta ❤️

CartoDB / cartoframes