Re: Disk filling, CPU filling, renegade inserts and deletes?

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(at)richardplotkin(dot)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
>