Timeline for Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 7, 2021 at 20:38 | vote | accept | gavenkoa | ||
| Dec 6, 2021 at 23:50 | answer | added | Erwin Brandstetter | timeline score: 1 | |
| Dec 6, 2021 at 13:00 | comment | added | J.D. |
Yes with a single INSERT it's all or nothing, and you'd have to start from the beginning again if it fails in the middle, with multiple INSERTs you could have failures between them, after some of the data was committed. Just curious how much data is being inserted?...perhaps the reason for the slowness can be performance tuned, which may be more favorable of a solution to you?
|
|
| Dec 6, 2021 at 12:57 | comment | added | gavenkoa |
So in this particular case I don't need "atomicity" of the operation but only correctness and durability (to have correct intermediate data committed). As you wrote it conflicts with ACID of a single SQL query and I have to resort to plpqsql.
|
|
| Dec 6, 2021 at 12:55 | comment | added | gavenkoa |
I wrote single INSERT ... SELECT query that might fail after two hours. In that case I'll "fix" data integrity and want to continue process without waiting another 2 hours. Seems I have to rewrite SQL query into plpgsql and commit bulks of inserts...
|
|
| Dec 6, 2021 at 12:36 | comment | added | J.D. |
For a single INSERT command, even if it's a bulk insert of many records, if an error occurs during, then all of the changes from that command are rolled back and nothing is committed. Therefore there is nothing to resume from, and you'd want to start the same INSERT over again from the beginning. This is part of the ACID principles of a relational database system. So it's unclear what you're asking if you're seeing otherwise?...are you saying your intention is to do multiple INSERTs and you want to prevent issues if that process is interrupted between INSERTs?
|
|
| Dec 6, 2021 at 12:26 | history | asked | gavenkoa | CC BY-SA 4.0 |