Re: Reg: BULK COLLECT

It seems you are fetching from a table then sequentially inserting each record to another table.

In PostgreSQL, you could use cursors in PL/pgSQL ("http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html").

Alternatively you may write a single query which selects from the table and performs an insert into the other table, it is usually more efficient than sequential single record inserts.

If the amount of data are significant you may use ORDER BY and LIMIT in your SELECT query in order to limit the count of records you are fetching for insertion.

You may place this SQL query in a loop within a bash script as shown below, this bash script and the pseudo query updates a few records of a given table at a time, you may convert this example into an INSERT query for piecemeal inserts keeping the transaction size manageable.


#!/bin/sh
#date;time sh scripts/sample_piecemeal_db_updation.sh 10000 2>&1|tee /tmp/sample_piecemeal_db_updation.sh.sh.log;date;
export PSQL_PATH="/apps/postgreSQL/pgsql-9.1.4/bin/psql";
export DB_NAME="my_db";
export PGPORT="5432";
export DB_USER="some_user";
export DB_HOST="some_ip"; #put the actual ip here.
step=$1;

echo `date +%Y-%m-%d.%H%Mhrs:%S.%N`'-DB update commences';
trim() { echo $1; }

        _sql_query2="
            SELECT count(a.*)AS cnt FROM main_table a;
        ";
        echo $_sql_query2;
        counter=0;
        for cnt in `$PSQL_PATH -d$DB_NAME -U$DB_USER --port=$PGPORT --host=$DB_HOST --quiet --tuples-only -c"$_sql_query2"`
        do
            echo '$cnt is:'$cnt;
            counter=$(( $counter + 1 ));
            for i in $(seq 0 $step $cnt)
            do
                _sql_query1="
                    SET work_mem='1GB';
                    UPDATE main_table b
                    SET some_field=a.some_field
                    FROM
                    (
                    SELECT
                    a.id
                    ,b.some_field2
                    FROM
                    (
                    SELECT
                    a.*
                    FROM
                    main_table a
                    ORDER BY a.id ASC
                    OFFSET $i
                    LIMIT $step
                    )a
                    JOIN
                    some_other_table b
                    ON
                    b.some_other_field=a.some_other_field
                    )a
                    WHERE
                    b.id=a.id
                    ;
                ";
                #echo "_sql_query1 is:$_sql_query1";
                results=`$PSQL_PATH -d$DB_NAME -U$DB_USER --port=$PGPORT --host=$DB_HOST --quiet --tuples-only -c"$_sql_query1"`
                echo "cnt is:$cnt, step is:$step, i is:$i, results:$results";
            done;
        done;
echo `date +%Y-%m-%d.%H%Mhrs:%S.%N`'-DB updation concludes.';