[Python] Potential data loss during `Insert into select from` Postgres query

I have a staging table in PostgreSQL and use a simple INSERT INTO ... SELECT ... query to copy data from the staging table to the final table. However, I occasionally encounter data loss issues after the operation is completed. I'm not sure why this is happening. staging table i have checked its correct (with correct data)

Here are some notable details:

• The staging table is a simple table without any indexes or keys.
• The final table has keys and indexes.
• The issue doesn't occur consistently. Sometimes the data transfer works fine, but other times there is data loss.

code i am using

import pg8000

...
bigSql = f"INSERT INTO final_tbl({sqlCols}) SELECT {sqlCols} FROM staging_tbl"
print(' ->Inserting data...')
limit = 4000000
offset = 0
while True:
cursor.execute(bigSql+f" LIMIT {limit} OFFSET {offset}")
print('Inserted ', cursor.rowcount)
if cursor.rowcount==0:
break
offset += limit
connection.commit()


regarding server information, i am using azure vm with laravel forge panel provided postgressql.

Continue reading...