I have a function where in
In a cursor loop I
1. create a temp table (on commit drop)
2. insert data into it
3. Run Analyze on the table
Select/update outside the loop.
This has been running fine for a while on multiple setups, large and small
volumes. The setups all have the same hardware configuration.
On one particular setup with about 200k records and this analyze runs for 45min
and then times out(statement timeout is set to 45 min). typically this takes a
few seconds at best. But when I move the analyze outside the loop everything
runs fine.
An section of the code for reference.
CREATE TEMP TABLE tmp_hierarchy_sorted ( sort_id serial, aqu_document_id
integer,parent_id integer, ancestor_id integer, object_hierarchy character
varying(255), object_hierarchy_array text[], levels integer) ON COMMIT DROP
TABLESPACE tblspc_tmp ;
CREATE UNIQUE INDEX tmp_hierarchy_sorted_aqu_document_id_idx ON
tmp_hierarchy_sorted USING btree( aqu_document_id ) TABLESPACE tblspc_index;';
execute vSQL;
--get min doc number for that collection based on existing promoted
collections in the matter
select coalesce(max(doc_number_max),0) into iMin_Doc_number
FROM doc_Collection c
WHERE exists (SELECT 1 FROM doc_collection c1 WHERE c1.id =
iCollectionId and c1.matter_id = c.matter_id and c1.doc_number_prefix =
c.doc_number_prefix)
AND status = 'PROMOTED';
--go ancestor by ancestor for ones that are not loose files
open curAncestor for
select distinct id FROM aqu_document_hierarchy h where collection_Id =
iCollectionId and ancestor_id =-1 and parent_id = -1
AND EXISTS (select 1 from aqu_document_hierarchy h1 where
h1.ancestor_id = h.id ) order by id ;
LOOP
FETCH curAncestor into iAncestor_id;
EXIT WHEN NOT FOUND;
--insert each ancestor into the table as this is not part in the bulk
insert
vSQL := 'INSERT INTO tmp_hierarchy_sorted( aqu_document_id, parent_id
, ancestor_id , object_hierarchy, object_hierarchy_array,levels)
(select id, -1, -1, object_hierarchy,
regexp_split_to_array(object_hierarchy, ''/'') ,0
from aqu_document_hierarchy where collection_Id =' || iCollectionId ||
' AND id = ' || iAncestor_id || ')';
execute vSQL;
-- insert filtered documents for that ancestor
vSQL := 'INSERT INTO tmp_hierarchy_sorted (aqu_document_id, parent_id
, ancestor_id , object_hierarchy, object_hierarchy_array, levels)
(
SELECT id, parent_id, ancestor_id, object_hierarchy,
regexp_split_to_array(object_hierarchy, ''/'') as object_hierarchy_array,
array_length(regexp_split_to_array(object_hierarchy, ''/'') ,1) as levels
FROM aqu_document_hierarchy h WHERE EXISTS (SELECT 1 FROM
aqu_document_error_details e where e.aqu_document_id = h.id and e.exit_status
in (2,3,4,5) ) AND ancestor_id = ' || iAncestor_id ||
' ORDER BY regexp_split_to_array(object_hierarchy, ''/'')
);';
execute vSQL;
ANALYZE tmp_hierarchy_sorted;
END LOOP;
Thanks for the help
-mridula
The information contained in this email message and its attachments is intended
only for the private and confidential use of the recipient(s) named above,
unless the sender expressly agrees otherwise. Transmission of email over the
Internet is not a secure communications medium. If you are requesting or have
requested the transmittal of personal data, as defined in applicable privacy
laws by means of email or in an attachment to email, you must select a more
secure alternate means of transmittal that supports your obligations to protect
such personal data. If the reader of this message is not the intended recipient
and/or you have received this email in error, you must take no action based on
the information in this email and you are hereby notified that any
dissemination, misuse or copying or disclosure of this communication is
strictly prohibited. If you have received this communication in error, please
notify us immediately by email and delete the original message.
