0

I have a procedure like that;

CREATE OR REPLACE PROCEDURE insert_into_tableAv2(inout rows_affected INT) LANGUAGE 'plpgsql' AS $$ BEGIN
    INSERT INTO insert_into_tableAv2 (serial, aaa, ooo, UPDATED)
        SELECT id, aaa, ooo, UPDATED
        FROM tableA
        WHERE NOT EXISTS (
            SELECT 1
            FROM tableAv2
            WHERE tableA.id = tableAv2.serial AND tableA.aaa = tableAv2.aaa
        )
        LIMIT 100;

        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        RETURN;

END; $$;

TableA has 9000 records in my DEV environment. Basically, it's a pretty small table.

When I call it like that it makes RDS CPU %100, and cannot insert anything and timed-out as well.

DO $$
DECLARE
    result int := 0; -- Initialize with a starting value
BEGIN
    LOOP
        result := 0;
        -- Call the procedure and update the result
        CALL insert_into_tableAv2(result);

        -- Exit the loop if the result is 0
        EXIT WHEN result = 0;
    END LOOP;
END $$;

The reason I wrote it like a procedure is that I want to run similar queries in bigger tables and call it many times. So does anyone know why the procedure call is not working ?

4
  • Provide a brief description of the structure of tableA and tableAv2, especially focusing on any indexes or constraints that might affect performance. Commented Aug 15, 2024 at 11:48
  • @BusraEcemSakar when I run this query INSERT INTO insert_into_tableAv2 (serial, aaa, ooo, UPDATED) SELECT id, aaa, ooo, UPDATED FROM tableA WHERE NOT EXISTS ( SELECT 1 FROM tableAv2 WHERE tableA.id = tableAv2.serial AND tableA.aaa = tableAv2.aaa ) It just takes 1 seconds and successful. When I call it as a single procedure, it also works pretty well. CALL insert_into_tableAv2(result); Therefore, I believe that problem is not indexes or table structure instead it's about the loop. Commented Aug 15, 2024 at 11:52
  • Why the limit 100? Commented Aug 15, 2024 at 15:04
  • Whatever you may believe to be true, please disclose the (minimum!) table definition as CREATE TABLE script, indexes and dependencies, some sample values as INSERT script, and your Postgres version to begin with. What's the situation with possible concurrent writes? Avoid mixed-case identifiers to avoid confusion. And I assume you mean INSERT INTO tableAv2 where it says INSERT INTO insert_into_tableAv2? Commented Aug 15, 2024 at 23:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.