Re: Pl/Pgsql Functions running simultaneously - Mailing list pgsql-performance

Thanks for your reply.  My comments are below.

On Friday 04 June 2004 16:39, you wrote:
> Uh... I don't think this is necessarily the wrong list, sometimes people
> don't have much to chime in. You could try reposting to -sql or -general
> I suppose.

I'll try one of those.

>
> As for my take on your questions, I wasn't exactly clear on what the
> problem is. If its just that things seem slow, make sure you have done
> the appropriate vacuum/analyze/reindex tech and then try adding some
> debug info to the function to determine where in the function it is
> slowing down.

Yeah, I do a fair amount of vacuum/analyze , but I am unclear as to when I
should run REINDEX.  Is their a way to tell that indexes have become corrupt,
or need to be reindexed?

>
> queries inside plpgsql functions will take locks as needed, but they are
> no different than regular statements, just keep in mind that the queries
> inside the function will work like an implicit transaction.

I've noticed.  Thanks for the info.

>
> Robert Treat
>
> On Thu, 2004-06-03 at 17:38, Marcus Whitney wrote:
> > Am I on the wrong list to ask this question, or does this list usually
> > have low activity?  Just asking because I am new and I need to know where
> > to ask this question.  Thanks.
> >
> > On Wednesday 02 June 2004 16:08, Marcus Whitney wrote:
> > > Hello all,
> > >
> > >   I have an import function that I have been working on for some time
> > > now, and it performed well up until recently.  It is doing a lot, and
> > > because the queries are not cached, I am not sure if that is what the
> > > problem is. If a function takes a while, does it lock any of the tables
> > > it is accessing, even for SELECT?
> > >
> > > Below is the bulk of the function:
> > >
> > > -- set sql statement variables
> > >         create_import_file_sql := ''COPY '' || container_table || ''
> > > ('' || filtered_container_columns  || '') TO '' ||
> > > quote_literal(formatted_import_file) || '' WITH NULL AS '' ||
> > > null_single_quotes;
> > >         upload_to_import_table_sql := ''COPY '' || import_table || ''
> > > (''
> > >
> > > || field_names || '') FROM '' || quote_literal(formatted_import_file)
> > > || || ''
> > >
> > > WITH NULL AS '' ||  null_single_quotes;
> > >         clean_personalization_fields_sql := ''UPDATE '' || import_table
> > > || '' SET emma_member_email = btrim(emma_member_email, '' ||
> > > quote_literal(quoted_single_quote) || '') , emma_member_name_first =
> > > btrim(emma_member_name_first, '' || quote_literal(quoted_single_quote)
> > > || '') ,   emma_member_name_last = btrim(emma_member_name_last, '' ||
> > > quote_literal(quoted_single_quote) || '') ;'';
> > >         clean_personalization_fields_sql2 := ''UPDATE '' ||
> > > import_table || '' SET emma_member_email = btrim(emma_member_email) ,
> > > emma_member_name_first = btrim(emma_member_name_first) ,
> > > emma_member_name_last =
> > > btrim(emma_member_name_last) ;'';
> > >         set_account_id_sql := ''UPDATE '' || import_table || '' SET
> > > emma_account_id = '' || account_id;
> > >         set_default_active_status_sql := ''UPDATE '' || import_table ||
> > > '' SET emma_member_status_id = 1'';
> > >         set_errors_for_null_email_sql := ''UPDATE '' || import_table ||
> > > '' SET emma_member_status_id = 2 WHERE emma_member_email IS NULL'';
> > > record_null_email_count_sql := ''UPDATE '' || import_history_table
> > >
> > > || '' SET emma_import_null_email_count = (SELECT COUNT(*) FROM '' ||
> > >
> > > import_table || '' WHERE emma_member_email IS NULL) WHERE
> > > emma_import_history_id ='' || import_history_id;
> > >         set_errors_for_invalid_email_sql := ''UPDATE '' || import_table
> > > || '' SET emma_member_status_id = 2  WHERE emma_member_email !~* '' ||
> > > email_regex; record_invalid_email_count_sql := ''UPDATE '' ||
> > > import_history_table
> > >
> > > || ''  SET emma_import_invalid_email_count = ( SELECT COUNT(*) FROM ''
> > > || ||
> > >
> > > import_table || ''  WHERE emma_member_email !~* '' || email_regex || ''
> > > ) WHERE emma_import_history_id ='' || import_history_id;
> > >         get_dupes_in_import_sql := ''SELECT emma_member_email,
> > > emma_member_status_id FROM '' || import_table || '' GROUP BY
> > > emma_member_email, emma_member_status_id having count(*) > 1'';
> > >         insert_dupes_sql := ''INSERT  INTO '' || dupe_table || ''
> > > SELECT * FROM '' || import_table || '' WHERE LOWER(emma_member_email) =
> > > LOWER('' || member_table || ''.emma_member_email)'';
> > >         record_table_dupe_count_sql := ''UPDATE '' ||
> > > import_history_table
> > >
> > > || '' SET emma_import_table_dupe_email_count = (SELECT COUNT(*) FROM ''
> > > || ||
> > >
> > > import_table || '' WHERE emma_member_email = LOWER('' || member_table
> > > || ''.emma_member_email)) WHERE emma_import_history_id ='' ||
> > > import_history_id; remove_dupes_from_import_table_sql := ''DELETE FROM
> > > ''
> > >
> > > || import_table
> > > ||
> > > || '' WHERE LOWER(emma_member_email) = LOWER('' || member_table ||
> > >
> > > ''.emma_member_email)'';
> > >         create_clean_import_file_sql := ''COPY '' || import_table || ''
> > > TO ''
> > >
> > > || quote_literal(clean_import_file) || '' WITH NULL AS '' ||
> > >
> > > null_single_quotes;
> > >         create_members_groups_ids_file_sql := ''COPY '' || import_table
> > > || '' (emma_member_id) TO '' || quote_literal(members_groups_ids_file)
> > > || '' WITH NULL AS '' ||  null_single_quotes;
> > >         empty_import_table_sql := ''TRUNCATE '' || import_table;
> > >         upload_clean_import_sql := ''COPY '' || member_table || '' FROM
> > > ''
> > >
> > > || quote_literal(clean_import_file) || '' WITH NULL AS '' ||
> > >
> > > null_single_quotes;
> > >         upload_members_groups_ids_sql := ''COPY '' ||
> > > members_groups_ids_table
> > >
> > > || '' (emma_member_id) FROM '' ||
> > > || quote_literal(members_groups_ids_file) ||
> > >
> > > '' WITH NULL AS '' ||  null_single_quotes;
> > >         empty_members_groups_ids_sql := ''TRUNCATE '' ||
> > > members_groups_ids_table;
> > >         empty_members_dupes_sql := ''TRUNCATE '' || dupe_table;
> > >         vacuum_sql := ''VACUUM '' || member_table || ''; VACUUM '' ||
> > > import_table || ''; VACUUM '' || container_table || ''; VACUUM '' ||
> > > members_groups_table || ''; VACUUM '' || members_groups_ids_table ||
> > > ''; VACUUM '' || dupe_table;
> > >
> > >         -- BEGIN ACTIVITY
> > >         -- Create the filtered import file with the
> > >         EXECUTE create_import_file_sql;
> > >         -- Load data from the filtered file to the import table
> > >         EXECUTE upload_to_import_table_sql;
> > >         -- Set account id in import table
> > >         EXECUTE set_account_id_sql;
> > >         -- Set the status of all the records to 1
> > >         EXECUTE set_default_active_status_sql;
> > >         -- Clean personalization data
> > >         EXECUTE clean_personalization_fields_sql;
> > >         EXECUTE clean_personalization_fields_sql2;
> > >         -- Set the status to error for all NULL emails
> > >         EXECUTE set_errors_for_null_email_sql;
> > >         -- Record the count of null emails
> > >         EXECUTE record_null_email_count_sql;
> > >         -- Set the status to error for all invalid emails
> > >         EXECUTE set_errors_for_invalid_email_sql;
> > >         -- Record the count of invalid emails
> > >         EXECUTE record_invalid_email_count_sql;
> > >
> > >         -- Remove duplicates in import table (originally in file)
> > >         FOR duplicate_record IN EXECUTE get_dupes_in_import_sql LOOP
> > >                 IF duplicate_record.emma_member_email IS NOT NULL THEN
> > >                     FOR replacement_record IN EXECUTE '' SELECT * FROM
> > > ''
> > >
> > > || import_table || '' WHERE emma_member_email = '' ||
> > >
> > > quote_literal(duplicate_record.emma_member_email) || '' ORDER BY
> > > emma_member_id LIMIT 1'' LOOP
> > >                             escape_first_name := quote_literal
> > > (replacement_record.emma_member_name_first);
> > >                             escape_last_name := quote_literal
> > > (replacement_record.emma_member_name_last);
> > >                             escape_email := quote_literal
> > > (replacement_record.emma_member_email);
> > >                             escape_status_id :=
> > > quote_literal(replacement_record.emma_member_status_id);
> > >                             -- Record count of dupes
> > >                             FOR dupe_record_count IN EXECUTE ''SELECT
> > > COUNT(*) AS count FROM '' || import_table || '' WHERE
> > > LOWER(emma_member_email) = LOWER('' || escape_email || '')'' LOOP
> > >                                     EXECUTE ''UPDATE '' ||
> > > import_history_table || ''  SET emma_import_file_dupe_email_count =''
> > > || dupe_record_count.count;
> > >                             END LOOP;
> > >                             FOR primary_dupe_record IN EXECUTE ''SELECT
> > > MAX(emma_member_id) AS max_id FROM '' || import_table || '' WHERE
> > > LOWER(emma_member_email) = LOWER('' || escape_email || '')'' LOOP
> > >                                     EXECUTE ''UPDATE '' || import_table
> > > || '' SET emma_member_status_id = 5 WHERE emma_member_id = '' ||
> > > primary_dupe_record.max_id;
> > >                                     EXECUTE ''DELETE FROM '' ||
> > > import_table
> > >
> > > || '' WHERE emma_member_email = '' ||
> > >
> > > quote_literal(duplicate_record.emma_member_email) || '' AND
> > > emma_member_status_id != 5'';
> > >                                     EXECUTE ''UPDATE '' || import_table
> > > || '' SET emma_member_status_id = 1 WHERE emma_member_status_id = 5'';
> > > END LOOP;
> > >                             import_dupe_count := import_dupe_count + 1;
> > >                     END LOOP;
> > >                 END IF;
> > >         END LOOP;
> > >
> > >         -- Move dupes over to the dupe table
> > >         EXECUTE insert_dupes_sql;
> > >         -- Record the count of dupes from import to members
> > >         EXECUTE record_table_dupe_count_sql;
> > >         -- Delete the dupes from the import table
> > >         EXECUTE remove_dupes_from_import_table_sql;
> > >         -- Create clean import file
> > >         EXECUTE create_clean_import_file_sql;
> > >         -- Create groups_id file
> > >         EXECUTE create_members_groups_ids_file_sql;
> > >         -- Empty import table
> > >         EXECUTE empty_import_table_sql;
> > >         -- Upload clean members from import
> > >         EXECUTE upload_clean_import_sql;
> > >         -- Upload group ids
> > >         EXECUTE upload_members_groups_ids_sql;
> > >
> > >         -- Associate to groups
> > >         groups := string_to_array(group_list, '','');
> > >         if array_lower(groups, 1) IS NOT NULL THEN
> > >             FOR i IN array_lower(groups, 1)..array_upper(groups, 1)
> > > LOOP EXECUTE ''INSERT INTO '' || members_groups_ids_table
> > >
> > > || '' SELECT '' || member_table || ''.emma_member_id FROM ONLY '' ||
> > >
> > > member_table || '' WHERE LOWER('' || member_table ||
> > > ''.emma_member_email) = LOWER('' || dupe_table || ''.emma_member_email)
> > > AND '' || member_table || ''.emma_member_id NOT IN (SELECT '' ||
> > > members_groups_table || ''.emma_member_id FROM '' ||
> > > members_groups_table || '' WHERE '' || members_groups_table ||
> > > ''.emma_group_id = '' || groups[i] || '') AND '' || member_table ||
> > > ''.emma_member_id NOT IN (SELECT emma_member_id FROM '' ||
> > > members_groups_ids_table || '')'';
> > >                      EXECUTE ''DELETE FROM '' ||
> > > members_groups_ids_table
> > >
> > > || '' WHERE emma_member_id IN (SELECT emma_member_id FROM '' ||
> > >
> > > members_groups_table || '' WHERE emma_group_id = '' || groups[i] || ''
> > > )''; EXECUTE ''INSERT INTO '' || members_groups_table || '' SELECT
> > > DISTINCT '' || groups[i] || '' AS emma_group_id,  emma_member_id FROM
> > > '' || members_groups_ids_table;
> > >             END LOOP;
> > >         END IF;
> > >
> > > Any pointers on large plpgsql operations are appreciated.  Especially
> > > when more than one instance is runinng.  Thanks.
> >
> > --
> > marcus whitney
> >
> > chief architect : cold feet creative
> >
> > www.coldfeetcreative.com
> >
> > 800.595.4401
> >
> >
> >
> > cold feet presents emma
> >
> > email marketing for discriminating
> >
> > organizations everywhere
> >
> > visit www.myemma.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings

--
marcus whitney

chief architect : cold feet creative

www.coldfeetcreative.com

800.595.4401
 


cold feet presents emma

email marketing for discriminating

organizations everywhere

visit www.myemma.com