1

This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be migrated as the Big Bang approach.

In oracle database, I used to use the below template of code for migration -

create or replace PROCEDURE PRC_TEST
AS
DECLARE
    CURSOR CUR
    IS
    SELECT ID,NAME  FROM test; 
TYPE test_typ IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
test_tbl test_typ;  

BEGIN
OPEN CUR;
LOOP
FETCH cur BULK COLLECT INTO test_tbl LIMIT 1000;
DBMS_OUTPUT.PUT_LINE(test_tbl.COUNT);
     FORALL I IN 1..test_tbl.COUNT --SAVE EXCEPTIONS
                INSERT
                INTO test1(ID,NAME)
                VALUES
                (
                test_tbl(I).id,
                test_tbl(I).name
                );
                
       FORALL I IN 1..test_tbl.COUNT
                  UPDATE test1 
                  SET name     = name||test_tbl(I).NAME
                  WHERE id =test_tbl(I).id;
                  
                DBMS_OUTPUT.PUT_LINE('AFTER'|| test_tbl.COUNT);  
        EXIT WHEN CUR%NOTFOUND ;       
     END LOOP;
     commit;
 close cur;    
 EXCEPTION
      WHEN OTHERS THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
        dbms_output.put_line('error'||sqlerrm);
        END LOOP;
END;
End PRC_TEST;

Is there a plpgsql equivalent code available for the same? What approach to be used in Postgres while handling such migration activity ? please provide some best practices to be followed for better performance and handling/storing the error records in Postgres.

Thanks..!

4
  • insert .. on conflict do update no PL/pgSQL or (slow) loop required Jul 28 '20 at 9:11
  • Note that frequent commits typically make things slower in Oracle (and Postgres). Jul 28 '20 at 9:32
  • Yes @a_horse_with_no_name, but the inserts will have around 70 Million records, hence wanted to do inserts in batches in order to have some control over the process and to log the error records which can be dealt later
    – cpb
    Jul 28 '20 at 11:24
  • Well, doing everything in one transaction will be more efficient. And I don't see how splitting this up would help to find errors (you are not logging the invalid rows to begin with). But again: a simple insert into ... select from .. on conflict do update would be the (efficient) equivalent in Postgres. If your really want to slow down the process, use a LIMIT in the SELECT clause Jul 28 '20 at 11:27
0

I would convert this into a single INSERT statement using on conflict do deal with the "insert or update" part:

insert into test1(id, name)
select id, name 
from test
on conflict (id) do update
  set name = excluded.name;

This assumes that there is a unique index (or constraint) on test1.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.