In-Reply-To: <12135.1114280225@sss.pgh.pa.us>
References: <6f4567d4e64297b356e2d71ec392cdae@richardplotkin.com>
	<4574.1114030270@sss.pgh.pa.us>
	<9c5b2bf6a0f5a0bc0abeb6b6c5cf7973@richardplotkin.com>
	<2a9467bc6e6dab5d997427c4ef6158e3@richardplotkin.com>
	<12016.1114279585@sss.pgh.pa.us>
	<c4a45adf8fdcb550f487a1ca7f9cfff5@richardplotkin.com>
	<12135.1114280225@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <31935aa56e9539c5648cb09e78152c03@richardplotkin.com>
Content-Transfer-Encoding: 7bit
Cc: pgsql-performance@postgresql.org
From: Richard Plotkin <richard@richardplotkin.com>
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes? 
Date: Sat, 23 Apr 2005 11:37:30 -0700
To: Tom Lane <tgl@sss.pgh.pa.us>

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
>