Summary
Exploring the feasibility of modifying the PostgreSQL destination connector in Airbyte to use TRUNCATE TABLE instead of DROP TABLE during a Full Refresh | Overwrite sync to preserve table structure and dependencies like materialized views. Includes code snippet for the proposed modification.
Question
Hi Airbyte Community,
I’m exploring a potential modification to the PostgreSQL destination connector to avoid table deletion during a Full Refresh | Overwrite sync. Instead of dropping and recreating the table, I’m considering using TRUNCATE TABLE to clear the table’s contents while preserving its structure and dependencies (e.g., materialized views).
Here’s the modified code for the overwriteRawTable method in PostgresSqlOperations.kt script:
override fun overwriteRawTable(database: JdbcDatabase, rawNamespace: String, rawName: String) {
val tmpName = rawName + AbstractStreamOperation.TMP_TABLE_SUFFIX
database.executeWithinTransaction(
listOf(
// Truncate the existing table instead of dropping it
“TRUNCATE TABLE $rawNamespace.$rawName”,
// Insert data from the temporary table into the truncated table
“INSERT INTO $rawNamespace.$rawName SELECT * FROM $rawNamespace.$tmpName”,
// Drop the temporary table
“DROP TABLE $rawNamespace.$tmpName”
)
)
}
My Questions:
- Is this approach feasible within Airbyte’s framework for the PostgreSQL destination connector?
- Are there any potential drawbacks or edge cases I should be aware of when replacing
DROP TABLEwithTRUNCATE TABLEin this context? - Has anyone implemented or tested a similar solution for preserving table dependencies (e.g., materialized views or indexes) during a full refresh?
Any insights, feedback, or advice would be greatly appreciated!
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.