Thread: custom average window function failure
Hello,
Until I upgraded to PostgreSQL 9.6, a custom average function was
working well as a window function. It's meant to average a composite
type:
CREATE TYPE public.angle_vectors AS
(x double precision,
y double precision);
COMMENT ON TYPE public.angle_vectors
IS 'This type holds the x (sine) and y (cosine) components of angle(s).';
The average function:
CREATE OR REPLACE FUNCTION public.angle_vectors_avg(angle_vectors_arr angle_vectors[])
RETURNS vector AS
$BODY$
DECLARE
x_avg double precision;
y_avg double precision;
magnitude double precision;
angle_avg double precision;
BEGIN
SELECT avg(x) INTO x_avg FROM unnest(angle_vectors_arr) irows;
SELECT avg(y) INTO y_avg FROM unnest(angle_vectors_arr) irows;
magnitude := sqrt((x_avg ^ 2.0) + (y_avg ^ 2.0));
angle_avg := degrees(atan2(x_avg, y_avg));
IF (angle_avg < 0 ) THEN
angle_avg := angle_avg + 360.0;
END IF;
RETURN (angle_avg, magnitude);
END
$BODY$
LANGUAGE plpgsql STABLE
COST 100;
COMMENT ON FUNCTION public.angle_vectors_avg(angle_vectors[]) IS 'This function computes the average angle from an
arrayof concatenated angle_vectors data type singletons. It returns vector data type.';
And the aggregate:
CREATE AGGREGATE public.avg(angle_vectors) (
SFUNC=array_append,
STYPE=angle_vectors[],
FINALFUNC=angle_vectors_avg
);
Query below used to work in PostgreSQL 9.5:
SELECT "time", avg((random(), random())::angle_vectors) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp, '5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
but is now failing with the following message in 9.6:
ERROR: input data type is not an array
********** Error **********
ERROR: input data type is not an array
SQL state: 42804
Any thoughts on what has changed that is leading to this failure?
--
Seb
On 10/08/2016 08:21 PM, Seb wrote:
> Hello,
>
> Until I upgraded to PostgreSQL 9.6, a custom average function was
> working well as a window function. It's meant to average a composite
> type:
>
> CREATE TYPE public.angle_vectors AS
> (x double precision,
> y double precision);
> COMMENT ON TYPE public.angle_vectors
> IS 'This type holds the x (sine) and y (cosine) components of angle(s).';
>
> The average function:
>
> CREATE OR REPLACE FUNCTION public.angle_vectors_avg(angle_vectors_arr angle_vectors[])
> RETURNS vector AS
> $BODY$
> DECLARE
> x_avg double precision;
> y_avg double precision;
> magnitude double precision;
> angle_avg double precision;
>
> BEGIN
> SELECT avg(x) INTO x_avg FROM unnest(angle_vectors_arr) irows;
> SELECT avg(y) INTO y_avg FROM unnest(angle_vectors_arr) irows;
> magnitude := sqrt((x_avg ^ 2.0) + (y_avg ^ 2.0));
> angle_avg := degrees(atan2(x_avg, y_avg));
> IF (angle_avg < 0 ) THEN
> angle_avg := angle_avg + 360.0;
> END IF;
> RETURN (angle_avg, magnitude);
> END
> $BODY$
> LANGUAGE plpgsql STABLE
> COST 100;
> COMMENT ON FUNCTION public.angle_vectors_avg(angle_vectors[]) IS 'This function computes the average angle from an
arrayof concatenated angle_vectors data type singletons. It returns vector data type.';
>
> And the aggregate:
>
> CREATE AGGREGATE public.avg(angle_vectors) (
> SFUNC=array_append,
> STYPE=angle_vectors[],
> FINALFUNC=angle_vectors_avg
> );
>
> Query below used to work in PostgreSQL 9.5:
>
> SELECT "time", avg((random(), random())::angle_vectors) over w
> from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp, '5 hours') as t("time")
> window w as (partition by date_trunc('day', "time") order by "time");
>
> but is now failing with the following message in 9.6:
>
> ERROR: input data type is not an array
>
> ********** Error **********
>
> ERROR: input data type is not an array
> SQL state: 42804
>
> Any thoughts on what has changed that is leading to this failure?
Not sure. When I tried using the above(on 9.5) it failed during the
CREATE OR REPLACE FUNCTION public.angle_vectors_avg stage with:
ERROR: type "vector" does not exist
So where is that coming from in your setup?
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sun, 9 Oct 2016 06:44:10 -0700,
Adrian Klaver <adrian.klaver@aklaver.com> wrote:
[...]
> Not sure. When I tried using the above(on 9.5) it failed during the
> CREATE OR REPLACE FUNCTION public.angle_vectors_avg stage with:
> ERROR: type "vector" does not exist
> So where is that coming from in your setup?
Aw nuts, I forgot to include that type definition. Here it is:
CREATE TYPE public.vector AS
(angle double precision,
magnitude double precision);
COMMENT ON TYPE public.vector
IS 'This type holds the basic descriptors of a vector; namely, angle and magnitude.';
--
Seb
On 10/09/2016 08:01 AM, Sebastian P. Luque wrote:
> On Sun, 9 Oct 2016 06:44:10 -0700,
> Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> [...]
>
>> Not sure. When I tried using the above(on 9.5) it failed during the
>> CREATE OR REPLACE FUNCTION public.angle_vectors_avg stage with:
>
>> ERROR: type "vector" does not exist
>
>
>> So where is that coming from in your setup?
>
> Aw nuts, I forgot to include that type definition. Here it is:
>
> CREATE TYPE public.vector AS
> (angle double precision,
> magnitude double precision);
> COMMENT ON TYPE public.vector
> IS 'This type holds the basic descriptors of a vector; namely, angle and magnitude.';
>
>
Hmm:
test=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
(1 row)
test=# select avg((random(), random())::angle_vectors);
avg
--------------------------------------
(62.4781575734486,0.865270065328572)
test=# select "time" from generate_series('2016-10-08'::timestamp,
'2016-10-10'::timestamp, '5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
time
---------------------
2016-10-08 00:00:00
2016-10-08 05:00:00
2016-10-08 10:00:00
2016-10-08 15:00:00
2016-10-08 20:00:00
2016-10-09 01:00:00
2016-10-09 06:00:00
2016-10-09 11:00:00
2016-10-09 16:00:00
2016-10-09 21:00:00
(10 rows)
test=# SELECT "time", avg(random()) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp,
'5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
time | avg
---------------------+-------------------
2016-10-08 00:00:00 | 0.387926945462823
2016-10-08 05:00:00 | 0.649316050112247
2016-10-08 10:00:00 | 0.608540423369656
2016-10-08 15:00:00 | 0.561799361603335
2016-10-08 20:00:00 | 0.54945012088865
2016-10-09 01:00:00 | 0.130873893853277
2016-10-09 06:00:00 | 0.443627830361947
2016-10-09 11:00:00 | 0.314536933631947
2016-10-09 16:00:00 | 0.425128075061366
2016-10-09 21:00:00 | 0.385504625830799
test=# SELECT "time", avg((random(), random())::angle_vectors) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp,
'5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
ERROR: input data type is not an array
The parts work, the whole does not. At this point I have no idea why.
--
Adrian Klaver
adrian.klaver@aklaver.com
Seb <spluque@gmail.com> writes:
> Any thoughts on what has changed that is leading to this failure?
Clearly a bug --- the wrong type OIDs are being passed down to
array_append. It should be told that it's getting called as
(angle_vectors[], angle_vectors) returns angle_vectors[]
but what it's actually getting told is
(vector, angle_vectors) returns vector
which naturally makes it spit up because "vector" isn't an array type.
I don't think control ever reaches your custom finalfunc at all.
Probably somebody fat-fingered this while refactoring code in the
aggregate/windowfunction area. Possibly me :-(. Haven't found
exactly where things are going off the rails, but it's clearly
a PG bug. Thanks for the report!
regards, tom lane
On 10/09/2016 08:46 AM, Tom Lane wrote:
> Seb <spluque@gmail.com> writes:
>> Any thoughts on what has changed that is leading to this failure?
>
> Clearly a bug --- the wrong type OIDs are being passed down to
> array_append. It should be told that it's getting called as
>
> (angle_vectors[], angle_vectors) returns angle_vectors[]
>
> but what it's actually getting told is
>
> (vector, angle_vectors) returns vector
>
> which naturally makes it spit up because "vector" isn't an array type.
> I don't think control ever reaches your custom finalfunc at all.
For my edification, why does this work?:
test[5442]=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
(1 row)
test[5442]=# select avg((random(), random())::angle_vectors);
avg
--------------------------------------
(25.0294036061885,0.892887489473068)
(1 row)
>
> Probably somebody fat-fingered this while refactoring code in the
> aggregate/windowfunction area. Possibly me :-(. Haven't found
> exactly where things are going off the rails, but it's clearly
> a PG bug. Thanks for the report!
>
> regards, tom lane
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/09/2016 08:46 AM, Tom Lane wrote:
>> Clearly a bug --- the wrong type OIDs are being passed down to
>> array_append. It should be told that it's getting called as
> For my edification, why does this work?:
On closer inspection, the error is only in the
aggregate-used-as-window-function case, not plain aggregation.
regards, tom lane
On Sun, 09 Oct 2016 12:40:09 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 10/09/2016 08:46 AM, Tom Lane wrote: >>> Clearly a bug --- the wrong type OIDs are being passed down to >>> array_append. It should be told that it's getting called as >> For my edification, why does this work?: > On closer inspection, the error is only in the > aggregate-used-as-window-function case, not plain aggregation. Yes, I see the same phenomenon. Could someone suggest a workaround until this is fixed? I'm under the gun to submit output tables and the only thing I can think of is a crawling slow loop to step through each window twice: once using the plain aggregation and another without just get all rows. I highly doubt it will be worthwhile, given it's going to be about 1000 iterations, and each one would take about 30-45 min... -- Seb
"Sebastian P. Luque" <spluque@gmail.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> On closer inspection, the error is only in the >> aggregate-used-as-window-function case, not plain aggregation. > Yes, I see the same phenomenon. Could someone suggest a workaround > until this is fixed? I'm under the gun to submit output tables and the > only thing I can think of is a crawling slow loop to step through each > window twice: once using the plain aggregation and another without just > get all rows. I highly doubt it will be worthwhile, given it's going to > be about 1000 iterations, and each one would take about 30-45 min... Are you in a position to apply patches? It's a one-line fix: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 Alternatively, the problem doesn't manifest when the aggregate transtype and output type are the same, so you could probably refactor your code to use plain array_agg and apply the finalfunc separately in the SQL query. regards, tom lane
On 10/09/2016 09:40 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 10/09/2016 08:46 AM, Tom Lane wrote: >>> Clearly a bug --- the wrong type OIDs are being passed down to >>> array_append. It should be told that it's getting called as > >> For my edification, why does this work?: > > On closer inspection, the error is only in the > aggregate-used-as-window-function case, not plain aggregation. Got it, thanks. > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 09 Oct 2016 16:00:21 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Sebastian P. Luque" <spluque@gmail.com> writes: >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> On closer inspection, the error is only in the >>> aggregate-used-as-window-function case, not plain aggregation. >> Yes, I see the same phenomenon. Could someone suggest a workaround >> until this is fixed? I'm under the gun to submit output tables and >> the only thing I can think of is a crawling slow loop to step through >> each window twice: once using the plain aggregation and another >> without just get all rows. I highly doubt it will be worthwhile, >> given it's going to be about 1000 iterations, and each one would take >> about 30-45 min... > Are you in a position to apply patches? It's a one-line fix: > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 > Alternatively, the problem doesn't manifest when the aggregate > transtype and output type are the same, so you could probably refactor > your code to use plain array_agg and apply the finalfunc separately in > the SQL query. Perfect, I'll try the latter option on this one. Thanks so much to both of you for your prompt feedback! -- Seb
On Sun, Oct 9, 2016 at 10:10 PM, Sebastian P. Luque <spluque@gmail.com> wrote: > On Sun, 09 Oct 2016 16:00:21 -0400, > Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> "Sebastian P. Luque" <spluque@gmail.com> writes: >>> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> On closer inspection, the error is only in the >>>> aggregate-used-as-window-function case, not plain aggregation. > >>> Yes, I see the same phenomenon. Could someone suggest a workaround >>> until this is fixed? I'm under the gun to submit output tables and >>> the only thing I can think of is a crawling slow loop to step through >>> each window twice: once using the plain aggregation and another >>> without just get all rows. I highly doubt it will be worthwhile, >>> given it's going to be about 1000 iterations, and each one would take >>> about 30-45 min... > >> Are you in a position to apply patches? It's a one-line fix: >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 > >> Alternatively, the problem doesn't manifest when the aggregate >> transtype and output type are the same, so you could probably refactor >> your code to use plain array_agg and apply the finalfunc separately in >> the SQL query. > > Perfect, I'll try the latter option on this one. Thanks so much to both > of you for your prompt feedback! Aside: nice use of custom aggregates through window functions. I use this tactic heavily. merlin
On Sun, 09 Oct 2016 16:00:21 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Are you in a position to apply patches? It's a one-line fix: > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 I'd like to try this by obtaining the Debian source package, downloading and applying patches such as this one, and then rebuilding. However, I don't know how best to download the patches from the URL above. If I click on the "patch" link, I'm simply taken to the section where this is shown on the screen. What's the procecure to download these patches? Apologies, if this is too off-topic or an old question. Hopefully, this doesn't require maintaining a local Git repository, as I'm only interested in applying patches against the Debian package to be able to build and install a local *.deb until the next release. -- Seb
Sebastian Luque <spluque@gmail.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Are you in a position to apply patches? It's a one-line fix: >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 > I'd like to try this by obtaining the Debian source package, downloading > and applying patches such as this one, and then rebuilding. However, I > don't know how best to download the patches from the URL above. If I > click on the "patch" link, I'm simply taken to the section where this is > shown on the screen. What's the procecure to download these patches? Clicking the "patch" link and then doing "save to file" in your browser should produce a file that will work as a patch. regards, tom lane
On Sat, 15 Oct 2016 22:24:31 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sebastian Luque <spluque@gmail.com> writes: >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Are you in a position to apply patches? It's a one-line fix: >>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=dca25c2562199ce1e7e26367613912a8eadbbde8 >> I'd like to try this by obtaining the Debian source package, >> downloading and applying patches such as this one, and then >> rebuilding. However, I don't know how best to download the patches >> from the URL above. If I click on the "patch" link, I'm simply taken >> to the section where this is shown on the screen. What's the >> procecure to download these patches? > Clicking the "patch" link and then doing "save to file" in your > browser should produce a file that will work as a patch. Thanks, I had completely missed the "patch" link right at the top of the page, so was only seeing the ones below the message, which work differently. It all works with the top link. -- Seb