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 array of 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 array of 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: 42804Any 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
"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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 asFor 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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=dca25c2562199ce1e7e26367613912a8eadbbde8Alternatively, 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general