Re: Disk filling, CPU filling, renegade inserts and deletes? - Mailing list pgsql-performance
From | Richard Plotkin |
---|---|
Subject | Re: Disk filling, CPU filling, renegade inserts and deletes? |
Date | |
Msg-id | 31935aa56e9539c5648cb09e78152c03@richardplotkin.com Whole thread Raw |
In response to | Re: Disk filling, CPU filling, renegade inserts and deletes? (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Disk filling, CPU filling, renegade inserts and deletes?
|
List | pgsql-performance |
Hi Tom, Thanks for your responses this morning. I did the select relname, and it returned 0 rows. I do have one function that creates a temp table and fills it within the same transaction. I'm pasting it below. Perhaps the "ON COMMIT DROP" is causing problems, and I need to drop the table at the end of the function instead of using ON COMMIT DROP? -- -- Name: crumbs(integer, text, boolean); Type: FUNCTION; Schema: public -- CREATE FUNCTION crumbs(integer, text, boolean) RETURNS text AS $_$DECLARE starting_page ALIAS FOR $1; current_page integer; delimiter text DEFAULT ': '; withLinkTags BOOLEAN DEFAULT FALSE; page_id_temp INTEGER; page_name_temp TEXT; current_nOrder INTEGER := 1; page_results record; path TEXT DEFAULT ''; BEGIN IF starting_page IS NULL THEN RETURN NULL; END IF; current_page := starting_page; IF $2 IS NOT NULL THEN delimiter := $2; END IF; IF $3 IS NOT NULL THEN withLinkTags := $3; END IF; --Create a table consisting of three columns: nOrder, page_id, name CREATE TEMPORARY TABLE results (nOrder integer, page_id integer, name text) ON COMMIT DROP; --Select the current page into the results table SELECT INTO page_id_temp, page_name_temp p.page_id, CASE WHEN p.title_abbr IS NOT NULL THEN p.title_abbr ELSE p.title END as name FROM page p WHERE p.page_id = starting_page; IF FOUND THEN EXECUTE 'INSERT INTO results (nOrder, page_id, name) VALUES (' || current_nOrder || ',' || page_id_temp || ',' || quote_literal(page_name_temp) || ')'; current_nOrder := current_nOrder + 1; END IF; --Loop through results for page parents LOOP SELECT INTO page_id_temp, page_name_temp parent.page_id as parent_id, CASE WHEN parent.title_abbr IS NOT NULL THEN parent.title_abbr ELSE parent.title END as name FROM page AS child INNER JOIN page AS parent ON child.subcat_id = parent.page_id WHERE child.page_id = current_page; IF FOUND THEN EXECUTE 'INSERT INTO results (nOrder, page_id, name) VALUES (' || current_nOrder || ',' || page_id_temp || ',' || quote_literal(page_name_temp) || ')'; current_page = page_id_temp; current_nOrder := current_nOrder + 1; ELSE EXIT; END IF; END LOOP; SELECT INTO page_id_temp, page_name_temp c.default_page as parent_id, c.name FROM page p INNER JOIN category c ON c.cat_id = p.cat_id WHERE page_id = starting_page; IF FOUND THEN EXECUTE 'INSERT INTO results (nOrder, page_id, name) VALUES (' || current_nOrder || ',' || page_id_temp || ',' || quote_literal(page_name_temp) || ')'; END IF; FOR page_results IN EXECUTE 'SELECT * FROM results ORDER BY nOrder DESC' LOOP IF path = '' THEN IF withLinkTags IS TRUE THEN path := '<a href="index.php?pid=' || page_results.page_id || '">'; path := path || page_results.name; path := path || '</a>'; ELSE path := page_results.name; END IF; ELSE IF withLinkTags IS TRUE THEN path := path || delimiter; path := path || '<a href="index.php?pid=' || page_results.page_id || '">'; path := path || page_results.name; path := path || '</a>'; ELSE path := path || delimiter || page_results.name; END IF; END IF; END LOOP; RETURN path; END;$_$ LANGUAGE plpgsql; On Apr 23, 2005, at 11:17 AM, Tom Lane wrote: > Richard Plotkin <richard@richardplotkin.com> writes: >> /usr/local/pgsql/data/base/17234/42791 >> /usr/local/pgsql/data/base/17234/42791.1 >> /usr/local/pgsql/data/base/17234/42791.2 >> /usr/local/pgsql/data/base/17234/42791.3 >> ... > > Well, that is certainly a table or index of some kind. > > Go into database 17234 --- if you are not certain which one that is, > see > select datname from pg_database where oid = 17234 > and do > select relname from pg_class where relfilenode = 42791 > > The only way I could see for this to not find the table is if the table > creation has not been committed yet. Do you have any apps that create > and fill a table in a single transaction? > > regards, tom lane >
pgsql-performance by date: