pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedError from array_agg when table has many rows
5+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2025-03-09 fedfcf6650 43847dd5e9 a7aa9f21f2 Don't try to parallelize array_agg() on an anonymous record type.
* Error from array_agg when table has many rows
@ 2025-03-07 20:07 Kirill Zdornyy <kirill@dineserve.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kirill Zdornyy @ 2025-03-07 20:07 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello,
After upgrading from PostgreSQL 12.19 to PostgreSQL 16.3 the function "array_agg" gives me the following error under certain conditions.
ERROR: input of anonymous composite types is not implemented
I was also able reproduce the issue on PostgreSQL 17.4 via the latest currently available Docker image.
Please see the following SQL script for the minimum reproduction.
```
drop table if exists my_table;
create table my_table
(
my_text text
);
-- Insert a minimum of 200,000 rows.
do
$$
declare
counter integer := 0;
begin
while counter < 200000
loop
INSERT INTO my_table (my_text) VALUES ('A simple sentence.');
counter := counter + 1;
end loop;
end;
$$;
select array_agg(t)
from (select my_text from my_table WHERE my_text != '') t;
```
On my machine the issue appears if 200,000 or more rows exist. I used EXPLAIN and noticed that the query plan is different if this condition is met.
This is the query plan if fewer than 200,000 rows exist.
```
Aggregate (cost=1935.40..1935.41 rows=1 width=32)
-> Seq Scan on my_table (cost=0.00..1719.90 rows=86199 width=32)
Filter: (my_text <> ''::text)
```
This is the query plan if more than 200,000 rows exist.
```
Finalize Aggregate (cost=3801.65..3801.66 rows=1 width=32)
-> Gather (cost=3801.53..3801.64 rows=1 width=32)
Workers Planned: 1
-> Partial Aggregate (cost=2801.53..2801.54 rows=1 width=32)
-> Parallel Seq Scan on my_table (cost=0.00..2548.00 rows=101411 width=32)
Filter: (my_text <> ''::text)
```
I am also able to reproduce the issue on AWS RDS PostgreSQL 16.3.
Thank you!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error from array_agg when table has many rows
@ 2025-03-08 09:38 Richard Guo <guofenglinux@gmail.com>
parent: Kirill Zdornyy <kirill@dineserve.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Guo @ 2025-03-08 09:38 UTC (permalink / raw)
To: Kirill Zdornyy <kirill@dineserve.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sat, Mar 8, 2025 at 8:10 AM Kirill Zdornyy <kirill@dineserve.com> wrote:
> After upgrading from PostgreSQL 12.19 to PostgreSQL 16.3 the function "array_agg" gives me the following error under certain conditions.
>
> ERROR: input of anonymous composite types is not implemented
>
> I was also able reproduce the issue on PostgreSQL 17.4 via the latest currently available Docker image.
Thanks for the report! I can reproduce it on master with the query
below.
create table t (a int);
insert into t values (1);
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_table_scan_size=0;
# select array_agg(s) from (select * from t) s;
ERROR: input of anonymous composite types is not implemented
And the plan for this query is:
explain (verbose, costs off)
select array_agg(s) from (select * from t) s;
QUERY PLAN
---------------------------------------------------
Finalize Aggregate
Output: array_agg(ROW(t.a))
-> Gather
Output: (PARTIAL array_agg(ROW(t.a)))
Workers Planned: 2
-> Partial Aggregate
Output: PARTIAL array_agg(ROW(t.a))
-> Parallel Seq Scan on public.t
Output: t.a
(9 rows)
We are performing deserialization during the final phase of the
aggregation on data of type RECORD but we fail to provide a valid
typmod (array_agg_deserialize() uses -1 as the typmod when calling the
receiveproc).
I haven't verified it, but I suspect it's related to 16fd03e95.
Thanks
Richard
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error from array_agg when table has many rows
@ 2025-03-08 15:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-03-08 15:50 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Kirill Zdornyy <kirill@dineserve.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Richard Guo <guofenglinux@gmail.com> writes:
> We are performing deserialization during the final phase of the
> aggregation on data of type RECORD but we fail to provide a valid
> typmod (array_agg_deserialize() uses -1 as the typmod when calling the
> receiveproc).
> I haven't verified it, but I suspect it's related to 16fd03e95.
Yeah. I don't think there is any way for array_agg_deserialize to
know the correct typmod, so what we have to do is disable using
partial aggregation in this case. Fortunately there's a
policy-setting function that can be taught that, as attached.
regards, tom lane
Attachments:
disable_partial_aggregation_on_RECORD.patch (text/x-diff, 1.1K) download
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 9e567f3cc45..0ac8966e30f 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -2052,7 +2052,7 @@ resolve_aggregate_transtype(Oid aggfuncid,
/*
* agg_args_support_sendreceive
- * Returns true if all non-byval of aggref's arg types have send and
+ * Returns true if all non-byval types of aggref's args have send and
* receive functions.
*/
bool
@@ -2067,6 +2067,15 @@ agg_args_support_sendreceive(Aggref *aggref)
TargetEntry *tle = (TargetEntry *) lfirst(lc);
Oid type = exprType((Node *) tle->expr);
+ /*
+ * RECORD is a special case: it has typsend/typreceive functions, but
+ * record_recv only works if passed the correct typmod to identify the
+ * specific anonymous record type. array_agg_deserialize cannot do
+ * that, so we have to disclaim support for the case.
+ */
+ if (type == RECORDOID)
+ return false;
+
typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(type));
if (!HeapTupleIsValid(typeTuple))
elog(ERROR, "cache lookup failed for type %u", type);
* Re: Error from array_agg when table has many rows
@ 2025-03-09 07:22 David Rowley <dgrowleyml@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David Rowley @ 2025-03-09 07:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; Kirill Zdornyy <kirill@dineserve.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sun, 9 Mar 2025 at 04:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Richard Guo <guofenglinux@gmail.com> writes:
> > We are performing deserialization during the final phase of the
> > aggregation on data of type RECORD but we fail to provide a valid
> > typmod (array_agg_deserialize() uses -1 as the typmod when calling the
> > receiveproc).
>
> > I haven't verified it, but I suspect it's related to 16fd03e95.
>
> Yeah. I don't think there is any way for array_agg_deserialize to
> know the correct typmod, so what we have to do is disable using
> partial aggregation in this case. Fortunately there's a
> policy-setting function that can be taught that, as attached.
The only way I can think of to get that would be to special-case
array_agg_serialize() to have it serialize the typmod when the send
function is record_send(), then add a similar special-case to
array_agg_deserialize() to check for a record_recv() and deserialize
the typmod there. That doesn't seem very pretty, so I'm happy to go
with your fix to disable parallel aggregates for this case.
David
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error from array_agg when table has many rows
@ 2025-03-09 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-03-09 17:14 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Richard Guo <guofenglinux@gmail.com>; Kirill Zdornyy <kirill@dineserve.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
David Rowley <dgrowleyml@gmail.com> writes:
> On Sun, 9 Mar 2025 at 04:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah. I don't think there is any way for array_agg_deserialize to
>> know the correct typmod, so what we have to do is disable using
>> partial aggregation in this case. Fortunately there's a
>> policy-setting function that can be taught that, as attached.
> The only way I can think of to get that would be to special-case
> array_agg_serialize() to have it serialize the typmod when the send
> function is record_send(), then add a similar special-case to
> array_agg_deserialize() to check for a record_recv() and deserialize
> the typmod there. That doesn't seem very pretty, so I'm happy to go
> with your fix to disable parallel aggregates for this case.
Yeah ... we could probably make that work if we had to, but the
ugliness would likely metastasize outside array_agg_[de]serialize.
Since it took more than a year to get a field report, I'm content
to just disable the optimization instead. Pushed that way.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-03-09 17:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-07 20:07 Error from array_agg when table has many rows Kirill Zdornyy <kirill@dineserve.com>
2025-03-08 09:38 ` Richard Guo <guofenglinux@gmail.com>
2025-03-08 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-09 07:22 ` David Rowley <dgrowleyml@gmail.com>
2025-03-09 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox