>> OK, I'll try to come up with something. Do you have a recommended way of
>> capturing the amount memory being used by Postgres related to this? I
>> was
>> thinking I would have a plpgsql function that loops a large number of
>> times, calling a few xpath() calls,
>
> Yeah, that's what I'd try first.
>
> regards, tom lane
Below is a test case that simulates the use of xpath() within a plpgsql
function in my application. I'm not sure of a good way to measure the
retained memory before/after the script runs, however. I ran this several
times and the postgres process that ran it does have more memory allocated
afterwards than before, but I don't know what is expected and what isn't.
You can adjust the number of loops that run at
num_loops int8 := 1000000;
-- m@
-----
DROP TABLE IF EXISTS tmp_xml_test CASCADE;
DROP TABLE IF EXISTS tmp_xml_addr CASCADE;
CREATE TEMPORARY TABLE tmp_xml_test(
id int4, x xml, primary key (id));
CREATE TEMPORARY TABLE tmp_xml_addr(
id int4, xmlpos int4, street text, city text, state text, zip text,
primary key (id, xmlpos));
CREATE OR REPLACE FUNCTION tmp_extract_address(data_row tmp_xml_test)
RETURNS void AS
$BODY$
DECLARE
addr_row tmp_xml_addr%ROWTYPE;
tmp_txt text;
tmp_array xml[];
BEGIN
addr_row.id := data_row.id;
DELETE FROM tmp_xml_addr WHERE id = data_row.id;
tmp_array := xpath(
'/po:purchaseOrder/*[name(.) = "shipTo" or name(.) =
"billTo"]',
data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]);
IF array_upper(tmp_array, 1) > 0 THEN
FOR idx IN 1..array_upper(tmp_array, 1) LOOP
addr_row.xmlpos := idx;
addr_row.street := upper(XMLSERIALIZE(CONTENT(xpath(
'/po:purchaseOrder/*[name(.) = "shipTo" or
name(.) = "billTo"]['
||idx|| ']/po:street[1]/text()',
data_row.x, ARRAY[ARRAY['po',
'http://www.example.com/PO1']]))[1] as
text));
addr_row.city := upper(XMLSERIALIZE(CONTENT(xpath(
'/po:purchaseOrder/*[name(.) = "shipTo" or
name(.) = "billTo"]['
||idx|| ']/po:city[1]/text()',
data_row.x, ARRAY[ARRAY['po',
'http://www.example.com/PO1']]))[1] as
text));
addr_row.state := upper(XMLSERIALIZE(CONTENT(xpath(
'/po:purchaseOrder/*[name(.) = "shipTo" or
name(.) = "billTo"]['
||idx|| ']/po:state[1]/text()',
data_row.x, ARRAY[ARRAY['po',
'http://www.example.com/PO1']]))[1] as
text));
addr_row.zip := upper(XMLSERIALIZE(CONTENT(xpath(
'/po:purchaseOrder/*[name(.) = "shipTo" or
name(.) = "billTo"]['
||idx|| ']/po:zip[1]/text()',
data_row.x, ARRAY[ARRAY['po',
'http://www.example.com/PO1']]))[1] as
text));
INSERT INTO tmp_xml_addr (id, xmlpos, street, city,
state, zip) VALUES
(addr_row.id, addr_row.xmlpos, addr_row.street,
addr_row.city,
addr_row.state, addr_row.zip);
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION tmp_populate_addr() RETURNS trigger AS
$BODY$
BEGIN
PERFORM tmp_extract_address(NEW);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER tmp_populate_addr_trigger AFTER INSERT OR UPDATE ON
tmp_xml_test
FOR EACH ROW EXECUTE PROCEDURE tmp_populate_addr();
DROP FUNCTION IF EXISTS tmp_test_loop();
CREATE OR REPLACE FUNCTION tmp_test_loop() RETURNS SETOF tmp_xml_addr AS
$BODY$
DECLARE
num_loops int8 := 1000000;
BEGIN
FOR idx IN 1..num_loops LOOP
INSERT INTO tmp_xml_test VALUES (idx,
$$<purchaseOrder xmlns="http://www.example.com/PO1"; orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>Mill Valley</city>
<state>CA</state>
<zip>90952</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment>
</item>
<item partNum="926-AA">
<productName>Baby Monitor</productName>
<quantity>1</quantity>
<USPrice>39.98</USPrice>
<shipDate>1999-05-21</shipDate>
</item>
</items>
</purchaseOrder>$$);
END LOOP;
FOR idx IN 1..num_loops LOOP
UPDATE tmp_xml_test SET id = idx WHERE id = idx;
END LOOP;
RETURN QUERY SELECT * FROM tmp_xml_addr ORDER BY id, xmlpos;
END;
$BODY$
LANGUAGE 'plpgsql';
SELECT * FROM tmp_test_loop();
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
