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_tempparent.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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
