Re: Memory use in 8.3 plpgsql with heavy use of xpath()

["Matt Magoffin" <postgresql.org@xxxxxxx>]

>> 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();