Hello all, long time no chit-chat on the PG mailing list. We're upgrading from
8.0.3 to 8.3 and found that some stored procedures utilizing int_agg that we
had left over from 7.3 had terrible performance. No problem, using ANY() we're
able to regain that performance, more or less, and at the same time greatly
simplify our stored procedures. But things can never be fast enough, can they?
So I have a question or two. Here's my function for reference:
CREATE OR REPLACE FUNCTION bg_nodes2descendants(INT[], INT) RETURNS SETOF
connection_generation AS
'
DECLARE
_row connection_generation%ROWTYPE;
_children INT[];
BEGIN
-- this is faster than constructing in the loop below
--_children = array(SELECT connectee_node_id FROM connections WHERE
connection_type_id = 1 AND connector_node_id = ANY($1));
FOR _row IN
SELECT connection_id, connection_type_id, connector_node_id,
connector_node_type_id, connectee_node_id,
connectee_node_type_id, current, timestamp, $2 + 1
FROM connections WHERE connection_type_id = 1 AND connector_node_id =
ANY($1)
LOOP
_children := _children || _row.connectee_node_id;
RETURN NEXT _row;
END LOOP;
IF FOUND THEN
RETURN QUERY SELECT * FROM bg_nodes2descendants(_children, $2+1);
END IF;
RETURN;
END
' LANGUAGE 'plpgsql';
So, my concern is alluded to in the comment above. When I use this
function in places where it returns large results, building the _children array
directly (in the commented out line) is about 25% faster. But I'd like to
avoid building the children array altogether and would instead like to generate
that array from the already collected output rows. For example, right before
the recursive call, I'd like to select a column of the buffered output rows,
cast it to an integer[], and pass it into the recursive call. Is there an
internal value I can access for this such as:
_children := array(SELECT connectee_node_id FROM $output);
Bonus question - if I rewrite the first FOR loop as:
RETURN QUERY SELECT connection_id, connection_type_id, connector_node_id,
connector_node_type_id, connectee_node_id,
connectee_node_type_id, current, timestamp, $2 + 1
FROM connections
WHERE connection_type_id = 1 AND connector_node_id = ANY($1);
I get "ERROR: structure of query does not match function result type", even
though the type signatures of the returned columns match the
"connection_generation" rowtype. I am pretty sure this could be resolved by
casting the resulting columns to that row type, but I am lost as to how the
syntax to do such a thing would look.
Thanks in advance for the help, and keep up the great work. PG8.3 is an
amazing piece of software and it blows me away how much more advanced it gets
with every release.
Bart Grantham
VP of R&D
Logicworks Inc. - Complex and Managed Hosting
