Thread: Alias for function return buffer in pl/pgsql?
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
Hello
array_append is relative slow. You can use SRF function for someone (I
am not sure if it's your case, but maybe).
postgres=# create or replace function buida(m int) returns int[] as
$$declare r int[] = '{}'; begin for i in 1..m loop r := r || i; end
loop; return r; end $$ language plpgsql strict immutable;
CREATE FUNCTION
postgres=# SELECT array_upper(buida(10000),1);
array_upper
-------------
10000
(1 row)
Time: 324,388 ms
postgres=# create or replace function buida(m int) returns int[] as
$$begin return array(select * from _buida($1)); end $$ language
plpgsql strict immutable;
CREATE FUNCTION
postgres=# create or replace function _buida(m int) returns setof int
as $$begin for i in 1..m loop return next i; end loop; return; end $$
language plpgsql strict immutable;
CREATE FUNCTION
postgres=# SELECT array_upper(buida(10000),1);
array_upper
-------------
10000
(1 row)
Time: 24,191 ms
2008/8/13 Bart Grantham <bg@logicworks.net>:
> 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.
this syntax is correct, it's probably postgresql bug
regards
pavel stehule
>
>
>
> 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
> > > 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); you have to cast. This code works: postgres=# create type xxtp as (a integer, b varchar); CREATE TYPE Time: 6,458 ms postgres=# create table xx(a integer, b varchar); CREATE TABLE Time: 54,053 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 Time: 5,993 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 Time: 3,393 ms postgres=# insert into xx select 1, 'hhh'; INSERT 0 1 >postgres=# create or replace function x() returns setof xxtp as $$begin return query select * from xx; return; end$$languageplpgsql; CREATE FUNCTION Time: 4,392 ms postgres=# select * from x(); a | b ---+----- 1 | hhh 1 | hhh 1 | hhh (3 rows) postgres=# create or replace function x() returns setof xxtp as $$begin return query select 1,'kkk'; return; end$$language plpgsql; CREATE FUNCTION Time: 4,577 ms postgres=# select * from x(); ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function "x" line 1 at RETURN QUERY postgres=# create or replace function x() returns setof xxtp as $$begin return query select 1,'kkk'::varchar; return; end$$language plpgsql; CREATE FUNCTION Time: 3,395 ms postgres=# select * from x(); a | b ---+----- 1 | kkk (1 row) regards Pavel Stehule > > > 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
Interesting. After some toying around I've figured out that this produces "ERROR: structure of query does not match
functionresult type":
RETURN QUERY SELECT connection_id, connection_type_id, connector_node_id,
connector_node_type_id, connectee_node_id, connectee_node_type_id,
current, timestamp::timestamp, $2+1
FROM connections WHERE connection_type_id = 1 AND connector_node_id = ANY($1);
Whereas this doesn't:
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);
Is there something about timestamps that prevents plpgsql from seeing where casting is unnecessary? For the record,
thatfield in the function's return type is definitely a timestamp, same for that column in the table. And I know it's
badform for me to have named the column "timestamp" when that's the name of the type. Oops. :)
Thanks for the help, I've shaved about 30% off the query time now that I can use RETURN QUERY.
BG
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Wednesday, August 13, 2008 7:51 AM
To: Bart Grantham
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Alias for function return buffer in pl/pgsql?
>
>
> 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);
you have to cast. This code works:
postgres=# create type xxtp as (a integer, b varchar);
CREATE TYPE
Time: 6,458 ms
postgres=# create table xx(a integer, b varchar);
CREATE TABLE
Time: 54,053 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
Time: 5,993 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
Time: 3,393 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
>postgres=# create or replace function x() returns setof xxtp as $$begin return query select * from xx; return;
end$$languageplpgsql;
CREATE FUNCTION
Time: 4,392 ms
postgres=# select * from x();
a | b
---+-----
1 | hhh
1 | hhh
1 | hhh
(3 rows)
postgres=# create or replace function x() returns setof xxtp as
$$begin return query select 1,'kkk'; return; end$$language plpgsql;
CREATE FUNCTION
Time: 4,577 ms
postgres=# select * from x();
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "x" line 1 at RETURN QUERY
postgres=# create or replace function x() returns setof xxtp as
$$begin return query select 1,'kkk'::varchar; return; end$$language
plpgsql;
CREATE FUNCTION
Time: 3,395 ms
postgres=# select * from x();
a | b
---+-----
1 | kkk
(1 row)
regards
Pavel Stehule
>
>
> 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
Interesting. After some toying around I've figured out that this produces "ERROR: structure of query does not match
functionresult type":
RETURN QUERY SELECT connection_id, connection_type_id, connector_node_id,
connector_node_type_id, connectee_node_id, connectee_node_type_id,
current, timestamp::timestamp, $2+1
FROM connections WHERE connection_type_id = 1 AND connector_node_id = ANY($1);
Whereas this doesn't:
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);
Is there something about timestamps that prevents plpgsql from seeing where casting is unnecessary? For the record,
thatfield in the function's return type is definitely a timestamp, same for that column in the table. And I know it's
badform for me to have named the column "timestamp" when that's the name of the type. Oops. :)
BG
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Wednesday, August 13, 2008 7:51 AM
To: Bart Grantham
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Alias for function return buffer in pl/pgsql?
>
>
> 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);
you have to cast. This code works:
postgres=# create type xxtp as (a integer, b varchar);
CREATE TYPE
Time: 6,458 ms
postgres=# create table xx(a integer, b varchar);
CREATE TABLE
Time: 54,053 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
Time: 5,993 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
Time: 3,393 ms
postgres=# insert into xx select 1, 'hhh';
INSERT 0 1
>postgres=# create or replace function x() returns setof xxtp as $$begin return query select * from xx; return;
end$$languageplpgsql;
CREATE FUNCTION
Time: 4,392 ms
postgres=# select * from x();
a | b
---+-----
1 | hhh
1 | hhh
1 | hhh
(3 rows)
postgres=# create or replace function x() returns setof xxtp as
$$begin return query select 1,'kkk'; return; end$$language plpgsql;
CREATE FUNCTION
Time: 4,577 ms
postgres=# select * from x();
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "x" line 1 at RETURN QUERY
postgres=# create or replace function x() returns setof xxtp as
$$begin return query select 1,'kkk'::varchar; return; end$$language
plpgsql;
CREATE FUNCTION
Time: 3,395 ms
postgres=# select * from x();
a | b
---+-----
1 | kkk
(1 row)
regards
Pavel Stehule
>
>
> 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