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

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