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

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.

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.

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.

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

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL