From: Richard Plotkin <richard@richardplotkin.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes?
Date: Sat, 23 Apr 2005 11:37:30 -0700
Message-ID: <31935aa56e9539c5648cb09e78152c03@richardplotkin.com> (raw)
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>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: richard@richardplotkin.com, tgl@sss.pgh.pa.us
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes?
In-Reply-To: <31935aa56e9539c5648cb09e78152c03@richardplotkin.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox