Thread: Reg: BULK COLLECT
I am porting my application from Oracle to PostgreSQL.
We are using BULK COLLECT functionality of oracle.
How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
A small example is as below (This is just an example and the query is much more complex which returns huge amount of data)
CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$
DECLARE
l_data b%ROWTYPE;
POPULATE_STATS CURSOR IS
(
SELECT * from a
)
; // query returning a huge amount of data
BEGIN
OPEN POPULATE_STATS;
LOOP
FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;
IF POPULATE_STATS%ROWCOUNT > 0
THEN
FORALL i IN 1..l_data.COUNT
INSERT INTO b VALUES l_data(i);
END IF;
IF NOT FOUND THEN EXIT; END IF;
END LOOP;
CLOSE POPULATE_STATS;
EXCEPTION
WHEN OTHERS THEN
CODE := SQLSTATE;
MSG := SQLERRM;
INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);
RAISE NOTICE 'SQLERRM';
RAISE NOTICE '%', SQLSTATE;
RAISE NOTICE '%', MSG;
END;
$body$
LANGUAGE PLPGSQL;
How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
Thanks & Regards
Medhavi Mahansaria
Mailto: medhavi.mahansaria@tcs.com
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
On 05/25/2015 07:24 AM, Medhavi Mahansaria wrote: > Hello, > > I am porting my application from Oracle to PostgreSQL. > > We are using BULK COLLECT functionality of oracle. > How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql? > > A small example is as below (This is just an example and the query is much more complex which returns huge amount of data) > > > */CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$/* > > */DECLARE/* > */l_data b%ROWTYPE;/* > > */POPULATE_STATS CURSOR IS/* > */(/* > */SELECT * from a/* > */)/* > */; // query returning a huge amount of data/* > > */BEGIN/* > */ OPEN POPULATE_STATS;/* > */ LOOP/* > */ FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;/* > */ IF POPULATE_STATS%ROWCOUNT > 0/* > */ THEN/* > */ FORALL i IN 1..l_data.COUNT/* > */ INSERT INTO b VALUES l_data(i);/* > */ END IF;/* > */ IF NOT FOUND THEN EXIT; END IF; /* > */ END LOOP;/* > */ CLOSE POPULATE_STATS;/* > */EXCEPTION/* > */ WHEN OTHERS THEN/* > */ CODE := SQLSTATE;/* > */ MSG := SQLERRM;/* > */ INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);/* > */ RAISE NOTICE 'SQLERRM';/* > */ RAISE NOTICE '%', SQLSTATE;/* > */ RAISE NOTICE '%', MSG;/* > */END; > /* > */$body$/* > */LANGUAGE PLPGSQL;/* > > How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql? > > > Thanks & Regards > Medhavi Mahansaria > Mailto: medhavi.mahansaria@tcs.com > That seems pretty over complicated version of: insert into b select * from a; Which is all you'll need in PG. It it does something else, then I failed to understand the stored proc. -Andy
#!/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.';
Allan.
Hello,
I am porting my application from Oracle to PostgreSQL.
We are using BULK COLLECT functionality of oracle.
How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
A small example is as below (This is just an example and the query is much more complex which returns huge amount of data)
CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$
DECLARE
l_data b%ROWTYPE;
POPULATE_STATS CURSOR IS
(
SELECT * from a
)
; // query returning a huge amount of data
BEGIN
OPEN POPULATE_STATS;
LOOP
FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;
IF POPULATE_STATS%ROWCOUNT > 0
THEN
FORALL i IN 1..l_data.COUNT
INSERT INTO b VALUES l_data(i);
END IF;
IF NOT FOUND THEN EXIT; END IF;
END LOOP;
CLOSE POPULATE_STATS;
EXCEPTION
WHEN OTHERS THEN
CODE := SQLSTATE;
MSG := SQLERRM;
INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);
RAISE NOTICE 'SQLERRM';
RAISE NOTICE '%', SQLSTATE;
RAISE NOTICE '%', MSG;
END;
$body$
LANGUAGE PLPGSQL;
How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
Thanks & Regards
Medhavi Mahansaria
Mailto: medhavi.mahansaria@tcs.com=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
On 05/25/2015 05:24 AM, Medhavi Mahansaria wrote: > Hello, > > I am porting my application from Oracle to PostgreSQL. > > We are using BULK COLLECT functionality of oracle. > How can i change the 'BULK COLLECT' fetch of the data from the cursor to > make if compatible for pg/plsql? See here: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING > > A small example is as below (This is just an example and the query is > much more complex which returns huge amount of data) > > > */CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$/* > > */DECLARE/* > */l_data b%ROWTYPE;/* > > */POPULATE_STATS CURSOR IS/* > */(/* > */SELECT * from a/* > */)/* > */; // query returning a huge amount of data/* > > */BEGIN/* > */ OPEN POPULATE_STATS;/* > */ LOOP/* > */ FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;/* > */ IF POPULATE_STATS%ROWCOUNT > 0/* > */ THEN/* > */ FORALL i IN 1..l_data.COUNT/* > */ INSERT INTO b VALUES l_data(i);/* > */ END IF;/* > */ IF NOT FOUND THEN EXIT; END IF; /* > */ END LOOP;/* > */ CLOSE POPULATE_STATS;/* > */EXCEPTION/* > */ WHEN OTHERS THEN/* > */ CODE := SQLSTATE;/* > */ MSG := SQLERRM;/* > */ INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);/* > */ RAISE NOTICE 'SQLERRM';/* > */ RAISE NOTICE '%', SQLSTATE;/* > */ RAISE NOTICE '%', MSG;/* > */END; > /* > */$body$/* > */LANGUAGE PLPGSQL;/* > > How can i change the 'BULK COLLECT' fetch of the data from the cursor to > make if compatible for pg/plsql? > > > Thanks & Regards > Medhavi Mahansaria > Mailto: medhavi.mahansaria@tcs.com > -- Adrian Klaver adrian.klaver@aklaver.com
We are using bulk collect to enhance the performance as the data is huge.
But as you said it is ideally insert into b select * from a;
So now I am using the looping through query result option as Adrian suggested.
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Thank You Adrian.
Thanks & Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________
From: Andy Colson <andy@squeakycode.net>
To: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>, pgsql-general@postgresql.org
Date: 05/25/2015 06:15 PM
Subject: Re: [GENERAL] Reg: BULK COLLECT
On 05/25/2015 07:24 AM, Medhavi Mahansaria wrote:
> Hello,
>
> I am porting my application from Oracle to PostgreSQL.
>
> We are using BULK COLLECT functionality of oracle.
> How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
>
> A small example is as below (This is just an example and the query is much more complex which returns huge amount of data)
>
>
> */CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$/*
>
> */DECLARE/*
> */l_data b%ROWTYPE;/*
>
> */POPULATE_STATS CURSOR IS/*
> */(/*
> */SELECT * from a/*
> */)/*
> */; // query returning a huge amount of data/*
>
> */BEGIN/*
> */ OPEN POPULATE_STATS;/*
> */ LOOP/*
> */ FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;/*
> */ IF POPULATE_STATS%ROWCOUNT > 0/*
> */ THEN/*
> */ FORALL i IN 1..l_data.COUNT/*
> */ INSERT INTO b VALUES l_data(i);/*
> */ END IF;/*
> */ IF NOT FOUND THEN EXIT; END IF; /*
> */ END LOOP;/*
> */ CLOSE POPULATE_STATS;/*
> */EXCEPTION/*
> */ WHEN OTHERS THEN/*
> */ CODE := SQLSTATE;/*
> */ MSG := SQLERRM;/*
> */ INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);/*
> */ RAISE NOTICE 'SQLERRM';/*
> */ RAISE NOTICE '%', SQLSTATE;/*
> */ RAISE NOTICE '%', MSG;/*
> */END;
> /*
> */$body$/*
> */LANGUAGE PLPGSQL;/*
>
> How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql?
>
>
> Thanks & Regards
> Medhavi Mahansaria
> Mailto: medhavi.mahansaria@tcs.com
>
That seems pretty over complicated version of:
insert into b select * from a;
Which is all you'll need in PG. It it does something else, then I failed to understand the stored proc.
-Andy
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
> > > On 05/25/2015 07:24 AM, Medhavi Mahansaria wrote: > > Hello, > > > > I am porting my application from Oracle to PostgreSQL. > > > > We are using BULK COLLECT functionality of oracle. > > How can i change the 'BULK COLLECT' fetch of the data from the cursor > to make if compatible for pg/plsql? > > > > A small example is as below (This is just an example and the query is > much more complex which returns huge amount of data) > > > > > > */CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$/* > > > > */DECLARE/* > > */l_data b%ROWTYPE;/* > > > > */POPULATE_STATS CURSOR IS/* > > */(/* > > */SELECT * from a/* > > */)/* > > */; // query returning a huge amount of data/* > > > > */BEGIN/* > > */ OPEN POPULATE_STATS;/* > > */ LOOP/* > > */ FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;/* > > */ IF POPULATE_STATS%ROWCOUNT > 0/* > > */ THEN/* > > */ FORALL i IN 1..l_data.COUNT/* > > */ INSERT INTO b VALUES l_data(i);/* > > */ END IF;/* > > */ IF NOT FOUND THEN EXIT; END IF; /* > > */ END LOOP;/* > > */ CLOSE POPULATE_STATS;/* > > */EXCEPTION/* > > */ WHEN OTHERS THEN/* > > */ CODE := SQLSTATE;/* > > */ MSG := SQLERRM;/* > > */ INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);/* > > */ RAISE NOTICE 'SQLERRM';/* > > */ RAISE NOTICE '%', SQLSTATE;/* > > */ RAISE NOTICE '%', MSG;/* > > */END; > > /* > > */$body$/* > > */LANGUAGE PLPGSQL;/* > > > > How can i change the 'BULK COLLECT' fetch of the data from the cursor > to make if compatible for pg/plsql? > > > > > > Thanks & Regards > > Medhavi Mahansaria > > Mailto: medhavi.mahansaria@tcs.com > > > > That seems pretty over complicated version of: > > insert into b select * from a; > > Which is all you'll need in PG. It it does something else, then I > failed to understand the stored proc. > > -Andy > > On 5/27/2015 12:52 AM, Medhavi Mahansaria wrote:> Dear Andy, > > We are using bulk collect to enhance the performance as the data is huge. > > But as you said it is ideally insert into b select * from a; > > So now I am using the looping through query result option as Adrian > suggested. > > http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING > > > Thank You Adrian. > > > Thanks & Regards > Medhavi Mahansaria Did you time it? I'll bet "insert into b select * from a" is the fastest method. -Andy
We are using bulk collect to enhance the performance as the data is huge.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services