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@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
>