0

Is it possible to move data to the new table sourcing other tables without losing progress in the middle?

I think of plpgsql that read data by chunks and commit after every N inserts... If the process stops in the middle the condition NOT EXISTS (...) allows to add missing data by repeating process again.

I think it is important pattern and there might be support for it directly with the syntax:

INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)

The reason for copying interruption can be a constraint or user pause an operation. Still we want not to lose progress and resume process (with a help of NOT EXISTS (...) guard).

gavenkoa
  • 509
  • 2
  • 8
  • 22
  • 2
    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? – J.D. Dec 06 '21 at 12:36
  • 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...

     – gavenkoa Dec 06 '21 at 12:55
  • 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. – gavenkoa Dec 06 '21 at 12:57
  • 1
    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? – J.D. Dec 06 '21 at 13:00

1 Answers1

1

The reason for copying interruption can be a constraint or user pause an operation.

If it's about the user "pausing" the operation, a PROCEDURE or a DO statement might be the right choice (Postgres 11+). INSERT N rows at a time and COMMIT after each iteration. See:

Picking up after an interruption may be cheaper if the source is stable and you can keep track of an increasing minimum ID or some such.

But if it's just about a constraint that might raise an exception when violated, use an UPSERT query to resolve conflicts: INSERT .. ON CONFLICT .... The conflict action depends on undisclosed details.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600