X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 41B8FD1BB17 for ; Mon, 7 Jun 2004 13:48:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08742-02 for ; Mon, 7 Jun 2004 16:48:35 +0000 (GMT) Received: from ml-hw5.monsterlabs.com (ml-hw5.monsterlabs.com [216.183.105.167]) by svr1.postgresql.org (Postfix) with SMTP id D2EACD1B3B6 for ; Mon, 7 Jun 2004 13:48:33 -0300 (ADT) Received: (qmail 9894 invoked from network); 7 Jun 2004 16:48:33 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.15?) (64.3.242.80) by ml-hw5.monsterlabs.com with SMTP; 7 Jun 2004 16:48:33 -0000 From: Marcus Whitney Reply-To: marcus@coldfeetcreative.com Organization: Coldfeet Creative To: Robert Treat Subject: Re: Pl/Pgsql Functions running simultaneously Date: Mon, 7 Jun 2004 11:43:41 -0500 User-Agent: KMail/1.6.1 References: <200406021608.56919.marcus@coldfeetcreative.com> <200406031638.03409.marcus@coldfeetcreative.com> <1086385141.29236.1118.camel@camel> In-Reply-To: <1086385141.29236.1118.camel@camel> Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200406071143.41841.marcus@coldfeetcreative.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/64 X-Sequence-Number: 7122 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= =20 should run REINDEX. Is their a way to tell that indexes have become corrup= t,=20 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 whe= re > > 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 tabl= es > > > it is accessing, even for SELECT? > > > > > > Below is the bulk of the function: > > > > > > -- set sql statement variables > > > create_import_file_sql :=3D ''COPY '' || container_table || '' > > > ('' || filtered_container_columns || '') TO '' || > > > quote_literal(formatted_import_file) || '' WITH NULL AS '' || > > > null_single_quotes; > > > upload_to_import_table_sql :=3D ''COPY '' || import_table || = '' > > > ('' > > > > > > || field_names || '') FROM '' || quote_literal(formatted_import_file) > > > || || '' > > > > > > WITH NULL AS '' || null_single_quotes; > > > clean_personalization_fields_sql :=3D ''UPDATE '' || import_t= able > > > || '' SET emma_member_email =3D btrim(emma_member_email, '' || > > > quote_literal(quoted_single_quote) || '') , emma_member_name_first = =3D > > > btrim(emma_member_name_first, '' || quote_literal(quoted_single_quote) > > > || '') , emma_member_name_last =3D btrim(emma_member_name_last, '' = || > > > quote_literal(quoted_single_quote) || '') ;''; > > > clean_personalization_fields_sql2 :=3D ''UPDATE '' || > > > import_table || '' SET emma_member_email =3D btrim(emma_member_email)= , > > > emma_member_name_first =3D btrim(emma_member_name_first) , > > > emma_member_name_last =3D > > > btrim(emma_member_name_last) ;''; > > > set_account_id_sql :=3D ''UPDATE '' || import_table || '' SET > > > emma_account_id =3D '' || account_id; > > > set_default_active_status_sql :=3D ''UPDATE '' || import_tabl= e || > > > '' SET emma_member_status_id =3D 1''; > > > set_errors_for_null_email_sql :=3D ''UPDATE '' || import_tabl= e || > > > '' SET emma_member_status_id =3D 2 WHERE emma_member_email IS NULL''; > > > record_null_email_count_sql :=3D ''UPDATE '' || import_history_table > > > > > > || '' SET emma_import_null_email_count =3D (SELECT COUNT(*) FROM '' || > > > > > > import_table || '' WHERE emma_member_email IS NULL) WHERE > > > emma_import_history_id =3D'' || import_history_id; > > > set_errors_for_invalid_email_sql :=3D ''UPDATE '' || import_t= able > > > || '' SET emma_member_status_id =3D 2 WHERE emma_member_email !~* ''= || > > > email_regex; record_invalid_email_count_sql :=3D ''UPDATE '' || > > > import_history_table > > > > > > || '' SET emma_import_invalid_email_count =3D ( SELECT COUNT(*) FROM= '' > > > || || > > > > > > import_table || '' WHERE emma_member_email !~* '' || email_regex || = '' > > > ) WHERE emma_import_history_id =3D'' || import_history_id; > > > get_dupes_in_import_sql :=3D ''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 :=3D ''INSERT INTO '' || dupe_table || '' > > > SELECT * FROM '' || import_table || '' WHERE LOWER(emma_member_email)= =3D > > > LOWER('' || member_table || ''.emma_member_email)''; > > > record_table_dupe_count_sql :=3D ''UPDATE '' || > > > import_history_table > > > > > > || '' SET emma_import_table_dupe_email_count =3D (SELECT COUNT(*) FRO= M '' > > > || || > > > > > > import_table || '' WHERE emma_member_email =3D LOWER('' || member_tab= le > > > || ''.emma_member_email)) WHERE emma_import_history_id =3D'' || > > > import_history_id; remove_dupes_from_import_table_sql :=3D ''DELETE F= ROM > > > '' > > > > > > || import_table > > > || > > > || '' WHERE LOWER(emma_member_email) =3D LOWER('' || member_table || > > > > > > ''.emma_member_email)''; > > > create_clean_import_file_sql :=3D ''COPY '' || import_table |= | '' > > > TO '' > > > > > > || quote_literal(clean_import_file) || '' WITH NULL AS '' || > > > > > > null_single_quotes; > > > create_members_groups_ids_file_sql :=3D ''COPY '' || import_t= able > > > || '' (emma_member_id) TO '' || quote_literal(members_groups_ids_file) > > > || '' WITH NULL AS '' || null_single_quotes; > > > empty_import_table_sql :=3D ''TRUNCATE '' || import_table; > > > upload_clean_import_sql :=3D ''COPY '' || member_table || '' = FROM > > > '' > > > > > > || quote_literal(clean_import_file) || '' WITH NULL AS '' || > > > > > > null_single_quotes; > > > upload_members_groups_ids_sql :=3D ''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 :=3D ''TRUNCATE '' || > > > members_groups_ids_table; > > > empty_members_dupes_sql :=3D ''TRUNCATE '' || dupe_table; > > > vacuum_sql :=3D ''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 =3D '' || > > > > > > quote_literal(duplicate_record.emma_member_email) || '' ORDER BY > > > emma_member_id LIMIT 1'' LOOP > > > escape_first_name :=3D quote_literal > > > (replacement_record.emma_member_name_first); > > > escape_last_name :=3D quote_literal > > > (replacement_record.emma_member_name_last); > > > escape_email :=3D quote_literal > > > (replacement_record.emma_member_email); > > > escape_status_id :=3D > > > 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) =3D LOWER('' || escape_email || '')'' LOOP > > > EXECUTE ''UPDATE '' || > > > import_history_table || '' SET emma_import_file_dupe_email_count =3D= '' > > > || dupe_record_count.count; > > > END LOOP; > > > FOR primary_dupe_record IN EXECUTE ''SELE= CT > > > MAX(emma_member_id) AS max_id FROM '' || import_table || '' WHERE > > > LOWER(emma_member_email) =3D LOWER('' || escape_email || '')'' LOOP > > > EXECUTE ''UPDATE '' || import_tab= le > > > || '' SET emma_member_status_id =3D 5 WHERE emma_member_id =3D '' || > > > primary_dupe_record.max_id; > > > EXECUTE ''DELETE FROM '' || > > > import_table > > > > > > || '' WHERE emma_member_email =3D '' || > > > > > > quote_literal(duplicate_record.emma_member_email) || '' AND > > > emma_member_status_id !=3D 5''; > > > EXECUTE ''UPDATE '' || import_tab= le > > > || '' SET emma_member_status_id =3D 1 WHERE emma_member_status_id =3D= 5''; > > > END LOOP; > > > import_dupe_count :=3D 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 :=3D 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) =3D LOWER('' || dupe_table || ''.emma_member_em= ail) > > > 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 =3D '' || 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 =3D '' || 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 --=20 marcus whitney chief architect : cold feet creative www.coldfeetcreative.com 800.595.4401 =A0 cold feet presents emma email marketing for discriminating organizations everywhere visit www.myemma.com