I’m trying to upload data to a postgresql timeseries database.
My problem isn’t a usual coding / engine one, but a logical one.
I have many dataframes, which are varying lengths.
For this example, say I have three – df1 df2 and df3, with lenths 800k, 100k and 120k

I can upload df1.to_sql(‘table_name’, engine … ) without issue.

When I try with df2, I get: sqlalchemy.exc.PendingRollbackError: Can’t reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)

Then I try df3 and it works.

Now I have broken down df2 into many dataframes of equal length and realized that from 53000 onwards is the issue, and I cannot upload in large chuncks after 53000.

Now for the weird part.

Take a subdataframe of df2 composed only of rows 53000 to 55000.
I can upload them by chunks of 100 or 500 or 999, but not by chunks of 1000 or above.
Keeping in mind that for df1, I uploaded in chunks of 500k without issue, and that the data is the same!

Now I must be missing something, but I have no idea what.