Re: Pl/Pgsql Functions running simultaneously

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