Subject: Re: Partial aggregates pushdown

From: Stephen Frost <[email-masked]>
Date: Tue, 28 Nov 2023 09:24:31 -0500

Greetings,

* Robert Haas ([email-masked]) wrote:
> On Mon, Nov 27, 2023 at 4:23 PM Tom Lane <[email-masked]> wrote:
> > Well, one of the founding principles of postgres_fdw was to be able
> > to talk to PG servers that are not of the same version as yours.
> > If we break that in the name of performance, we are going to have
> > a lot of unhappy users.  Even the ones who do get the benefit of
> > the speedup are going to be unhappy when it breaks because they
> > didn't upgrade local and remote at exactly the same time.
> 
> I agree with this.

+1.  We do want to continue to make this work- to the extent possible.
I don't think there's any problem with saying that when talking to an
older server, you don't get the same capabilities as you do when talking
to a newer server.

> > Just because we'd like to have it doesn't make the patch workable
> > in the real world.
> 
> And also with this in concept - I'd like to plan arbitrarily
> complicated queries perfectly and near-instantly, and then execute
> them at faster-than-light speed, but we can't. However, I don't
> understand the fatalism with respect to the feature at hand. As I said
> before, it's not like no other product has made this work. Sure, some
> of those products may not have the extensible system of data types
> that we do, or may not care about cross-version communication, but
> those don't seem like good enough reasons to just immediately give up.

Certainly there are other projects out there which are based on PG that
have managed to make this work and work really quite well.

> TBH, I suspect even some PG forks have made this work, like maybe PGXC
> or PGXL, although I don't know for certain. We might not like the
> trade-offs they made to get there, but we haven't even talked through
> possible design ideas yet, so it seems way too early to give up.

Yes, Citus[1] and Greenplum[2], to just name two.

I certainly understand the concern around the security of this and would
have thought the approach we'd use would be to not just take internal
state and pass it along but rather to provide a way for aggregates to
opt-in to supporting this and have them serialize/deserialize with
new dedicated functions that have appropriate checks to avoid bad things
happening.  That could also be versioned, perhaps, if we feel that's
necessary (I'm a bit skeptical, but it would hopefully address the
concern about different versions having different data that they want to
pass along).

> One of the things that I think is a problem in this area is that the
> ways we have to configure FDW connections are just not very rich.

Agreed.

> We're trying to cram everything into a set of strings that can be
> attached to the foreign server or the user mapping, but that's not a
> very good fit for something like how all the local SQL functions that
> might exist map onto all of the remote SQL functions that might exist.
> Now you might well say that we don't want the act of configuring a
> foreign data wrapper to be insanely complicated, and I would agree
> with that. But, on the other hand, as Larry Wall once said, a good
> programming language makes simple things simple and complicated things
> possible. I think our current configuration system is only
> accomplishing the first of those goals.

We've already got issues in this area with extensions- there's no way
for a user to say what version of an extension exists on the remote side
and no way for an extension to do anything different based on that
information.  Perhaps we could work on a solution to both of these
issues, but at the least I don't see holding back on this effort for a
problem that already exists but which we've happily accepted because of
the benefit it provides, like being able to push-down postgis bounding
box conditionals to allow for indexed lookups.

Thanks,

Stephen

[1]: https://docs.citusdata.com/en/v11.1/develop/reference_sql.html
[2]: https://postgresconf.org/conferences/Beijing/program/proposals/implementation-of-distributed-aggregation-in-greenplum

---

From: "[email-masked]" <[email-masked]>
Date: Wed, 6 Dec 2023 08:41:21 +0000

Hi Mr.Haas, hackers.

> From: Robert Haas <[email-masked]>
> Sent: Tuesday, November 28, 2023 5:03 AM
> Also, I want to make one other point here about security and reliability. Right now, there is no way for a user to feed
> arbitrary data to a deserialization function. Since serialization and deserialization functions are only used in the context of
> parallel query, we always know that the data fed to the deserialization function must have come from the serialization
> function on the same machine. Nor can users call the deserialization function directly with arbitrary data of their own
> choosing, because users cannot call functions that take or return internal. But with this system, it becomes possible to
> feed arbitrary data to a deserialization function.
> The user could redefine the function on the remote side so that it produces arbitrary data of their choosing, and the local
> deserialization function will ingest it.
> 
> That's potentially quite a significant problem. Consider for example that numericvar_deserialize() does no validity
> checking on any of the weight, sign, or dscale, but not all values for those fields are legal. Right now that doesn't matter,
> but if you can feed arbitrary data to that function, then it is. I don't know exactly what the consequences are if you can get
> that function to spit out a NumericVar with values outside the normal legal range. What can you do then?
> Store a bogus numeric on disk? Crash the server? Worst case, some problem like this could be a security issue allowing for
> escalation to superuser; more likely, it would be a crash bug, corrupt your database, or lead to unexpected and strange
> error messages.
> 
> Unfortunately, I have the unpleasant suspicion that most internal-type aggregates will be affected by this problem.
> Consider, for example, string_agg_deserialize(). Generally, strings are one of the least-constrained data types, so you
> might hope that this function would be OK. But it doesn't look very promising. The first int4 in the serialized representation
> is the cursor, which would have to be bounds-checked, lest someone provide a cursor that falls outside the bounds of the
> StringInfo and, maybe, cause a reference to an arbitrary memory location. Then the rest of the representation is the actual
> data, which could be anything. This function is used for both bytea and for text, and for bytea, letting the payload be
> anything is OK.
> But for text, the supplied data shouldn't contain an embedded zero byte, or otherwise be invalid in the server encoding. If
> it were, that would provide a vector to inject invalidly encoded data into the database. This feature can't be allowed to do
> that.
I completely overlooked this issue. I should have considered the risks of sending raw state values or serialized state
data directly from remote to local. I apologize.

> What could be a solution to this class of problems? One option is to just give up on supporting this feature for internal-type
> aggregates for now. That's easy enough to do, and just means we have less functionality, but it's sad because that's
> functionality we'd like to have. Another approach is to add necessary sanity checks to the relevant deserialization
> functions, but that seems a little hard to get right, and it would slow down parallel query cases which are probably going to
> be more common than the use of this feature. I think the slowdown might be significant, too. A third option is to change
> those aggregates in some way, like giving them a transition function that operates on some data type other than internal,
> but there again we have to be careful of slowdowns. A final option is to rethink the infrastructure in some way, like having
> a way to serialize to something other than bytea, for which we already have input functions with adequate checks. For
> instance, if string_agg_serialize() produced a record containing an integer column and a text or bytea column, we could
> attempt to ingest that record on the other side and presumably the right things would happen in the case of any invalid
> data. But I'm not quite sure what infrastructure would be required to make this kind of idea work.
Thank you very much for providing a direction towards resolving this issue.
As you have suggested as the last option, it seems that expanding the current mechanism of the aggregation
function is the only choice. It may take some time, but I will consider specific solutions.

> From: Robert Haas <[email-masked]>
> Sent: Tuesday, November 28, 2023 4:08 AM
> On Wed, Nov 22, 2023 at 1:32 AM Alexander Pyhalov <[email-masked]> wrote:
> > Hi. HAVING is also a problem. Consider the following query
> >
> > SELECT count(a) FROM t HAVING count(a) > 10 - we can't push it down to
> > foreign server as HAVING needs full aggregate result, but foreign
> > server don't know it.
> 
> I don't see it that way. What we would push to the foreign server would be something like SELECT count(a) FROM t. Then,
> after we get the results back and combine the various partial counts locally, we would locally evaluate the HAVING clause
> afterward. That is, partial aggregation is a barrier to pushing down HAVING clause itself, but it doesn't preclude pushing
> down the aggregation.
I understand what the problem is. I will try to fix it in the next version.

> From: Robert Haas <[email-masked]>
> Sent: Tuesday, November 28, 2023 5:03 AM
> On Wed, Nov 22, 2023 at 5:16 AM [email-masked]
> <[email-masked]> wrote:
> > I did not choose Approach2 because I was not confident that the
> > disadvantage mentioned in 2.(2)(a) would be accepted by the PostgreSQL development community.
> > If it is accepted, I think Approach 2 is smarter.
> > Could you please provide your opinion on which approach is preferable
> > after comparing these two approaches?
> > If we cannot say anything without comparing the amount of source code,
> > as Mr.Momjian mentioned, we need to estimate the amount of source code required to implement Approach2.
> 
> I've had the same concern, that approach #2 would draw objections, so I think you were right to be cautious about it. I
> don't think it is a wonderful approach in all ways, but I do think that it is superior to approach #1. If we add dedicated
> support to the grammar, it is mostly a one-time effort, and after that, there should not be much need for anyone to be
> concerned about it. If we instead add extra aggregates, then that generates extra work every time someone writes a patch
> that adds a new aggregate to core. I have a difficult time believing that anyone will prefer an approach that involves an
> ongoing maintenance effort of that type over one that doesn't.
> 
> One point that seems to me to be of particular importance is that if we add new aggregates, there is a risk that some
> future aggregate might do that incorrectly, so that the main aggregate works, but the secondary aggregate created for this
> feature does not work. That seems like it would be very frustrating for future code authors so I'd like to avoid the risk as
> much as we can.
Are you concerned about the hassle and potential human errors of manually adding new partial
aggregation functions, rather than the catalog becoming bloated?
The process of creating partial aggregation functions from aggregation functions can be automated,
so I believe this issue can be resolved. However, automating it may increase the size of the patch
even more, so overall, approach#2 might be better.
To implement approach #2, it would be necessary to investigate how much additional code is required.

Sincerely yours,
Yuuki Fujii

--
Yuuki Fujii
Information Technology R&D Center Mitsubishi Electric Corporation

---

From: Robert Haas <[email-masked]>
Date: Wed, 6 Dec 2023 08:25:16 -0500

> aggregation functions, rather than the catalog becoming bloated?

I'm concerned about both.

> To implement approach #2, it would be necessary to investigate how much additional code is required.

Yes. Unfortunately I fear that there is quite a lot of work left to do
here in order to produce a committable feature. To me it seems
necessary to conduct an investigation of approach #2. If the result of
that investigation is that nothing major stands in the way of approach
#2, then I think we should adopt it, which is more work. In addition,
the problems around transmitting serialized bytea blobs between
machines that can't be assumed to fully trust each other will need to
be addressed in some way, which seems like it will require a good deal
of design work, forming some kind of consensus, and then
implementation work to follow. In addition to that there may be some
small problems that need to be solved at a detail level, such as the
HAVING issue. I think the last category won't be too hard to sort out,
but that still leaves two really major areas to address.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: "[email-masked]" <[email-masked]>
Date: Thu, 7 Dec 2023 00:10:58 +0000

Hi Mr.Haas.

> -----Original Message-----
> Sent: Wednesday, December 6, 2023 10:25 PM
> > Are you concerned about the hassle and potential human errors of
> > manually adding new partial aggregation functions, rather than the catalog becoming bloated?
> 
Understood. Thank you for your response.

> > The process of creating partial aggregation functions from aggregation
> > functions can be automated, so I believe this issue can be resolved.
> > However, automating it may increase the size of the patch even more, so overall, approach#2 might be better.
> Yes. Unfortunately I fear that there is quite a lot of work left to do here in order to produce a committable feature. To me it
> seems necessary to conduct an investigation of approach #2. If the result of that investigation is that nothing major
> stands in the way of approach #2, then I think we should adopt it, which is more work. In addition, the problems around
> transmitting serialized bytea blobs between machines that can't be assumed to fully trust each other will need to be
> addressed in some way, which seems like it will require a good deal of design work, forming some kind of consensus, and
> then implementation work to follow. In addition to that there may be some small problems that need to be solved at a
> detail level, such as the HAVING issue. I think the last category won't be too hard to sort out, but that still leaves two really
> major areas to address.
Yes, I agree with you. It is clear that further investigation and discussion are still needed. 
I would be grateful if we can resolve this issue gradually. I would also like to continue the discussion if possible in the future.

Sincerely yours,
Yuuki Fujii

---

From: Robert Haas <[email-masked]>
Date: Thu, 7 Dec 2023 09:56:08 -0500

> I would be grateful if we can resolve this issue gradually. I would also like to continue the discussion if possible in the future.

I think that would be good. Thanks for your work on this. It is a hard problem.

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 7 Dec 2023 16:12:18 -0500

> I think that would be good. Thanks for your work on this. It is a hard problem.

Agreed.  First, Robert is right that this feature is long overdue.  It
might not help many of our existing workloads, but it opens us up to
handling new, larger workloads.

Second, the patch already has a mechanism to check the remote server
version to see if it is the same or newer.   Here is the version check
documentation patch:

	check_partial_aggregate_support (boolean)
	
	If this option is false, <filename>postgres_fdw</filename> always
	uses partial aggregate pushdown by assuming that each built-in
	aggregate function has a partial aggregate function defined on
	the remote server.  If this option is true, local aggregates
	whose partial computation function references itself are assumed
	to exist on the remote server.	If not, during query planning,
	<filename>postgres_fdw</filename> will connect to the remote
	server and retrieve the remote server version.	If the remote
	version is the same or newer, partial aggregate functions will be
	assumed to exist.  If older, <filename>postgres_fdw</filename>
	checks that the remote server has a matching partial aggregate
	function before performing partial aggregate pushdown.	The default
	is <literal>false</literal>.

There is also an extension list that specifies which extension-owned
functions can be pushed down;  from the doc patch:

	To reduce the risk of misexecution of queries, WHERE clauses and
	aggregate expressions are not sent to the remote server unless they
	only use data types, operators, and functions that are built-in
	or belong to an extension that is listed in the foreign server's
	<literal>extensions</literal> option.

Third, we already have a way of creating records for tables:

	SELECT pg_language FROM pg_language;
	                pg_language
	-------------------------------------------
	 (12,internal,10,f,f,0,0,2246,)
	 (13,c,10,f,f,0,0,2247,)
	 (14,sql,10,f,t,0,0,2248,)
	 (13576,plpgsql,10,t,t,13573,13574,13575,)

And we do have record input functionality:

	CREATE TABLE test (x int, language pg_language);
	
	INSERT INTO test SELECT 0, pg_language FROM pg_language;
	
	SELECT * FROM test;
	 x |                 language
	---+-------------------------------------------
	 0 | (12,internal,10,f,f,0,0,2246,)
	 0 | (13,c,10,f,f,0,0,2247,)
	 0 | (14,sql,10,f,t,0,0,2248,)
	 0 | (13576,plpgsql,10,t,t,13573,13574,13575,)
	(4 rows)

However, functions don't have pre-created records, and internal
functions don't see to have an SQL-defined structure, but as I remember
the internal aggregate functions all take the same internal structure,
so I guess we only need one fixed input and one output that would
output/input such records.  Performance might be an issue, but at this
point let's just implement this and measure the overhead since there are
few/any(?) other viable options.

Fourth, going with #2 where we do the pushdown using an SQL keyword also
allows extensions to automatically work, while requiring partial
aggregate functions for every non-partial aggregate will require work
for extensions, and potentially lead to more version mismatch issues.

Finally, I am now concerned that this will not be able to be in PG 17,
which I was hoping for.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

---

From: Robert Haas <[email-masked]>
Date: Mon, 11 Dec 2023 09:24:25 -0500

> version to see if it is the same or newer.   Here is the version check

Right. This feature can certainly be implemented in a
backward-compatible way. I'm not sure that we have as much control
over what does and does not get pushed down as we really want here,
but it's completely possible to do this in a way that doesn't break
other use cases.

> few/any(?) other viable options.

IMHO records will be the easiest approach, but it will be some work to try it.

> for extensions, and potentially lead to more version mismatch issues.

Yeah.

> Finally, I am now concerned that this will not be able to be in PG 17,

Getting it ready to ship by March seems very difficult. I'm not saying
it couldn't happen, but I think more likely it won't.

---

From: vignesh C <[email-masked]>
Date: Sat, 27 Jan 2024 07:27:34 +0530

> I would be grateful if we can resolve this issue gradually. I would also like to continue the discussion if possible in the future.

Thanks for all the efforts on this patch. I have changed the status of
the commitfest entry to "Returned with Feedback" as there is still
some work to get this patch out. Feel free to continue the discussion
and add a new entry when the patch is in a reviewable shape.

Regards,
Vignesh

---

From: "[email-masked]" <[email-masked]>
Date: Thu, 22 Feb 2024 07:20:45 +0000

Hi. Mr.Haas, hackers.

I apologize for the significant delay since my last post.
I have conducted investigations and considerations regarding the remaining tasks as follows.
Would it be possible for you to review them?
In particular, could you please confirm if the approach mentioned in 1. is acceptable? 
If there are no issues with the direction outlined in 1., I plan to make a simple prototype based on this approach.

1. Transmitting state value safely between machines
> the problems around transmitting
> serialized bytea blobs between machines that can't be assumed to fully trust each other will need to be addressed in some
> way, which seems like it will require a good deal of design work, forming some kind of consensus, and then implementation
> work to follow. 
I have considered methods for safely transmitting state values between different machines.
I have taken into account the version policy of PostgreSQL (5 years of support) and the major version release cycle over the past 10 years (1 year), and as a result, I have made the assumption that transmission is allowed only when the difference between the local version and the remote version is 5 or less.
I believe that by adding new components, "export function" and "import function", to the aggregate functions, and further introducing a new SQL keyword to the query syntax of aggregate expressions, we can address this issue.
If the version of the local server is higher than or equal to the version of the remote server, the proposed method can be simplified. The export version mentioned later in (1) would not be necessary. Furthermore, if the version of the local server matches the version of the remote server, the proposed method can be further simplified.
I would appreciate your input on reasonable assumptions regarding the differences in versions between the local server and the remote server.
I will explain the specifications of the export function, import function, the new SQL keyword for aggregate expressions, and the behavior of query processing for partial aggregation separately.
(1) Export Function Specification
This function is another final function for partial aggregate.
This function converts the state value that represents the result of partial aggregation into a format that can be read by the local server. 
This function is called instead of the existing finalfunc during the final stage of aggregation when performing partial aggregation.
The conversion process described above will be referred to as "export".
The argument of an export function is the version of the server that will receive the return value.
Hereafter, this version will be referred to as the export version.
The concept of an export version is necessary to handle cases where the version of the local server is smaller than the version of the remote server.
The return value of the export function is the transformed state value, and its data type is bytea.
For backward compatibility, the developer of the export function must ensure that the export can be performed for major versions up to five versions prior to the major version of PostgreSQL that the export function is being developed for.
For built-in functions, I believe it is necessary to allow for the possibility of not developing the export functionality for specific versions in the future (due to reasons such as development burden) after the export function is developed for a certain version.
To achieve this, for built-in functions, we will add a column to the pg_aggregate catalog that indicates the presence or absence of export functionality for each major version, including the major version being developed and the previous five major versions. This column will be named safety_export_versions and will have a data type of boolean[6].
For user-defined functions, we will refer to the extensions option and add an external server option called safety_export_extensions, which will maintain a list of extensions that include only the aggregate functions that can be exported to the local server version.

(2) Import Function Specification
The import function is a function that performs validity checks on the exported data and converts it into a state value. The process of this conversion is referred to as importing.
The import function is called from postgres_fdw in the local server.
The arguments of the import function are the exported data and the export version.
The return value of the import function is a state value that can be read on the local server.
The import function will terminate with an error if the validity check determines that the exported result cannot be read on the local server.
For backward compatibility, developers of the import function must ensure that it can be imported if the export version is up to five versions prior to their own version.

(3) The new SQL keyword for aggregate expressions
The local server passes the instructions for partial aggregation and the export version to the remote server using SQL keywords. The syntax specification is as follows:
aggregate_function(PARTIAL_AGGREGATE(export_version) expr)

Here, PARTIAL_AGGREGATE is a keyword that indicates partial aggregation, and export_version is a string constant that indicates the export version.

(4) The behavior of query processing for partial aggregation
I will illustrate the flow of query processing using the example query "select aggfunc(c) from t".
In the following explanation, the major version of the remote server will be referred to as remote_version, and the major version of the local server will be referred to as local_version.
STEP1. Checking the feasibility of partial aggregation pushdown on the local server
(i) Retrieving the remote_version
The postgres_fdw connects to the remote server and retrieves the remote_version.
(ii) Checking the versions
The postgres_fdw determines whether the difference between local_version and remote_version is within 5. If the difference is 6 or more, it is determined that partial aggregation pushdown is not possible.
(iii) Checking the import function
The postgres_fdw checks the pg_aggregate catalog to see if there is an import function for aggfunc. If there is none, it is determined that partial aggregation pushdown is not possible.
(iv) Checking the export function
If aggfunc is a built-in function, the postgres_fdw checks the pg_aggregate catalog. It checks if there is a version number export_version that satisfies the conditions local_version >= export_version >= local_version-5 and if there is an export function available for that version. If the version number export_version does not exist, it is determined that partial aggregation pushdown is not possible. This check is only performed if local_version >= remote_version.
If aggfunc is a user-defined function, the postgres_fdw checks if the extension on which aggfunc depends is included in export_safety_extensions. If it is not included, it is determined that partial aggregation pushdown is not possible.

STEP2. Sending a remote query on the local server
The query containing the keyword indicating partial aggregation is sent to the remote server. The remote query for the sample query would be as follows: 
"select aggfunc(PARTIAL_AGGREGATE(export_version) c) from t"

STEP3. Executing the remote query on the remote server
The remote server performs partial aggregation for aggfunc. Instead of calling the final function at the last stage of aggregation, the remote server calls the export function with export_version and generates the return value of the partial aggregation.

STEP4. Receiving the result of the remote query on the local server
The postgres_fdw passes the export_version and the return value of STEP3 to the import function of aggfunc and receives the state value. The postgres_fdw then passes the received state value to the executor of the local server.

2. The approach of adding SQL keywords
> Sent: Tuesday, November 21, 2023 5:52 AM
> I do have a concern about this, though. It adds a lot of bloat. It adds a whole lot of additional entries to pg_aggregate, and
> every new aggregate we add in the future will require a bonus entry for this, and it needs a bunch of new pg_proc entries as
> well. One idea that I've had in the past is to instead introduce syntax that just does this, without requiring a separate
> aggregate definition in each case.
> For example, maybe instead of changing string_agg(whatever) to string_agg_p_text_text(whatever), you can say
> PARTIAL_AGGREGATE
> string_agg(whatever) or string_agg(PARTIAL_AGGREGATE whatever) or something. Then all aggregates could be treated
> in a generic way. I'm not completely sure that's better, but I think it's worth considering.
I have prototyped an approach using SQL keywords for the patch that does not include the functionality of Step 1. Please find the prototype attached as a file.
# I apologize for not including sufficient comments, documentation, and tests in the prototype. Please understand.
Mainly, it seems that we can address this by adding handling for the new SQL keywords in the parser and making modifications to the finalize process for aggregation in the executor.
As pointed out by Mr.Haas, it has been realized that the code can be significantly simplified.
The additional lines of code, excluding documentation and tests, are as follows.
Adding new aggregate functions approach(approach #1): 1,069
Adding new SQL keyword approach(approach #2): 318
As mentioned in 1., I plan to modify the patch by adding SQL keywords in the future.

3. Fixing the behavior when the HAVING clause is present
> afterward. That is, partial aggregation is a barrier to pushing down HAVING clause itself, but it doesn't preclude pushing
I have made modifications in the attached patch to ensure that when the HAVING clause is present, the HAVING clause is executed locally while the partial aggregations are pushed down.

Sincerely yours,
Yuuki Fujii

---

From: Alexander Pyhalov <[email-masked]>
Date: Wed, 28 Feb 2024 16:43:07 +0300

Hi.

[email-masked] писал(а) 2024-02-22 10:20:
> If there are no issues with the direction outlined in 1., I plan to 
> make a simple prototype based on this approach.
> 
>> serialized bytea blobs between machines that can't be assumed to fully 
>> trust each other will need to be addressed in some
>> way, which seems like it will require a good deal of design work, 
>> forming some kind of consensus, and then implementation
>> work to follow.
> I have taken into account the version policy of PostgreSQL (5 years of 
> support) and the major version release cycle over the past 10 years (1 
> year), and as a result, I have made the assumption that transmission is 
> allowed only when the difference between the local version and the 
> remote version is 5 or less.
> I believe that by adding new components, "export function" and "import 
> function", to the aggregate functions, and further introducing a new 
> SQL keyword to the query syntax of aggregate expressions, we can 
> address this issue.
> If the version of the local server is higher than or equal to the 
> version of the remote server, the proposed method can be simplified. 
> The export version mentioned later in (1) would not be necessary. 
> Furthermore, if the version of the local server matches the version of 
> the remote server, the proposed method can be further simplified.
> I would appreciate your input on reasonable assumptions regarding the 
> differences in versions between the local server and the remote server.
> I will explain the specifications of the export function, import 
> function, the new SQL keyword for aggregate expressions, and the 
> behavior of query processing for partial aggregation separately.
> This function converts the state value that represents the result of 
> partial aggregation into a format that can be read by the local server.
> This function is called instead of the existing finalfunc during the 
> final stage of aggregation when performing partial aggregation.
> The concept of an export version is necessary to handle cases where the 
> version of the local server is smaller than the version of the remote 
> server.
> For backward compatibility, the developer of the export function must 
> ensure that the export can be performed for major versions up to five 
> versions prior to the major version of PostgreSQL that the export 
> function is being developed for.
> For built-in functions, I believe it is necessary to allow for the 
> possibility of not developing the export functionality for specific 
> versions in the future (due to reasons such as development burden) 
> after the export function is developed for a certain version.
> To achieve this, for built-in functions, we will add a column to the 
> pg_aggregate catalog that indicates the presence or absence of export 
> functionality for each major version, including the major version being 
> developed and the previous five major versions. This column will be 
> named safety_export_versions and will have a data type of boolean[6].
> For user-defined functions, we will refer to the extensions option and 
> add an external server option called safety_export_extensions, which 
> will maintain a list of extensions that include only the aggregate 
> functions that can be exported to the local server version.
> ...

I honestly think that achieving cross-version compatibility in this way 
puts a significant burden on developers. Can we instead always use the 
more or less universal export and import function to fix possible issues 
with binary representations on different architectures and just refuse 
to push down partial aggregates on server version mismatch? At least at 
the first step?

>> I don't see it that way. What we would push to the foreign server 
>> would be something like SELECT count(a) FROM t. Then,
>> after we get the results back and combine the various partial counts 
>> locally, we would locally evaluate the HAVING clause
>> afterward. That is, partial aggregation is a barrier to pushing down 
>> HAVING clause itself, but it doesn't preclude pushing
>> down the aggregation.
> I have made modifications in the attached patch to ensure that when the 
> HAVING clause is present, the HAVING clause is executed locally while 
> the partial aggregations are pushed down.
> 
> 

Sorry, I don't see how it works. When we have partial aggregates and 
having clause, foreign_grouping_ok() returns false and 
add_foreign_grouping_paths() adds no paths.
I'm not saying it's necessary to fix this in the first patch version.

explain verbose select sum(a) from pagg_tab having sum(a)>10;
                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------
  Finalize Aggregate  (cost=2282.49..2282.50 rows=1 width=8)
    Output: sum(pagg_tab.a)
    Filter: (sum(pagg_tab.a) > 10)
    ->  Append  (cost=760.81..2282.48 rows=3 width=8)
          ->  Partial Aggregate  (cost=760.81..760.82 rows=1 width=8)
                Output: PARTIAL sum(pagg_tab.a)
                ->  Foreign Scan on public.fpagg_tab_p1 pagg_tab  
(cost=100.00..753.50 rows=2925 width=4)
                      Output: pagg_tab.a
                      Remote SQL: SELECT a FROM public.pagg_tab_p1
          ->  Partial Aggregate  (cost=760.81..760.82 rows=1 width=8)
                Output: PARTIAL sum(pagg_tab_1.a)
                ->  Foreign Scan on public.fpagg_tab_p2 pagg_tab_1  
(cost=100.00..753.50 rows=2925 width=4)
                      Output: pagg_tab_1.a
                      Remote SQL: SELECT a FROM public.pagg_tab_p2
          ->  Partial Aggregate  (cost=760.81..760.82 rows=1 width=8)
                Output: PARTIAL sum(pagg_tab_2.a)
                ->  Foreign Scan on public.fpagg_tab_p3 pagg_tab_2  
(cost=100.00..753.50 rows=2925 width=4)
                      Output: pagg_tab_2.a
                      Remote SQL: SELECT a FROM public.pagg_tab_p3

Also I have some minor notices on the code.

contrib/postgres_fdw/deparse.c: comment before appendFunctionName() has 
gone, this seems to be wrong.

In finalize_aggregate()

1079         /*
1080          * Apply the agg's finalfn if one is provided, else return 
transValue.
1081          */

Comment should be updated to note behavior for agg_partial aggregates.

1129         else if (peragg->aggref->agg_partial
1130                         && (peragg->aggref->aggtranstype == 
INTERNALOID)
1131                         && OidIsValid(peragg->serialfn_oid))

In this if branch, should we check just for peragg->aggref->agg_partial 
and peragg->aggref->aggtranstype == INTERNALOID? It seems that if 
peragg->aggref->aggtranstype == INTERNALOID and there's no
serialfn_oid, it's likely an error (and one should be generated).

Overall patch seems nicer. Will look at it more this week.
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

---

From: "[email-masked]" <[email-masked]>
Date: Sat, 16 Mar 2024 02:28:50 +0000

Hi. Mr.Pyhalov.

> From: Alexander Pyhalov <[email-masked]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> >> Sent: Wednesday, December 6, 2023 10:25 PM the problems around
> >> transmitting serialized bytea blobs between machines that can't be
> >> assumed to fully trust each other will need to be addressed in some
> >> forming some kind of consensus, and then implementation work to
> >> follow.
> > year), and as a result, I have made the assumption that transmission
> > is allowed only when the difference between the local version and the
 ...
> 
> I honestly think that achieving cross-version compatibility in this way puts a significant burden on developers. Can we
> instead always use the more or less universal export and import function to fix possible issues with binary representations
> on different architectures and just refuse to push down partial aggregates on server version mismatch? At least at the first
> step?
Thank you for your comment. I agree with your point that the proposed method would impose a significant burden on developers. In order to ensure cross-version compatibility, it is necessary to impose constraints on the format of the state values exchanged between servers, which would indeed burden developers. As you mentioned, I think that it is realistic to allow partial aggregation pushdown only when coordinating between the same versions in the first step.

> From: Alexander Pyhalov <[email-masked]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> >> > SELECT count(a) FROM t HAVING count(a) > 10 - we can't push it down
> >> > to foreign server as HAVING needs full aggregate result, but
> >> > foreign server don't know it.
> >>
> >> would be something like SELECT count(a) FROM t. Then, after we get
> >> the results back and combine the various partial counts locally, we
> >> would locally evaluate the HAVING clause afterward. That is, partial
> >> aggregation is a barrier to pushing down HAVING clause itself, but it
> >> doesn't preclude pushing down the aggregation.
> > I have made modifications in the attached patch to ensure that when
> > the HAVING clause is present, the HAVING clause is executed locally
> > while the partial aggregations are pushed down.
> >
> >
> 
> Sorry, I don't see how it works. When we have partial aggregates and having clause, foreign_grouping_ok() returns false and
Our sincere apologies. I had attached an older version before this modification.

> From: Alexander Pyhalov <[email-masked]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> contrib/postgres_fdw/deparse.c: comment before appendFunctionName() has gone, this seems to be wrong.
Fixed.

> From: Alexander Pyhalov <[email-masked]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> In finalize_aggregate()
> 
> 1079         /*
Fixed.

> From: Alexander Pyhalov <[email-masked]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> In this if branch, should we check just for peragg->aggref->agg_partial and peragg->aggref->aggtranstype ==
> INTERNALOID? It seems that if
As you pointed out, I have made modifications to the source code so that it terminates with an error if serialfn is invalid.

Sincerely yours,
Yuuki Fujii

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 19 Mar 2024 16:29:42 -0400

On Sat, Mar 16, 2024 at 02:28:50AM +0000, [email-masked] wrote:
> Hi. Mr.Pyhalov.
>
> > From: Alexander Pyhalov <[email-masked]> Sent: Wednesday,
> > February 28, 2024 10:43 PM
> > >> From: Robert Haas <[email-masked]> Sent: Wednesday,
> > >> December 6, 2023 10:25 PM the problems around transmitting
> > >> serialized bytea blobs between machines that can't be assumed to
> > >> fully trust each other will need to be addressed in some way,
> > >> which seems like it will require a good deal of design work,
> > > I have considered methods for safely transmitting state values
> > > between different machines.  I have taken into account the version
> > > policy of PostgreSQL (5 years of support) and the major version
> > > release cycle over the past 10 years (1 year), and as a result, I
> > > have made the assumption that transmission is allowed only when
> > > the difference between the local version and the remote version
> > > is 5 or less.  I believe that by adding new components, "export
> > > function" and "import function", to the aggregate functions, and
> > > further introducing a new SQL keyword to the query syntax of
> > > aggregate expressions, we can address this issue.
> >
> > I honestly think that achieving cross-version compatibility in
> > this way puts a significant burden on developers. Can we instead
> > always use the more or less universal export and import function
> > to fix possible issues with binary representations on different
> > architectures and just refuse to push down partial aggregates on
> > server version mismatch? At least at the first step?
>
> Thank you for your comment. I agree with your point that the proposed
> method would impose a significant burden on developers. In order
> to ensure cross-version compatibility, it is necessary to impose
> constraints on the format of the state values exchanged between
> servers, which would indeed burden developers. As you mentioned, I
> think that it is realistic to allow partial aggregation pushdown only
> when coordinating between the same versions in the first step.

The current patch has:

      if ((OidIsValid(aggform->aggfinalfn) ||
              (aggform->aggtranstype == INTERNALOID)) &&
              fpinfo->check_partial_aggregate_support)
      {
              if (fpinfo->remoteversion == 0)
              {
                      PGconn     *conn = GetConnection(fpinfo->user, false, NULL);

                      fpinfo->remoteversion = PQserverVersion(conn);
              }

              if (fpinfo->remoteversion < PG_VERSION_NUM)
                      partial_agg_ok = false;
      }

It uses check_partial_aggregate_support, which defaults to false,
meaning partial aggregates will be pushed down with no version check by
default.  If set to true, pushdown will happen if the remote server is
the same version or newer, which seems acceptable to me.

FYI, the patch is much smaller now.  :-)

---

From: Alexander Pyhalov <[email-masked]>
Date: Mon, 25 Mar 2024 10:00:51 +0300

Hi.

In foreign_grouping_ok() having qual is added to local conds here:

6635                         if (is_foreign_expr(root, grouped_rel, 
expr) && !partial)
6636                                 fpinfo->remote_conds = 
lappend(fpinfo->remote_conds, rinfo);
6637                         else
6638                                 fpinfo->local_conds = 
lappend(fpinfo->local_conds, rinfo);
6639                 }
6640         }

This is incorrect. If you look at plan for query in postgres_fdw.sql

-- Partial aggregates are safe to push down when there is a HAVING 
clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING 
sum(a) < 700 ORDER BY 1;
                                                                      
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
  Finalize GroupAggregate
    Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
    Group Key: pagg_tab.b
    Filter: (sum(pagg_tab.a) < 700)
    ->  Sort
          Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL 
max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
          Sort Key: pagg_tab.b
          ->  Append
                ->  Foreign Scan
                      Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), 
(PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
                      Filter: ((sum(pagg_tab.a)) < 700)
                      Relations: Aggregate on (public.fpagg_tab_p1 
pagg_tab)
                      Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), 
max(a), count(*), sum(a), sum(a) FROM public.pagg_tab_p1 GROUP BY 1
                ->  Foreign Scan
                      Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), 
(PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*)), (PARTIAL 
sum(pagg_tab_1.a))
                      Filter: ((sum(pagg_tab_1.a)) < 700)
                      Relations: Aggregate on (public.fpagg_tab_p2 
pagg_tab_1)
                      Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), 
max(a), count(*), sum(a), sum(a) FROM public.pagg_tab_p2 GROUP BY 1
                ->  Foreign Scan
                      Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), 
(PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*)), (PARTIAL 
sum(pagg_tab_2.a))
                      Filter: ((sum(pagg_tab_2.a)) < 700)
                      Relations: Aggregate on (public.fpagg_tab_p3 
pagg_tab_2)
                      Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), 
max(a), count(*), sum(a), sum(a) FROM public.pagg_tab_p3 GROUP BY 1

You can see that filter is applied before append. The result is correct 
only by chance, as sum in every partition is actually < 700. If you 
lower this bound, let's say, to 200, you'll start getting wrong results 
as data is filtered prior to aggregation.

It seems, however, that in partial case you should just avoid pulling 
conditions from having qual at all, all filters will be applied on upper 
level. Something like

diff --git a/contrib/postgres_fdw/postgres_fdw.c 
b/contrib/postgres_fdw/postgres_fdw.c
index 42eb17ae7c0..54918b9f1a4 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -6610,7 +6610,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo 
*grouped_rel,
          * Classify the pushable and non-pushable HAVING clauses and 
save them in
          * remote_conds and local_conds of the grouped rel's fpinfo.
          */
-       if (extra->havingQual)
+       if (extra->havingQual && !partial)
         {
                 foreach(lc, (List *) extra->havingQual)
                 {
@@ -6632,7 +6632,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo 
*grouped_rel,
                                                                          
  grouped_rel->relids,
                                                                          
  NULL,
                                                                          
  NULL);
-                       if (is_foreign_expr(root, grouped_rel, expr) && 
!partial)
+                       if (is_foreign_expr(root, grouped_rel, expr))
                                 fpinfo->remote_conds = 
lappend(fpinfo->remote_conds, rinfo);
                         else
                                 fpinfo->local_conds = 
lappend(fpinfo->local_conds, rinfo);

>> Comment should be updated to note behavior for agg_partial aggregates.

Comment in nodeAgg.c seems to be strange:

1079         /*
1080          * If the agg's finalfn is provided and PARTIAL_AGGREGATE 
keyword is
1081          * not specified, apply the agg's finalfn.
1082          * If PARTIAL_AGGREGATE keyword is specified and the 
transValue type
1083          * is internal, apply the agg's serialfn. In this case, if 
the agg's
1084          * serialfn must not be invalid. Otherwise return 
transValue.
1085          */

Likely, you mean:

... In this case the agg'ss serialfn must not be invalid...

Lower, in the same file, please, correct error message:

1136                 if(!OidIsValid(peragg->serialfn_oid))
1137                         elog(ERROR, "serialfunc is note provided 
for partial aggregate");

it should be "serialfunc is not provided for partial aggregate"

Also something is wrong with the following test :

  SELECT /* aggregate <> partial aggregate */
      array_agg(c_int4array), array_agg(b),
      avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
      avg(b::float4), avg(b::float8),
      corr(b::float8, (b * b)::float8),
      covar_pop(b::float8, (b * b)::float8),
      covar_samp(b::float8, (b * b)::float8),
      regr_avgx((2 * b)::float8, b::float8),
.....

Its results have changed since last patch. Do they depend on daylight 
saving time?

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

---

From: Alexander Pyhalov <[email-masked]>
Date: Tue, 26 Mar 2024 14:33:21 +0300

>> Our sincere apologies. I had attached an older version before this 

Hi.
Found one more problem. You can fire partial aggregate over partitioned 
table, but convert_combining_aggrefs() will make non-partial copy, which 
leads to
'variable not found in subplan target list' error.

Attaching fixed version. Also I've added changes, related to HAVING 
processing.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

From 75efe7d703bcdc1f5b0cf7ca8244213b58730bd5 Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email-masked]>
Date: Mon, 25 Mar 2024 09:05:32 +0300
Subject: [PATCH] Partial aggregates push down SQL keyword v3

---
 contrib/postgres_fdw/deparse.c                |  83 ++-
 .../postgres_fdw/expected/postgres_fdw.out    | 670 ++++++++++++++++--
 contrib/postgres_fdw/option.c                 |   4 +-
 contrib/postgres_fdw/postgres_fdw.c           |  41 +-
 contrib/postgres_fdw/postgres_fdw.h           |  10 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     | 160 ++++-
 src/backend/executor/nodeAgg.c                |  60 +-
 src/backend/nodes/makefuncs.c                 |   1 +
 src/backend/optimizer/plan/planner.c          | 133 +++-
 src/backend/optimizer/plan/setrefs.c          |   1 +
 src/backend/optimizer/prep/prepagg.c          |   1 +
 src/backend/parser/gram.y                     |  29 +-
 src/backend/parser/parse_agg.c                |  24 +-
 src/backend/parser/parse_clause.c             |   1 +
 src/backend/parser/parse_expr.c               |   3 +-
 src/backend/parser/parse_func.c               |  24 +-
 src/backend/utils/adt/ruleutils.c             |   3 +-
 src/include/executor/nodeAgg.h                |   9 +
 src/include/nodes/parsenodes.h                |   1 +
 src/include/nodes/pathnodes.h                 |   5 +
 src/include/nodes/primnodes.h                 |   3 +
 src/include/parser/kwlist.h                   |   1 +
 src/include/parser/parse_agg.h                |   2 +-
 23 files changed, 1147 insertions(+), 122 deletions(-)

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 8fc66fa11c7..9f12e8243c7 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -206,7 +206,7 @@ static bool is_subquery_var(Var *node, RelOptInfo *foreignrel,
 							int *relno, int *colno);
 static void get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
 										  int *relno, int *colno);
-
+static bool partial_agg_ok(Aggref *agg, PgFdwRelationInfo *fpinfo);
 
 /*
  * Examine each qual clause in input_conds, and classify them into two groups,
@@ -911,8 +911,9 @@ foreign_expr_walker(Node *node,
 				if (!IS_UPPER_REL(glob_cxt->foreignrel))
 					return false;
 
-				/* Only non-split aggregates are pushable. */
-				if (agg->aggsplit != AGGSPLIT_SIMPLE)
+				if (agg->aggsplit != AGGSPLIT_SIMPLE && agg->aggsplit != AGGSPLIT_INITIAL_SERIAL)
+					return false;
+				if (agg->aggsplit == AGGSPLIT_INITIAL_SERIAL && !partial_agg_ok(agg, fpinfo))
 					return false;
 
 				/* As usual, it must be shippable. */
@@ -3659,18 +3660,35 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
 	StringInfo	buf = context->buf;
 	bool		use_variadic;
 
-	/* Only basic, non-split aggregation accepted. */
-	Assert(node->aggsplit == AGGSPLIT_SIMPLE);
+
+	Assert((node->aggsplit == AGGSPLIT_SIMPLE) ||
+		   (node->aggsplit == AGGSPLIT_INITIAL_SERIAL));
 
 	/* Check if need to print VARIADIC (cf. ruleutils.c) */
 	use_variadic = node->aggvariadic;
 
 	/* Find aggregate name from aggfnoid which is a pg_proc entry */
 	appendFunctionName(node->aggfnoid, context);
+
 	appendStringInfoChar(buf, '(');
 
-	/* Add DISTINCT */
-	appendStringInfoString(buf, (node->aggdistinct != NIL) ? "DISTINCT " : "");
+	/* Add DISTINCT or PARTIAL_AGGREGATE */
+	if (node->aggdistinct != NIL) {
+		appendStringInfoString(buf, "DISTINCT ");
+	} else if (node->aggsplit == AGGSPLIT_INITIAL_SERIAL) {
+		HeapTuple	aggtup;
+		Form_pg_aggregate aggform;
+
+		aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(node->aggfnoid));
+		if (!HeapTupleIsValid(aggtup))
+			elog(ERROR, "cache lookup failed for aggregate %u", node->aggfnoid);
+		aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+
+		if ((aggform->aggtranstype == INTERNALOID) || OidIsValid(aggform->aggfinalfn))
+			appendStringInfoString(buf, "PARTIAL_AGGREGATE ");
+
+		ReleaseSysCache(aggtup);
+	}
 
 	if (AGGKIND_IS_ORDERED_SET(node->aggkind))
 	{
@@ -3865,6 +3883,7 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
 	Query	   *query = context->root->parse;
 	ListCell   *lc;
 	bool		first = true;
+	PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
 
 	/* Nothing to be done, if there's no GROUP BY clause in the query. */
 	if (!query->groupClause)
@@ -3885,7 +3904,7 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
 	 * to empty, and in any case the redundancy situation on the remote might
 	 * be different than what we think here.
 	 */
-	foreach(lc, query->groupClause)
+	foreach(lc, fpinfo->group_clause)
 	{
 		SortGroupClause *grp = (SortGroupClause *) lfirst(lc);
 
@@ -4189,3 +4208,51 @@ get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
 	/* Shouldn't get here */
 	elog(ERROR, "unexpected expression in subquery output");
 }
+
+/*
+ * ----------
+ * Check that partial aggregate "agg" is safe to push down.
+ *
+ * It is pushdown-safe when all of the following conditions are true:
+ *
+ *    * agg is an AGGKIND_NORMAL aggregate which contains no DISTINCT or
+ *    ORDER BY clauses
+ *    * remote server can return partial aggregate results
+ * ----------
+ */
+static bool
+partial_agg_ok(Aggref *agg, PgFdwRelationInfo *fpinfo)
+{
+	HeapTuple	aggtup;
+	Form_pg_aggregate aggform;
+	bool		partial_agg_ok = true;
+
+	Assert(agg->aggsplit == AGGSPLIT_INITIAL_SERIAL);
+
+	/* We don't support complex partial aggregates */
+	if (agg->aggdistinct || agg->aggkind != AGGKIND_NORMAL || agg->aggorder != NIL)
+		return false;
+
+	aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
+	if (!HeapTupleIsValid(aggtup))
+		elog(ERROR, "cache lookup failed for aggregate %u", agg->aggfnoid);
+	aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+
+	if ((OidIsValid(aggform->aggfinalfn) ||
+		(aggform->aggtranstype == INTERNALOID)) &&
+		fpinfo->check_partial_aggregate_support)
+	{
+		if (fpinfo->remoteversion == 0)
+		{
+			PGconn	   *conn = GetConnection(fpinfo->user, false, NULL);
+
+			fpinfo->remoteversion = PQserverVersion(conn);
+		}
+
+		if (fpinfo->remoteversion < PG_VERSION_NUM)
+			partial_agg_ok = false;
+	}
+
+	ReleaseSysCache(aggtup);
+	return partial_agg_ok;
+}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c355e8f3f7d..cd04ba3ec01 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10017,36 +10017,56 @@ RESET enable_partitionwise_join;
 -- ===================================================================
 -- test partitionwise aggregates
 -- ===================================================================
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+					c_int4array _int4, c_interval interval,
+					c_money money, c_1c text, c_1b bytea,
+					c_bit bit(2), c_1or3int2 int2,
+					c_1or3int4 int4, c_1or3int8 int8,
+					c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+					c_tid tid, c_int4range int4range,
+					c_int4multirange int4multirange,
+					c_time time, c_timetz timetz,
+					c_timestamp timestamp, c_timestamptz timestamptz,
+					c_xid8 xid8)
+	PARTITION BY RANGE(a);
 CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
 CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
 CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
 -- Create foreign partitions
 CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
 CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2');
 CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');
 ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
 ANALYZE fpagg_tab_p1;
 ANALYZE fpagg_tab_p2;
 ANALYZE fpagg_tab_p3;
+SET extra_float_digits = 0;
 -- When GROUP BY clause matches with PARTITION KEY.
 -- Plan with partitionwise aggregates is disabled
 SET enable_partitionwise_aggregate TO false;
 EXPLAIN (COSTS OFF)
 SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-                     QUERY PLAN                      
------------------------------------------------------
- GroupAggregate
-   Group Key: pagg_tab.a
-   Filter: (avg(pagg_tab.b) < '22'::numeric)
-   ->  Append
-         ->  Foreign Scan on fpagg_tab_p1 pagg_tab_1
-         ->  Foreign Scan on fpagg_tab_p2 pagg_tab_2
-         ->  Foreign Scan on fpagg_tab_p3 pagg_tab_3
-(7 rows)
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Sort
+   Sort Key: pagg_tab.a
+   ->  HashAggregate
+         Group Key: pagg_tab.a
+         Filter: (avg(pagg_tab.b) < '22'::numeric)
+         ->  Append
+               ->  Foreign Scan on fpagg_tab_p1 pagg_tab_1
+               ->  Foreign Scan on fpagg_tab_p2 pagg_tab_2
+               ->  Foreign Scan on fpagg_tab_p3 pagg_tab_3
+(9 rows)
 
 -- Plan with partitionwise aggregates is enabled
 SET enable_partitionwise_aggregate TO true;
@@ -10076,36 +10096,66 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O
  21 | 2100 |   1 |   100
 (6 rows)
 
+-- Check partial aggregate over partitioned table
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+                                                 QUERY PLAN                                                  
+-------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+   Output: avg(PARTIAL_AGGREGATE pagg_tab.a), avg(pagg_tab.a)
+   ->  Append
+         ->  Foreign Scan
+               Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab.a)), (PARTIAL avg(pagg_tab.a))
+               Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p1
+         ->  Foreign Scan
+               Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_1.a)), (PARTIAL avg(pagg_tab_1.a))
+               Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p2
+         ->  Foreign Scan
+               Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_2.a)), (PARTIAL avg(pagg_tab_2.a))
+               Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p3
+(15 rows)
+
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+     avg      |         avg         
+--------------+---------------------
+ {3000,43500} | 14.5000000000000000
+(1 row)
+
 -- Check with whole-row reference
 -- Should have all the columns in the target list for the given relation
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-                                         QUERY PLAN                                         
---------------------------------------------------------------------------------------------
- Merge Append
+                                                                                                                                           QUERY PLAN                                                                                                                                            
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: t1.a, (count(((t1.*)::pagg_tab)))
    Sort Key: t1.a
-   ->  GroupAggregate
-         Output: t1.a, count(((t1.*)::pagg_tab))
-         Group Key: t1.a
-         Filter: (avg(t1.b) < '22'::numeric)
-         ->  Foreign Scan on public.fpagg_tab_p1 t1
-               Output: t1.a, t1.*, t1.b
-               Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 ORDER BY a ASC NULLS LAST
-   ->  GroupAggregate
-         Output: t1_1.a, count(((t1_1.*)::pagg_tab))
-         Group Key: t1_1.a
-         Filter: (avg(t1_1.b) < '22'::numeric)
-         ->  Foreign Scan on public.fpagg_tab_p2 t1_1
-               Output: t1_1.a, t1_1.*, t1_1.b
-               Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 ORDER BY a ASC NULLS LAST
-   ->  GroupAggregate
-         Output: t1_2.a, count(((t1_2.*)::pagg_tab))
-         Group Key: t1_2.a
-         Filter: (avg(t1_2.b) < '22'::numeric)
-         ->  Foreign Scan on public.fpagg_tab_p3 t1_2
-               Output: t1_2.a, t1_2.*, t1_2.b
-               Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 ORDER BY a ASC NULLS LAST
-(23 rows)
+   ->  Append
+         ->  HashAggregate
+               Output: t1.a, count(((t1.*)::pagg_tab))
+               Group Key: t1.a
+               Filter: (avg(t1.b) < '22'::numeric)
+               ->  Foreign Scan on public.fpagg_tab_p1 t1
+                     Output: t1.a, t1.*, t1.b
+                     Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p1
+         ->  HashAggregate
+               Output: t1_1.a, count(((t1_1.*)::pagg_tab))
+               Group Key: t1_1.a
+               Filter: (avg(t1_1.b) < '22'::numeric)
+               ->  Foreign Scan on public.fpagg_tab_p2 t1_1
+                     Output: t1_1.a, t1_1.*, t1_1.b
+                     Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p2
+         ->  HashAggregate
+               Output: t1_2.a, count(((t1_2.*)::pagg_tab))
+               Group Key: t1_2.a
+               Filter: (avg(t1_2.b) < '22'::numeric)
+               ->  Foreign Scan on public.fpagg_tab_p3 t1_2
+                     Output: t1_2.a, t1_2.*, t1_2.b
+                     Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p3
+(25 rows)
 
 SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
  a  | count 
@@ -10118,27 +10168,535 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
  21 |   100
 (6 rows)
 
--- When GROUP BY clause does not match with PARTITION KEY.
-EXPLAIN (COSTS OFF)
+-- Partial aggregates are safe to push down when there is a HAVING clause
+EXPLAIN (VERBOSE, COSTS OFF)
 SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
-                        QUERY PLAN                         
------------------------------------------------------------
- Finalize GroupAggregate
-   Group Key: pagg_tab.b
-   Filter: (sum(pagg_tab.a) < 700)
+                                                                     QUERY PLAN                                                                      
+-----------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+   Sort Key: pagg_tab.b
+   ->  Finalize HashAggregate
+         Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+         Group Key: pagg_tab.b
+         Filter: (sum(pagg_tab.a) < 700)
+         ->  Append
+               ->  Foreign Scan
+                     Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
+                     Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p1 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab_1.a))
+                     Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p2 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab_2.a))
+                     Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p3 GROUP BY 1
+(20 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
+ b  |         avg         | max | count 
+----+---------------------+-----+-------
+  0 | 10.0000000000000000 |  20 |    60
+  1 | 11.0000000000000000 |  21 |    60
+ 10 | 10.0000000000000000 |  20 |    60
+ 11 | 11.0000000000000000 |  21 |    60
+ 20 | 10.0000000000000000 |  20 |    60
+ 21 | 11.0000000000000000 |  21 |    60
+ 30 | 10.0000000000000000 |  20 |    60
+ 31 | 11.0000000000000000 |  21 |    60
+ 40 | 10.0000000000000000 |  20 |    60
+ 41 | 11.0000000000000000 |  21 |    60
+(10 rows)
+
+-- Partial aggregates are safe to push down without having clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+                                                       QUERY PLAN                                                        
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+   Sort Key: pagg_tab.b
+   ->  Finalize HashAggregate
+         Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+         Group Key: pagg_tab.b
+         ->  Append
+               ->  Foreign Scan
+                     Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b  |         avg         | max | count 
+----+---------------------+-----+-------
+  0 | 10.0000000000000000 |  20 |    60
+  1 | 11.0000000000000000 |  21 |    60
+  2 | 12.0000000000000000 |  22 |    60
+  3 | 13.0000000000000000 |  23 |    60
+  4 | 14.0000000000000000 |  24 |    60
+  5 | 15.0000000000000000 |  25 |    60
+  6 | 16.0000000000000000 |  26 |    60
+  7 | 17.0000000000000000 |  27 |    60
+  8 | 18.0000000000000000 |  28 |    60
+  9 | 19.0000000000000000 |  29 |    60
+ 10 | 10.0000000000000000 |  20 |    60
+ 11 | 11.0000000000000000 |  21 |    60
+ 12 | 12.0000000000000000 |  22 |    60
+ 13 | 13.0000000000000000 |  23 |    60
+ 14 | 14.0000000000000000 |  24 |    60
+ 15 | 15.0000000000000000 |  25 |    60
+ 16 | 16.0000000000000000 |  26 |    60
+ 17 | 17.0000000000000000 |  27 |    60
+ 18 | 18.0000000000000000 |  28 |    60
+ 19 | 19.0000000000000000 |  29 |    60
+ 20 | 10.0000000000000000 |  20 |    60
+ 21 | 11.0000000000000000 |  21 |    60
+ 22 | 12.0000000000000000 |  22 |    60
+ 23 | 13.0000000000000000 |  23 |    60
+ 24 | 14.0000000000000000 |  24 |    60
+ 25 | 15.0000000000000000 |  25 |    60
+ 26 | 16.0000000000000000 |  26 |    60
+ 27 | 17.0000000000000000 |  27 |    60
+ 28 | 18.0000000000000000 |  28 |    60
+ 29 | 19.0000000000000000 |  29 |    60
+ 30 | 10.0000000000000000 |  20 |    60
+ 31 | 11.0000000000000000 |  21 |    60
+ 32 | 12.0000000000000000 |  22 |    60
+ 33 | 13.0000000000000000 |  23 |    60
+ 34 | 14.0000000000000000 |  24 |    60
+ 35 | 15.0000000000000000 |  25 |    60
+ 36 | 16.0000000000000000 |  26 |    60
+ 37 | 17.0000000000000000 |  27 |    60
+ 38 | 18.0000000000000000 |  28 |    60
+ 39 | 19.0000000000000000 |  29 |    60
+ 40 | 10.0000000000000000 |  20 |    60
+ 41 | 11.0000000000000000 |  21 |    60
+ 42 | 12.0000000000000000 |  22 |    60
+ 43 | 13.0000000000000000 |  23 |    60
+ 44 | 14.0000000000000000 |  24 |    60
+ 45 | 15.0000000000000000 |  25 |    60
+ 46 | 16.0000000000000000 |  26 |    60
+ 47 | 17.0000000000000000 |  27 |    60
+ 48 | 18.0000000000000000 |  28 |    60
+ 49 | 19.0000000000000000 |  29 |    60
+(50 rows)
+
+-- Partial aggregates are safe to push down even if we need both variable and variable-based expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+                                                                  QUERY PLAN                                                                  
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*)), ((((pagg_tab.b / 2)))::numeric), ((pagg_tab.b / 2))
+   Sort Key: ((((pagg_tab.b / 2)))::numeric)
+   ->  Finalize HashAggregate
+         Output: avg(pagg_tab.a), max(pagg_tab.a), count(*), (((pagg_tab.b / 2)))::numeric, ((pagg_tab.b / 2))
+         Group Key: ((pagg_tab.b / 2))
+         ->  Append
+               ->  Foreign Scan
+                     Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), pagg_tab.b
+                     Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+                     Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1, 2
+               ->  Foreign Scan
+                     Output: ((pagg_tab_1.b / 2)), (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*)), pagg_tab_1.b
+                     Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+                     Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1, 2
+               ->  Foreign Scan
+                     Output: ((pagg_tab_2.b / 2)), (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*)), pagg_tab_2.b
+                     Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+                     Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1, 2
+(19 rows)
+
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+         avg         | max | count | numeric 
+---------------------+-----+-------+---------
+ 10.5000000000000000 |  21 |   120 |       0
+ 12.5000000000000000 |  23 |   120 |       1
+ 14.5000000000000000 |  25 |   120 |       2
+ 16.5000000000000000 |  27 |   120 |       3
+ 18.5000000000000000 |  29 |   120 |       4
+ 10.5000000000000000 |  21 |   120 |       5
+ 12.5000000000000000 |  23 |   120 |       6
+ 14.5000000000000000 |  25 |   120 |       7
+ 16.5000000000000000 |  27 |   120 |       8
+ 18.5000000000000000 |  29 |   120 |       9
+ 10.5000000000000000 |  21 |   120 |      10
+ 12.5000000000000000 |  23 |   120 |      11
+ 14.5000000000000000 |  25 |   120 |      12
+ 16.5000000000000000 |  27 |   120 |      13
+ 18.5000000000000000 |  29 |   120 |      14
+ 10.5000000000000000 |  21 |   120 |      15
+ 12.5000000000000000 |  23 |   120 |      16
+ 14.5000000000000000 |  25 |   120 |      17
+ 16.5000000000000000 |  27 |   120 |      18
+ 18.5000000000000000 |  29 |   120 |      19
+ 10.5000000000000000 |  21 |   120 |      20
+ 12.5000000000000000 |  23 |   120 |      21
+ 14.5000000000000000 |  25 |   120 |      22
+ 16.5000000000000000 |  27 |   120 |      23
+ 18.5000000000000000 |  29 |   120 |      24
+(25 rows)
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT /* aggregate <> partial aggregate */
+    array_agg(c_int4array), array_agg(b),
+    avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval), avg(b::float4), avg(b::float8), avg(b::numeric),
+    corr(b::float8, (b * b)::float8),
+    covar_pop(b::float8, (b * b)::float8),
+    covar_samp(b::float8, (b * b)::float8),
+    regr_avgx((2 * b)::float8, b::float8),
+    regr_avgy((2 * b)::float8, b::float8),
+    regr_intercept((2 * b + 3)::float8, b::float8),
+    regr_r2((b * b)::float8, b::float8),
+    regr_slope((2 * b + 3)::float8, b::float8),
+    regr_sxx((2 * b + 3)::float8, b::float8),
+    regr_sxy((b * b)::float8, b::float8),
+    regr_syy((2 * b + 3)::float8, b::float8),
+    stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+    stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+    stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+    string_agg(c_1c, ','), string_agg(c_1b, ','),
+    sum(b::int8), sum(b::numeric),
+    variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+    var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+    var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+    /* aggregate = partial aggregate */
+    any_value(b * 0),
+    bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+    bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+    bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+    bool_and(c_bool),
+    bool_or(c_bool),
+    count(b), count(*),
+    every(c_bool),
+    max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+    min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+    range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+    regr_count((2 * b + 3)::float8, b::float8),
+    sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4),	sum(c_interval), sum(c_money)
+  FROM pagg_tab WHERE c_serial between 1 and 30;
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+   Output: array_agg(pagg_tab.c_int4array), array_agg(pagg_tab.b), avg((pagg_tab.b)::smallint), avg(pagg_tab.b), avg((pagg_tab.b)::bigint), avg(pagg_tab.c_interval), avg((pagg_tab.b)::real), avg((pagg_tab.b)::double precision), avg((pagg_tab.b)::numeric), corr((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), covar_pop((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), covar_samp((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), regr_avgx(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_avgy(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_intercept((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_r2(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_slope((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_sxx((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_sxy(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_syy((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), stddev((pagg_tab.b)::real), stddev((pagg_tab.b)::double precision), stddev((pagg_tab.b)::smallint), stddev(pagg_tab.b), stddev((pagg_tab.b)::bigint), stddev((pagg_tab.b)::numeric), stddev_pop((pagg_tab.b)::real), stddev_pop((pagg_tab.b)::double precision), stddev_pop((pagg_tab.b)::smallint), stddev_pop(pagg_tab.b), stddev_pop((pagg_tab.b)::bigint), stddev_pop((pagg_tab.b)::numeric), stddev_samp((pagg_tab.b)::real), stddev_samp((pagg_tab.b)::double precision), stddev_samp((pagg_tab.b)::smallint), stddev_samp(pagg_tab.b), stddev_samp((pagg_tab.b)::bigint), stddev_samp((pagg_tab.b)::numeric), string_agg(pagg_tab.c_1c, ','::text), string_agg(pagg_tab.c_1b, '\x2c'::bytea), sum((pagg_tab.b)::bigint), sum((pagg_tab.b)::numeric), variance((pagg_tab.b)::real), variance((pagg_tab.b)::double precision), variance((pagg_tab.b)::smallint), variance(pagg_tab.b), variance((pagg_tab.b)::bigint), variance((pagg_tab.b)::numeric), var_pop((pagg_tab.b)::real), var_pop((pagg_tab.b)::double precision), var_pop((pagg_tab.b)::smallint), var_pop(pagg_tab.b), var_pop((pagg_tab.b)::bigint), var_pop((pagg_tab.b)::numeric), var_samp((pagg_tab.b)::real), var_samp((pagg_tab.b)::double precision), var_samp((pagg_tab.b)::smallint), var_samp(pagg_tab.b), var_samp((pagg_tab.b)::bigint), var_samp((pagg_tab.b)::numeric), any_value((pagg_tab.b * 0)), bit_and(pagg_tab.c_bit), bit_and(pagg_tab.c_1or3int2), bit_and(pagg_tab.c_1or3int4), bit_and(pagg_tab.c_1or3int8), bit_or(pagg_tab.c_bit), bit_or(pagg_tab.c_1or3int2), bit_or(pagg_tab.c_1or3int4), bit_or(pagg_tab.c_1or3int8), bit_xor(pagg_tab.c_bit), bit_xor(pagg_tab.c_1or3int2), bit_xor(pagg_tab.c_1or3int4), bit_xor(pagg_tab.c_1or3int8), bool_and(pagg_tab.c_bool), bool_or(pagg_tab.c_bool), count(pagg_tab.b), count(*), every(pagg_tab.c_bool), max(pagg_tab.c_int4array), max(pagg_tab.c_enum), max((pagg_tab.c_1c)::character(1)), max(('01-01-2000'::date + pagg_tab.b)), max(('0.0.0.0'::inet + (pagg_tab.b)::bigint)), max((pagg_tab.b)::real), max((pagg_tab.b)::double precision), max((pagg_tab.b)::smallint), max(pagg_tab.b), max((pagg_tab.b)::bigint), max(pagg_tab.c_interval), max(pagg_tab.c_money), max((pagg_tab.b)::numeric), max((pagg_tab.b)::oid), max(pagg_tab.c_pg_lsn), max(pagg_tab.c_tid), max(pagg_tab.c_1c), max(pagg_tab.c_time), max(pagg_tab.c_timetz), max(pagg_tab.c_timestamp), max(pagg_tab.c_timestamptz), max(pagg_tab.c_xid8), min(pagg_tab.c_int4array), min(pagg_tab.c_enum), min((pagg_tab.c_1c)::character(1)), min(('01-01-2000'::date + pagg_tab.b)), min(('0.0.0.0'::inet + (pagg_tab.b)::bigint)), min((pagg_tab.b)::real), min((pagg_tab.b)::double precision), min((pagg_tab.b)::smallint), min(pagg_tab.b), min((pagg_tab.b)::bigint), min(pagg_tab.c_interval), min(pagg_tab.c_money), min((pagg_tab.b)::numeric), min((pagg_tab.b)::oid), min(pagg_tab.c_pg_lsn), min(pagg_tab.c_tid), min(pagg_tab.c_1c), min(pagg_tab.c_time), min(pagg_tab.c_timetz), min(pagg_tab.c_timestamp), min(pagg_tab.c_timestamptz), min(pagg_tab.c_xid8), range_intersect_agg(pagg_tab.c_int4range), range_intersect_agg(pagg_tab.c_int4multirange), regr_count((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), sum((pagg_tab.b)::real), sum((pagg_tab.b)::double precision), sum((pagg_tab.b)::smallint), sum(pagg_tab.b), sum(pagg_tab.c_interval), sum(pagg_tab.c_money)
+   ->  Append
+         ->  Foreign Scan
+               Output: (PARTIAL array_agg(pagg_tab.c_int4array)), (PARTIAL array_agg(pagg_tab.b)), (PARTIAL avg((pagg_tab.b)::smallint)), (PARTIAL avg(pagg_tab.b)), (PARTIAL avg((pagg_tab.b)::bigint)), (PARTIAL avg(pagg_tab.c_interval)), (PARTIAL avg((pagg_tab.b)::real)), (PARTIAL avg((pagg_tab.b)::double precision)), (PARTIAL avg((pagg_tab.b)::numeric)), (PARTIAL corr((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL covar_pop((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL covar_samp((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_r2(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL stddev((pagg_tab.b)::real)), (PARTIAL stddev((pagg_tab.b)::double precision)), (PARTIAL stddev((pagg_tab.b)::smallint)), (PARTIAL stddev(pagg_tab.b)), (PARTIAL stddev((pagg_tab.b)::bigint)), (PARTIAL stddev((pagg_tab.b)::numeric)), (PARTIAL stddev_pop((pagg_tab.b)::real)), (PARTIAL stddev_pop((pagg_tab.b)::double precision)), (PARTIAL stddev_pop((pagg_tab.b)::smallint)), (PARTIAL stddev_pop(pagg_tab.b)), (PARTIAL stddev_pop((pagg_tab.b)::bigint)), (PARTIAL stddev_pop((pagg_tab.b)::numeric)), (PARTIAL stddev_samp((pagg_tab.b)::real)), (PARTIAL stddev_samp((pagg_tab.b)::double precision)), (PARTIAL stddev_samp((pagg_tab.b)::smallint)), (PARTIAL stddev_samp(pagg_tab.b)), (PARTIAL stddev_samp((pagg_tab.b)::bigint)), (PARTIAL stddev_samp((pagg_tab.b)::numeric)), (PARTIAL string_agg(pagg_tab.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab.b)::bigint)), (PARTIAL sum((pagg_tab.b)::numeric)), (PARTIAL variance((pagg_tab.b)::real)), (PARTIAL variance((pagg_tab.b)::double precision)), (PARTIAL variance((pagg_tab.b)::smallint)), (PARTIAL variance(pagg_tab.b)), (PARTIAL variance((pagg_tab.b)::bigint)), (PARTIAL variance((pagg_tab.b)::numeric)), (PARTIAL var_pop((pagg_tab.b)::real)), (PARTIAL var_pop((pagg_tab.b)::double precision)), (PARTIAL var_pop((pagg_tab.b)::smallint)), (PARTIAL var_pop(pagg_tab.b)), (PARTIAL var_pop((pagg_tab.b)::bigint)), (PARTIAL var_pop((pagg_tab.b)::numeric)), (PARTIAL var_samp((pagg_tab.b)::real)), (PARTIAL var_samp((pagg_tab.b)::double precision)), (PARTIAL var_samp((pagg_tab.b)::smallint)), (PARTIAL var_samp(pagg_tab.b)), (PARTIAL var_samp((pagg_tab.b)::bigint)), (PARTIAL var_samp((pagg_tab.b)::numeric)), (PARTIAL any_value((pagg_tab.b * 0))), (PARTIAL bit_and(pagg_tab.c_bit)), (PARTIAL bit_and(pagg_tab.c_1or3int2)), (PARTIAL bit_and(pagg_tab.c_1or3int4)), (PARTIAL bit_and(pagg_tab.c_1or3int8)), (PARTIAL bit_or(pagg_tab.c_bit)), (PARTIAL bit_or(pagg_tab.c_1or3int2)), (PARTIAL bit_or(pagg_tab.c_1or3int4)), (PARTIAL bit_or(pagg_tab.c_1or3int8)), (PARTIAL bit_xor(pagg_tab.c_bit)), (PARTIAL bit_xor(pagg_tab.c_1or3int2)), (PARTIAL bit_xor(pagg_tab.c_1or3int4)), (PARTIAL bit_xor(pagg_tab.c_1or3int8)), (PARTIAL bool_and(pagg_tab.c_bool)), (PARTIAL bool_or(pagg_tab.c_bool)), (PARTIAL count(pagg_tab.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab.c_bool)), (PARTIAL max(pagg_tab.c_int4array)), (PARTIAL max(pagg_tab.c_enum)), (PARTIAL max((pagg_tab.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab.b)::bigint))), (PARTIAL max((pagg_tab.b)::real)), (PARTIAL max((pagg_tab.b)::double precision)), (PARTIAL max((pagg_tab.b)::smallint)), (PARTIAL max(pagg_tab.b)), (PARTIAL max((pagg_tab.b)::bigint)), (PARTIAL max(pagg_tab.c_interval)), (PARTIAL max(pagg_tab.c_money)), (PARTIAL max((pagg_tab.b)::numeric)), (PARTIAL max((pagg_tab.b)::oid)), (PARTIAL max(pagg_tab.c_pg_lsn)), (PARTIAL max(pagg_tab.c_tid)), (PARTIAL max(pagg_tab.c_1c)), (PARTIAL max(pagg_tab.c_time)), (PARTIAL max(pagg_tab.c_timetz)), (PARTIAL max(pagg_tab.c_timestamp)), (PARTIAL max(pagg_tab.c_timestamptz)), (PARTIAL max(pagg_tab.c_xid8)), (PARTIAL min(pagg_tab.c_int4array)), (PARTIAL min(pagg_tab.c_enum)), (PARTIAL min((pagg_tab.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab.b)::bigint))), (PARTIAL min((pagg_tab.b)::real)), (PARTIAL min((pagg_tab.b)::double precision)), (PARTIAL min((pagg_tab.b)::smallint)), (PARTIAL min(pagg_tab.b)), (PARTIAL min((pagg_tab.b)::bigint)), (PARTIAL min(pagg_tab.c_interval)), (PARTIAL min(pagg_tab.c_money)), (PARTIAL min((pagg_tab.b)::numeric)), (PARTIAL min((pagg_tab.b)::oid)), (PARTIAL min(pagg_tab.c_pg_lsn)), (PARTIAL min(pagg_tab.c_tid)), (PARTIAL min(pagg_tab.c_1c)), (PARTIAL min(pagg_tab.c_time)), (PARTIAL min(pagg_tab.c_timetz)), (PARTIAL min(pagg_tab.c_timestamp)), (PARTIAL min(pagg_tab.c_timestamptz)), (PARTIAL min(pagg_tab.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL sum((pagg_tab.b)::real)), (PARTIAL sum((pagg_tab.b)::double precision)), (PARTIAL sum((pagg_tab.b)::smallint)), (PARTIAL sum(pagg_tab.b)), (PARTIAL sum(pagg_tab.c_interval)), (PARTIAL sum(pagg_tab.c_money))
+               Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+               Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(PARTIAL_AGGREGATE c_interval), sum(c_money) FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+         ->  Foreign Scan
+               Output: (PARTIAL array_agg(pagg_tab_1.c_int4array)), (PARTIAL array_agg(pagg_tab_1.b)), (PARTIAL avg((pagg_tab_1.b)::smallint)), (PARTIAL avg(pagg_tab_1.b)), (PARTIAL avg((pagg_tab_1.b)::bigint)), (PARTIAL avg(pagg_tab_1.c_interval)), (PARTIAL avg((pagg_tab_1.b)::real)), (PARTIAL avg((pagg_tab_1.b)::double precision)), (PARTIAL avg((pagg_tab_1.b)::numeric)), (PARTIAL corr((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL covar_pop((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL covar_samp((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_r2(((pagg_tab_1.b * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab_1.b * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL stddev((pagg_tab_1.b)::real)), (PARTIAL stddev((pagg_tab_1.b)::double precision)), (PARTIAL stddev((pagg_tab_1.b)::smallint)), (PARTIAL stddev(pagg_tab_1.b)), (PARTIAL stddev((pagg_tab_1.b)::bigint)), (PARTIAL stddev((pagg_tab_1.b)::numeric)), (PARTIAL stddev_pop((pagg_tab_1.b)::real)), (PARTIAL stddev_pop((pagg_tab_1.b)::double precision)), (PARTIAL stddev_pop((pagg_tab_1.b)::smallint)), (PARTIAL stddev_pop(pagg_tab_1.b)), (PARTIAL stddev_pop((pagg_tab_1.b)::bigint)), (PARTIAL stddev_pop((pagg_tab_1.b)::numeric)), (PARTIAL stddev_samp((pagg_tab_1.b)::real)), (PARTIAL stddev_samp((pagg_tab_1.b)::double precision)), (PARTIAL stddev_samp((pagg_tab_1.b)::smallint)), (PARTIAL stddev_samp(pagg_tab_1.b)), (PARTIAL stddev_samp((pagg_tab_1.b)::bigint)), (PARTIAL stddev_samp((pagg_tab_1.b)::numeric)), (PARTIAL string_agg(pagg_tab_1.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab_1.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab_1.b)::bigint)), (PARTIAL sum((pagg_tab_1.b)::numeric)), (PARTIAL variance((pagg_tab_1.b)::real)), (PARTIAL variance((pagg_tab_1.b)::double precision)), (PARTIAL variance((pagg_tab_1.b)::smallint)), (PARTIAL variance(pagg_tab_1.b)), (PARTIAL variance((pagg_tab_1.b)::bigint)), (PARTIAL variance((pagg_tab_1.b)::numeric)), (PARTIAL var_pop((pagg_tab_1.b)::real)), (PARTIAL var_pop((pagg_tab_1.b)::double precision)), (PARTIAL var_pop((pagg_tab_1.b)::smallint)), (PARTIAL var_pop(pagg_tab_1.b)), (PARTIAL var_pop((pagg_tab_1.b)::bigint)), (PARTIAL var_pop((pagg_tab_1.b)::numeric)), (PARTIAL var_samp((pagg_tab_1.b)::real)), (PARTIAL var_samp((pagg_tab_1.b)::double precision)), (PARTIAL var_samp((pagg_tab_1.b)::smallint)), (PARTIAL var_samp(pagg_tab_1.b)), (PARTIAL var_samp((pagg_tab_1.b)::bigint)), (PARTIAL var_samp((pagg_tab_1.b)::numeric)), (PARTIAL any_value((pagg_tab_1.b * 0))), (PARTIAL bit_and(pagg_tab_1.c_bit)), (PARTIAL bit_and(pagg_tab_1.c_1or3int2)), (PARTIAL bit_and(pagg_tab_1.c_1or3int4)), (PARTIAL bit_and(pagg_tab_1.c_1or3int8)), (PARTIAL bit_or(pagg_tab_1.c_bit)), (PARTIAL bit_or(pagg_tab_1.c_1or3int2)), (PARTIAL bit_or(pagg_tab_1.c_1or3int4)), (PARTIAL bit_or(pagg_tab_1.c_1or3int8)), (PARTIAL bit_xor(pagg_tab_1.c_bit)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int2)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int4)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int8)), (PARTIAL bool_and(pagg_tab_1.c_bool)), (PARTIAL bool_or(pagg_tab_1.c_bool)), (PARTIAL count(pagg_tab_1.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab_1.c_bool)), (PARTIAL max(pagg_tab_1.c_int4array)), (PARTIAL max(pagg_tab_1.c_enum)), (PARTIAL max((pagg_tab_1.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab_1.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab_1.b)::bigint))), (PARTIAL max((pagg_tab_1.b)::real)), (PARTIAL max((pagg_tab_1.b)::double precision)), (PARTIAL max((pagg_tab_1.b)::smallint)), (PARTIAL max(pagg_tab_1.b)), (PARTIAL max((pagg_tab_1.b)::bigint)), (PARTIAL max(pagg_tab_1.c_interval)), (PARTIAL max(pagg_tab_1.c_money)), (PARTIAL max((pagg_tab_1.b)::numeric)), (PARTIAL max((pagg_tab_1.b)::oid)), (PARTIAL max(pagg_tab_1.c_pg_lsn)), (PARTIAL max(pagg_tab_1.c_tid)), (PARTIAL max(pagg_tab_1.c_1c)), (PARTIAL max(pagg_tab_1.c_time)), (PARTIAL max(pagg_tab_1.c_timetz)), (PARTIAL max(pagg_tab_1.c_timestamp)), (PARTIAL max(pagg_tab_1.c_timestamptz)), (PARTIAL max(pagg_tab_1.c_xid8)), (PARTIAL min(pagg_tab_1.c_int4array)), (PARTIAL min(pagg_tab_1.c_enum)), (PARTIAL min((pagg_tab_1.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab_1.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab_1.b)::bigint))), (PARTIAL min((pagg_tab_1.b)::real)), (PARTIAL min((pagg_tab_1.b)::double precision)), (PARTIAL min((pagg_tab_1.b)::smallint)), (PARTIAL min(pagg_tab_1.b)), (PARTIAL min((pagg_tab_1.b)::bigint)), (PARTIAL min(pagg_tab_1.c_interval)), (PARTIAL min(pagg_tab_1.c_money)), (PARTIAL min((pagg_tab_1.b)::numeric)), (PARTIAL min((pagg_tab_1.b)::oid)), (PARTIAL min(pagg_tab_1.c_pg_lsn)), (PARTIAL min(pagg_tab_1.c_tid)), (PARTIAL min(pagg_tab_1.c_1c)), (PARTIAL min(pagg_tab_1.c_time)), (PARTIAL min(pagg_tab_1.c_timetz)), (PARTIAL min(pagg_tab_1.c_timestamp)), (PARTIAL min(pagg_tab_1.c_timestamptz)), (PARTIAL min(pagg_tab_1.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab_1.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab_1.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL sum((pagg_tab_1.b)::real)), (PARTIAL sum((pagg_tab_1.b)::double precision)), (PARTIAL sum((pagg_tab_1.b)::smallint)), (PARTIAL sum(pagg_tab_1.b)), (PARTIAL sum(pagg_tab_1.c_interval)), (PARTIAL sum(pagg_tab_1.c_money))
+               Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+               Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(PARTIAL_AGGREGATE c_interval), sum(c_money) FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+         ->  Foreign Scan
+               Output: (PARTIAL array_agg(pagg_tab_2.c_int4array)), (PARTIAL array_agg(pagg_tab_2.b)), (PARTIAL avg((pagg_tab_2.b)::smallint)), (PARTIAL avg(pagg_tab_2.b)), (PARTIAL avg((pagg_tab_2.b)::bigint)), (PARTIAL avg(pagg_tab_2.c_interval)), (PARTIAL avg((pagg_tab_2.b)::real)), (PARTIAL avg((pagg_tab_2.b)::double precision)), (PARTIAL avg((pagg_tab_2.b)::numeric)), (PARTIAL corr((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL covar_pop((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL covar_samp((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_r2(((pagg_tab_2.b * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab_2.b * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL stddev((pagg_tab_2.b)::real)), (PARTIAL stddev((pagg_tab_2.b)::double precision)), (PARTIAL stddev((pagg_tab_2.b)::smallint)), (PARTIAL stddev(pagg_tab_2.b)), (PARTIAL stddev((pagg_tab_2.b)::bigint)), (PARTIAL stddev((pagg_tab_2.b)::numeric)), (PARTIAL stddev_pop((pagg_tab_2.b)::real)), (PARTIAL stddev_pop((pagg_tab_2.b)::double precision)), (PARTIAL stddev_pop((pagg_tab_2.b)::smallint)), (PARTIAL stddev_pop(pagg_tab_2.b)), (PARTIAL stddev_pop((pagg_tab_2.b)::bigint)), (PARTIAL stddev_pop((pagg_tab_2.b)::numeric)), (PARTIAL stddev_samp((pagg_tab_2.b)::real)), (PARTIAL stddev_samp((pagg_tab_2.b)::double precision)), (PARTIAL stddev_samp((pagg_tab_2.b)::smallint)), (PARTIAL stddev_samp(pagg_tab_2.b)), (PARTIAL stddev_samp((pagg_tab_2.b)::bigint)), (PARTIAL stddev_samp((pagg_tab_2.b)::numeric)), (PARTIAL string_agg(pagg_tab_2.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab_2.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab_2.b)::bigint)), (PARTIAL sum((pagg_tab_2.b)::numeric)), (PARTIAL variance((pagg_tab_2.b)::real)), (PARTIAL variance((pagg_tab_2.b)::double precision)), (PARTIAL variance((pagg_tab_2.b)::smallint)), (PARTIAL variance(pagg_tab_2.b)), (PARTIAL variance((pagg_tab_2.b)::bigint)), (PARTIAL variance((pagg_tab_2.b)::numeric)), (PARTIAL var_pop((pagg_tab_2.b)::real)), (PARTIAL var_pop((pagg_tab_2.b)::double precision)), (PARTIAL var_pop((pagg_tab_2.b)::smallint)), (PARTIAL var_pop(pagg_tab_2.b)), (PARTIAL var_pop((pagg_tab_2.b)::bigint)), (PARTIAL var_pop((pagg_tab_2.b)::numeric)), (PARTIAL var_samp((pagg_tab_2.b)::real)), (PARTIAL var_samp((pagg_tab_2.b)::double precision)), (PARTIAL var_samp((pagg_tab_2.b)::smallint)), (PARTIAL var_samp(pagg_tab_2.b)), (PARTIAL var_samp((pagg_tab_2.b)::bigint)), (PARTIAL var_samp((pagg_tab_2.b)::numeric)), (PARTIAL any_value((pagg_tab_2.b * 0))), (PARTIAL bit_and(pagg_tab_2.c_bit)), (PARTIAL bit_and(pagg_tab_2.c_1or3int2)), (PARTIAL bit_and(pagg_tab_2.c_1or3int4)), (PARTIAL bit_and(pagg_tab_2.c_1or3int8)), (PARTIAL bit_or(pagg_tab_2.c_bit)), (PARTIAL bit_or(pagg_tab_2.c_1or3int2)), (PARTIAL bit_or(pagg_tab_2.c_1or3int4)), (PARTIAL bit_or(pagg_tab_2.c_1or3int8)), (PARTIAL bit_xor(pagg_tab_2.c_bit)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int2)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int4)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int8)), (PARTIAL bool_and(pagg_tab_2.c_bool)), (PARTIAL bool_or(pagg_tab_2.c_bool)), (PARTIAL count(pagg_tab_2.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab_2.c_bool)), (PARTIAL max(pagg_tab_2.c_int4array)), (PARTIAL max(pagg_tab_2.c_enum)), (PARTIAL max((pagg_tab_2.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab_2.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab_2.b)::bigint))), (PARTIAL max((pagg_tab_2.b)::real)), (PARTIAL max((pagg_tab_2.b)::double precision)), (PARTIAL max((pagg_tab_2.b)::smallint)), (PARTIAL max(pagg_tab_2.b)), (PARTIAL max((pagg_tab_2.b)::bigint)), (PARTIAL max(pagg_tab_2.c_interval)), (PARTIAL max(pagg_tab_2.c_money)), (PARTIAL max((pagg_tab_2.b)::numeric)), (PARTIAL max((pagg_tab_2.b)::oid)), (PARTIAL max(pagg_tab_2.c_pg_lsn)), (PARTIAL max(pagg_tab_2.c_tid)), (PARTIAL max(pagg_tab_2.c_1c)), (PARTIAL max(pagg_tab_2.c_time)), (PARTIAL max(pagg_tab_2.c_timetz)), (PARTIAL max(pagg_tab_2.c_timestamp)), (PARTIAL max(pagg_tab_2.c_timestamptz)), (PARTIAL max(pagg_tab_2.c_xid8)), (PARTIAL min(pagg_tab_2.c_int4array)), (PARTIAL min(pagg_tab_2.c_enum)), (PARTIAL min((pagg_tab_2.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab_2.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab_2.b)::bigint))), (PARTIAL min((pagg_tab_2.b)::real)), (PARTIAL min((pagg_tab_2.b)::double precision)), (PARTIAL min((pagg_tab_2.b)::smallint)), (PARTIAL min(pagg_tab_2.b)), (PARTIAL min((pagg_tab_2.b)::bigint)), (PARTIAL min(pagg_tab_2.c_interval)), (PARTIAL min(pagg_tab_2.c_money)), (PARTIAL min((pagg_tab_2.b)::numeric)), (PARTIAL min((pagg_tab_2.b)::oid)), (PARTIAL min(pagg_tab_2.c_pg_lsn)), (PARTIAL min(pagg_tab_2.c_tid)), (PARTIAL min(pagg_tab_2.c_1c)), (PARTIAL min(pagg_tab_2.c_time)), (PARTIAL min(pagg_tab_2.c_timetz)), (PARTIAL min(pagg_tab_2.c_timestamp)), (PARTIAL min(pagg_tab_2.c_timestamptz)), (PARTIAL min(pagg_tab_2.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab_2.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab_2.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL sum((pagg_tab_2.b)::real)), (PARTIAL sum((pagg_tab_2.b)::double precision)), (PARTIAL sum((pagg_tab_2.b)::smallint)), (PARTIAL sum(pagg_tab_2.b)), (PARTIAL sum(pagg_tab_2.c_interval)), (PARTIAL sum(pagg_tab_2.c_money))
+               Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+               Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(PARTIAL_AGGREGATE c_interval), sum(c_money) FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+(15 rows)
+
+SELECT /* aggregate <> partial aggregate */
+    array_agg(c_int4array), array_agg(b),
+    avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
+    avg(b::float4), avg(b::float8),
+    corr(b::float8, (b * b)::float8),
+    covar_pop(b::float8, (b * b)::float8),
+    covar_samp(b::float8, (b * b)::float8),
+    regr_avgx((2 * b)::float8, b::float8),
+    regr_avgy((2 * b)::float8, b::float8),
+    regr_intercept((2 * b + 3)::float8, b::float8),
+    regr_r2((b * b)::float8, b::float8),
+    regr_slope((2 * b + 3)::float8, b::float8),
+    regr_sxx((2 * b + 3)::float8, b::float8),
+    regr_sxy((b * b)::float8, b::float8),
+    regr_syy((2 * b + 3)::float8, b::float8),
+    stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+    stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+    stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+    string_agg(c_1c, ','), string_agg(c_1b, ','),
+    sum(b::int8), sum(b::numeric),
+    variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+    var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+    var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+    /* aggregate = partial aggregate */
+    any_value(b * 0),
+    bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+    bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+    bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+    bool_and(c_bool),
+    bool_or(c_bool),
+    count(b), count(*),
+    every(c_bool),
+    max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+    min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+    range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+    regr_count((2 * b + 3)::float8, b::float8),
+    sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4),	sum(c_interval), sum(c_money)
+  FROM pagg_tab WHERE c_serial between 1 and 30;
+                                                                                       array_agg                                                                                       |                                     array_agg                                      |         avg         |         avg         |         avg         |    avg     | avg  | avg  |       corr        |    covar_pop     | covar_samp | regr_avgx | regr_avgy | regr_intercept |      regr_r2      | regr_slope | regr_sxx | regr_sxy | regr_syy |     stddev      |     stddev      |       stddev       |       stddev       |       stddev       |       stddev       |    stddev_pop    |    stddev_pop    |     stddev_pop     |     stddev_pop     |     stddev_pop     |     stddev_pop     |   stddev_samp   |   stddev_samp   |    stddev_samp     |    stddev_samp     |    stddev_samp     |    stddev_samp     |                         string_agg                          |                                                        string_agg                                                        | sum | sum | variance | variance |      variance       |      variance       |      variance       |      variance       |     var_pop      |     var_pop      |       var_pop       |       var_pop       |       var_pop       |       var_pop       | var_samp | var_samp |      var_samp       |      var_samp       |      var_samp       |      var_samp       | any_value | bit_and | bit_and | bit_and | bit_and | bit_or | bit_or | bit_or | bit_or | bit_xor | bit_xor | bit_xor | bit_xor | bool_and | bool_or | count | count | every |  max  |  max  | max |    max     |   max    | max | max | max | max | max |   max   |  max  | max | max | max  |  max   | max |   max    |     max     |           max            |             max              | max |  min  | min | min |    min     |   min   | min | min | min | min | min | min |  min  | min | min | min |  min  | min |   min    |     min     |           min            |             min              | min | range_intersect_agg | range_intersect_agg | regr_count | sum | sum | sum | sum |    sum    |  sum   
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+------------+------+------+-------------------+------------------+------------+-----------+-----------+----------------+-------------------+------------+----------+----------+----------+-----------------+-----------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+--------------------+--------------------+--------------------+--------------------+-----------------+-----------------+--------------------+--------------------+--------------------+--------------------+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+-----+-----+----------+----------+---------------------+---------------------+---------------------+---------------------+------------------+------------------+---------------------+---------------------+---------------------+---------------------+----------+----------+---------------------+---------------------+---------------------+---------------------+-----------+---------+---------+---------+---------+--------+--------+--------+--------+---------+---------+---------+---------+----------+---------+-------+-------+-------+-------+-------+-----+------------+----------+-----+-----+-----+-----+-----+---------+-------+-----+-----+------+--------+-----+----------+-------------+--------------------------+------------------------------+-----+-------+-----+-----+------------+---------+-----+-----+-----+-----+-----+-----+-------+-----+-----+-----+-------+-----+----------+-------------+--------------------------+------------------------------+-----+---------------------+---------------------+------------+-----+-----+-----+-----+-----------+--------
+ {{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0}} | {1,2,3,4,5,6,7,8,9,30,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29} | 15.5000000000000000 | 15.5000000000000000 | 15.5000000000000000 | @ 0.5 secs | 15.5 | 15.5 | 0.970298913589258 | 2322.41666666667 |     2402.5 |      15.5 |        31 |              3 | 0.941479981712494 |          2 |   2247.5 |  69672.5 |     8990 | 8.8034084308295 | 8.8034084308295 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.65544144839919 | 8.65544144839919 | 8.6554414483991899 | 8.6554414483991899 | 8.6554414483991899 | 8.6554414483991899 | 8.8034084308295 | 8.8034084308295 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 0,1,2,3,4,5,6,7,8,9,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8 | \x302c312c322c332c342c352c362c372c382c392c392c302c312c322c332c342c352c362c372c382c392c302c312c322c332c342c352c362c372c38 | 465 | 465 |     77.5 |     77.5 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 74.9166666666667 | 74.9166666666667 | 74.9166666666666667 | 74.9166666666666667 | 74.9166666666666667 | 74.9166666666666667 |     77.5 |     77.5 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 |         0 | 01      |       1 |       1 |       1 | 11     |      3 |      3 |      3 | 10      |       2 |       2 |       2 | f        | t       |    30 |    30 | f     | {1,0} | happy | 9   | 01-31-2000 | 0.0.0.30 |  30 |  30 |  30 |  30 |  30 | @ 1 sec | $1.00 |  30 |  30 | 0/30 | (0,30) | 9   | 00:00:30 | 00:00:30-07 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 PST |   9 | {0,0} | sad | 0   | 01-02-2000 | 0.0.0.1 |   1 |   1 |   1 |   1 |   1 | @ 0 | $0.00 |   1 |   1 | 0/1 | (0,1) | 0   | 00:00:01 | 00:00:01-07 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 PST |   0 | [0,1)               | {[0,1),[100,101)}   |         30 | 465 | 465 | 465 | 465 | @ 15 secs | $15.00
+(1 row)
+
+-- Tests for backward compatibility
+ALTER SERVER loopback OPTIONS (ADD check_partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+                                                       QUERY PLAN                                                        
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+   Sort Key: pagg_tab.b
+   ->  Finalize HashAggregate
+         Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+         Group Key: pagg_tab.b
+         ->  Append
+               ->  Foreign Scan
+                     Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b  |         avg         | max | count 
+----+---------------------+-----+-------
+  0 | 10.0000000000000000 |  20 |    60
+  1 | 11.0000000000000000 |  21 |    60
+  2 | 12.0000000000000000 |  22 |    60
+  3 | 13.0000000000000000 |  23 |    60
+  4 | 14.0000000000000000 |  24 |    60
+  5 | 15.0000000000000000 |  25 |    60
+  6 | 16.0000000000000000 |  26 |    60
+  7 | 17.0000000000000000 |  27 |    60
+  8 | 18.0000000000000000 |  28 |    60
+  9 | 19.0000000000000000 |  29 |    60
+ 10 | 10.0000000000000000 |  20 |    60
+ 11 | 11.0000000000000000 |  21 |    60
+ 12 | 12.0000000000000000 |  22 |    60
+ 13 | 13.0000000000000000 |  23 |    60
+ 14 | 14.0000000000000000 |  24 |    60
+ 15 | 15.0000000000000000 |  25 |    60
+ 16 | 16.0000000000000000 |  26 |    60
+ 17 | 17.0000000000000000 |  27 |    60
+ 18 | 18.0000000000000000 |  28 |    60
+ 19 | 19.0000000000000000 |  29 |    60
+ 20 | 10.0000000000000000 |  20 |    60
+ 21 | 11.0000000000000000 |  21 |    60
+ 22 | 12.0000000000000000 |  22 |    60
+ 23 | 13.0000000000000000 |  23 |    60
+ 24 | 14.0000000000000000 |  24 |    60
+ 25 | 15.0000000000000000 |  25 |    60
+ 26 | 16.0000000000000000 |  26 |    60
+ 27 | 17.0000000000000000 |  27 |    60
+ 28 | 18.0000000000000000 |  28 |    60
+ 29 | 19.0000000000000000 |  29 |    60
+ 30 | 10.0000000000000000 |  20 |    60
+ 31 | 11.0000000000000000 |  21 |    60
+ 32 | 12.0000000000000000 |  22 |    60
+ 33 | 13.0000000000000000 |  23 |    60
+ 34 | 14.0000000000000000 |  24 |    60
+ 35 | 15.0000000000000000 |  25 |    60
+ 36 | 16.0000000000000000 |  26 |    60
+ 37 | 17.0000000000000000 |  27 |    60
+ 38 | 18.0000000000000000 |  28 |    60
+ 39 | 19.0000000000000000 |  29 |    60
+ 40 | 10.0000000000000000 |  20 |    60
+ 41 | 11.0000000000000000 |  21 |    60
+ 42 | 12.0000000000000000 |  22 |    60
+ 43 | 13.0000000000000000 |  23 |    60
+ 44 | 14.0000000000000000 |  24 |    60
+ 45 | 15.0000000000000000 |  25 |    60
+ 46 | 16.0000000000000000 |  26 |    60
+ 47 | 17.0000000000000000 |  27 |    60
+ 48 | 18.0000000000000000 |  28 |    60
+ 49 | 19.0000000000000000 |  29 |    60
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET check_partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+                                                       QUERY PLAN                                                        
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+   Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+   Sort Key: pagg_tab.b
+   ->  Finalize HashAggregate
+         Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+         Group Key: pagg_tab.b
+         ->  Append
+               ->  Foreign Scan
+                     Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+               ->  Foreign Scan
+                     Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+                     Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+                     Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b  |         avg         | max | count 
+----+---------------------+-----+-------
+  0 | 10.0000000000000000 |  20 |    60
+  1 | 11.0000000000000000 |  21 |    60
+  2 | 12.0000000000000000 |  22 |    60
+  3 | 13.0000000000000000 |  23 |    60
+  4 | 14.0000000000000000 |  24 |    60
+  5 | 15.0000000000000000 |  25 |    60
+  6 | 16.0000000000000000 |  26 |    60
+  7 | 17.0000000000000000 |  27 |    60
+  8 | 18.0000000000000000 |  28 |    60
+  9 | 19.0000000000000000 |  29 |    60
+ 10 | 10.0000000000000000 |  20 |    60
+ 11 | 11.0000000000000000 |  21 |    60
+ 12 | 12.0000000000000000 |  22 |    60
+ 13 | 13.0000000000000000 |  23 |    60
+ 14 | 14.0000000000000000 |  24 |    60
+ 15 | 15.0000000000000000 |  25 |    60
+ 16 | 16.0000000000000000 |  26 |    60
+ 17 | 17.0000000000000000 |  27 |    60
+ 18 | 18.0000000000000000 |  28 |    60
+ 19 | 19.0000000000000000 |  29 |    60
+ 20 | 10.0000000000000000 |  20 |    60
+ 21 | 11.0000000000000000 |  21 |    60
+ 22 | 12.0000000000000000 |  22 |    60
+ 23 | 13.0000000000000000 |  23 |    60
+ 24 | 14.0000000000000000 |  24 |    60
+ 25 | 15.0000000000000000 |  25 |    60
+ 26 | 16.0000000000000000 |  26 |    60
+ 27 | 17.0000000000000000 |  27 |    60
+ 28 | 18.0000000000000000 |  28 |    60
+ 29 | 19.0000000000000000 |  29 |    60
+ 30 | 10.0000000000000000 |  20 |    60
+ 31 | 11.0000000000000000 |  21 |    60
+ 32 | 12.0000000000000000 |  22 |    60
+ 33 | 13.0000000000000000 |  23 |    60
+ 34 | 14.0000000000000000 |  24 |    60
+ 35 | 15.0000000000000000 |  25 |    60
+ 36 | 16.0000000000000000 |  26 |    60
+ 37 | 17.0000000000000000 |  27 |    60
+ 38 | 18.0000000000000000 |  28 |    60
+ 39 | 19.0000000000000000 |  29 |    60
+ 40 | 10.0000000000000000 |  20 |    60
+ 41 | 11.0000000000000000 |  21 |    60
+ 42 | 12.0000000000000000 |  22 |    60
+ 43 | 13.0000000000000000 |  23 |    60
+ 44 | 14.0000000000000000 |  24 |    60
+ 45 | 15.0000000000000000 |  25 |    60
+ 46 | 16.0000000000000000 |  26 |    60
+ 47 | 17.0000000000000000 |  27 |    60
+ 48 | 18.0000000000000000 |  28 |    60
+ 49 | 19.0000000000000000 |  29 |    60
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '1.0');
+SET enable_partitionwise_join=on;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+                                                                                      QUERY PLAN                                                                                      
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+   Output: avg(t1.b), avg((t1.b)::bigint)
+   ->  Append
+         ->  Foreign Scan
+               Output: (PARTIAL avg(t1.b)), (PARTIAL avg((t1.b)::bigint))
+               Relations: Aggregate on ((public.fpagg_tab_p1 t1) INNER JOIN (public.fpagg_tab_p1 t2))
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE r4.b), avg(PARTIAL_AGGREGATE r4.b::bigint) FROM (public.pagg_tab_p1 r4 INNER JOIN public.pagg_tab_p1 r7 ON (((r4.a = r7.a))))
+         ->  Foreign Scan
+               Output: (PARTIAL avg(t1_1.b)), (PARTIAL avg((t1_1.b)::bigint))
+               Relations: Aggregate on ((public.fpagg_tab_p2 t1_1) INNER JOIN (public.fpagg_tab_p2 t2_1))
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE r5.b), avg(PARTIAL_AGGREGATE r5.b::bigint) FROM (public.pagg_tab_p2 r5 INNER JOIN public.pagg_tab_p2 r8 ON (((r5.a = r8.a))))
+         ->  Foreign Scan
+               Output: (PARTIAL avg(t1_2.b)), (PARTIAL avg((t1_2.b)::bigint))
+               Relations: Aggregate on ((public.fpagg_tab_p3 t1_2) INNER JOIN (public.fpagg_tab_p3 t2_2))
+               Remote SQL: SELECT avg(PARTIAL_AGGREGATE r6.b), avg(PARTIAL_AGGREGATE r6.b::bigint) FROM (public.pagg_tab_p3 r6 INNER JOIN public.pagg_tab_p3 r9 ON (((r6.a = r9.a))))
+(15 rows)
+
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+         avg         |         avg         
+---------------------+---------------------
+ 24.5000000000000000 | 24.5000000000000000
+(1 row)
+
+RESET enable_partitionwise_join;
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '0.1');
+ALTER SERVER loopback OPTIONS (DROP check_partial_aggregate_support);
+-- It is unsafe to push down partial aggregates which contain DISTINCT clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(a), count(distinct b) FROM pagg_tab;
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Aggregate
+   Output: max(pagg_tab.a), count(DISTINCT pagg_tab.b)
    ->  Merge Append
          Sort Key: pagg_tab.b
-         ->  Partial GroupAggregate
-               Group Key: pagg_tab.b
-               ->  Foreign Scan on fpagg_tab_p1 pagg_tab
-         ->  Partial GroupAggregate
-               Group Key: pagg_tab_1.b
-               ->  Foreign Scan on fpagg_tab_p2 pagg_tab_1
-         ->  Partial GroupAggregate
-               Group Key: pagg_tab_2.b
-               ->  Foreign Scan on fpagg_tab_p3 pagg_tab_2
-(14 rows)
+         ->  Foreign Scan on public.fpagg_tab_p1 pagg_tab_1
+               Output: pagg_tab_1.a, pagg_tab_1.b
+               Remote SQL: SELECT a, b FROM public.pagg_tab_p1 ORDER BY b ASC NULLS LAST
+         ->  Foreign Scan on public.fpagg_tab_p2 pagg_tab_2
+               Output: pagg_tab_2.a, pagg_tab_2.b
+               Remote SQL: SELECT a, b FROM public.pagg_tab_p2 ORDER BY b ASC NULLS LAST
+         ->  Foreign Scan on public.fpagg_tab_p3 pagg_tab_3
+               Output: pagg_tab_3.a, pagg_tab_3.b
+               Remote SQL: SELECT a, b FROM public.pagg_tab_p3 ORDER BY b ASC NULLS LAST
+(13 rows)
+
+SELECT max(a), count(distinct b) FROM pagg_tab;
+ max | count 
+-----+-------
+  29 |    50
+(1 row)
+
+-- It is unsafe to push down partial aggregates which contain ORDER BY clauses
+EXPLAIN (VERBOSE, COSTS OFF) SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+                                                   QUERY PLAN                                                    
+-----------------------------------------------------------------------------------------------------------------
+ Aggregate
+   Output: array_agg(pagg_tab.b ORDER BY pagg_tab.b)
+   ->  Sort
+         Output: pagg_tab.b
+         Sort Key: pagg_tab.b
+         ->  Append
+               ->  Foreign Scan on public.fpagg_tab_p1 pagg_tab_1
+                     Output: pagg_tab_1.b
+                     Remote SQL: SELECT b FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+               ->  Foreign Scan on public.fpagg_tab_p2 pagg_tab_2
+                     Output: pagg_tab_2.b
+                     Remote SQL: SELECT b FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+               ->  Foreign Scan on public.fpagg_tab_p3 pagg_tab_3
+                     Output: pagg_tab_3.b
+                     Remote SQL: SELECT b FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+(15 rows)
+
+SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+                                     array_agg                                      
+------------------------------------------------------------------------------------
+ {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}
+(1 row)
 
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
 -- ===================================================================
 -- access rights and superuser
 -- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 630b304338a..cc6d80844ba 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -126,7 +126,8 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
 			strcmp(def->defname, "async_capable") == 0 ||
 			strcmp(def->defname, "parallel_commit") == 0 ||
 			strcmp(def->defname, "parallel_abort") == 0 ||
-			strcmp(def->defname, "keep_connections") == 0)
+			strcmp(def->defname, "keep_connections") == 0 ||
+			strcmp(def->defname, "check_partial_aggregate_support") == 0)
 		{
 			/* these accept only boolean values */
 			(void) defGetBoolean(def);
@@ -268,6 +269,7 @@ InitPgFdwOptions(void)
 		/* batch_size is available on both server and table */
 		{"batch_size", ForeignServerRelationId, false},
 		{"batch_size", ForeignTableRelationId, false},
+		{"check_partial_aggregate_support", ForeignServerRelationId, false},
 		/* async_capable is available on both server and table */
 		{"async_capable", ForeignServerRelationId, false},
 		{"async_capable", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 142dcfc9957..54918b9f1a4 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -36,6 +36,7 @@
 #include "optimizer/pathnode.h"
 #include "optimizer/paths.h"
 #include "optimizer/planmain.h"
+#include "optimizer/planner.h"
 #include "optimizer/prep.h"
 #include "optimizer/restrictinfo.h"
 #include "optimizer/tlist.h"
@@ -519,7 +520,7 @@ static bool foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel,
 							JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel,
 							JoinPathExtraData *extra);
 static bool foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
-								Node *havingQual);
+								GroupPathExtraData *extra);
 static List *get_useful_pathkeys_for_relation(PlannerInfo *root,
 											  RelOptInfo *rel);
 static List *get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel);
@@ -650,6 +651,8 @@ postgresGetForeignRelSize(PlannerInfo *root,
 	fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
 	fpinfo->shippable_extensions = NIL;
 	fpinfo->fetch_size = 100;
+	fpinfo->check_partial_aggregate_support = false;
+	fpinfo->remoteversion = 0;
 	fpinfo->async_capable = false;
 
 	apply_server_options(fpinfo);
@@ -661,7 +664,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
 	 * should match what ExecCheckPermissions() does.  If we fail due to lack
 	 * of permissions, the query would have failed at runtime anyway.
 	 */
-	if (fpinfo->use_remote_estimate)
+	if (fpinfo->use_remote_estimate || fpinfo->check_partial_aggregate_support)
 	{
 		Oid			userid;
 
@@ -6031,9 +6034,10 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
 	fpinfo->pushdown_safe = true;
 
 	/* Get user mapping */
-	if (fpinfo->use_remote_estimate)
+	if (fpinfo->use_remote_estimate || fpinfo->check_partial_aggregate_support)
 	{
-		if (fpinfo_o->use_remote_estimate)
+		if (fpinfo_o->use_remote_estimate ||
+			fpinfo_o->check_partial_aggregate_support)
 			fpinfo->user = fpinfo_o->user;
 		else
 			fpinfo->user = fpinfo_i->user;
@@ -6215,6 +6219,8 @@ apply_server_options(PgFdwRelationInfo *fpinfo)
 				ExtractExtensionList(defGetString(def), false);
 		else if (strcmp(def->defname, "fetch_size") == 0)
 			(void) parse_int(defGetString(def), &fpinfo->fetch_size, 0, NULL);
+		else if (strcmp(def->defname, "check_partial_aggregate_support") == 0)
+			fpinfo->check_partial_aggregate_support = defGetBoolean(def);
 		else if (strcmp(def->defname, "async_capable") == 0)
 			fpinfo->async_capable = defGetBoolean(def);
 	}
@@ -6274,6 +6280,8 @@ merge_fdw_options(PgFdwRelationInfo *fpinfo,
 	fpinfo->shippable_extensions = fpinfo_o->shippable_extensions;
 	fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate;
 	fpinfo->fetch_size = fpinfo_o->fetch_size;
+	fpinfo->check_partial_aggregate_support = fpinfo_o->check_partial_aggregate_support;
+	fpinfo->remoteversion = fpinfo_o->remoteversion;
 	fpinfo->async_capable = fpinfo_o->async_capable;
 
 	/* Merge the table level options from either side of the join. */
@@ -6456,7 +6464,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
  */
 static bool
 foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
-					Node *havingQual)
+					GroupPathExtraData *extra)
 {
 	Query	   *query = root->parse;
 	PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) grouped_rel->fdw_private;
@@ -6465,6 +6473,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
 	ListCell   *lc;
 	int			i;
 	List	   *tlist = NIL;
+	bool		partial = extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL;
 
 	/* We currently don't support pushing Grouping Sets. */
 	if (query->groupingSets)
@@ -6498,6 +6507,12 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
 	 * a node, as long as it's not at top level; then no match is possible.
 	 */
 	i = 0;
+	fpinfo->group_clause = query->groupClause;
+	if (partial)
+	{
+		fpinfo->group_clause = extra->groupClausePartial;
+		grouping_target = extra->partial_target;
+	}
 	foreach(lc, grouping_target->exprs)
 	{
 		Expr	   *expr = (Expr *) lfirst(lc);
@@ -6509,7 +6524,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
 		 * check the whole GROUP BY clause not just processed_groupClause,
 		 * because we will ship all of it, cf. appendGroupByClause.
 		 */
-		if (sgref && get_sortgroupref_clause_noerr(sgref, query->groupClause))
+		if (sgref && get_sortgroupref_clause_noerr(sgref, fpinfo->group_clause))
 		{
 			TargetEntry *tle;
 
@@ -6595,9 +6610,9 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
 	 * Classify the pushable and non-pushable HAVING clauses and save them in
 	 * remote_conds and local_conds of the grouped rel's fpinfo.
 	 */
-	if (havingQual)
+	if (extra->havingQual && !partial)
 	{
-		foreach(lc, (List *) havingQual)
+		foreach(lc, (List *) extra->havingQual)
 		{
 			Expr	   *expr = (Expr *) lfirst(lc);
 			RestrictInfo *rinfo;
@@ -6711,6 +6726,7 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage,
 
 	/* Ignore stages we don't support; and skip any duplicate calls. */
 	if ((stage != UPPERREL_GROUP_AGG &&
+		 stage != UPPERREL_PARTIAL_GROUP_AGG &&
 		 stage != UPPERREL_ORDERED &&
 		 stage != UPPERREL_FINAL) ||
 		output_rel->fdw_private)
@@ -6727,6 +6743,10 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage,
 			add_foreign_grouping_paths(root, input_rel, output_rel,
 									   (GroupPathExtraData *) extra);
 			break;
+		case UPPERREL_PARTIAL_GROUP_AGG:
+			add_foreign_grouping_paths(root, input_rel, output_rel,
+									   (GroupPathExtraData *) extra);
+			break;
 		case UPPERREL_ORDERED:
 			add_foreign_ordered_paths(root, input_rel, output_rel);
 			break;
@@ -6767,7 +6787,8 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
 		return;
 
 	Assert(extra->patype == PARTITIONWISE_AGGREGATE_NONE ||
-		   extra->patype == PARTITIONWISE_AGGREGATE_FULL);
+		   extra->patype == PARTITIONWISE_AGGREGATE_FULL ||
+		   extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL);
 
 	/* save the input_rel as outerrel in fpinfo */
 	fpinfo->outerrel = input_rel;
@@ -6787,7 +6808,7 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
 	 * Use HAVING qual from extra. In case of child partition, it will have
 	 * translated Vars.
 	 */
-	if (!foreign_grouping_ok(root, grouped_rel, extra->havingQual))
+	if (!foreign_grouping_ok(root, grouped_rel, extra))
 		return;
 
 	/*
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 37c1575af6c..dcf164163e8 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -86,6 +86,15 @@ typedef struct PgFdwRelationInfo
 	ForeignServer *server;
 	UserMapping *user;			/* only set in use_remote_estimate mode */
 
+	/* for partial aggregate pushdown */
+	bool		check_partial_aggregate_support;
+
+	/*
+	 * If remoteversion is zero, it means the remote server version has not
+	 * been acquired.
+	 */
+	int			remoteversion;
+
 	int			fetch_size;		/* fetch size for this remote table */
 
 	/*
@@ -110,6 +119,7 @@ typedef struct PgFdwRelationInfo
 
 	/* Grouping information */
 	List	   *grouped_tlist;
+	List	   *group_clause;
 
 	/* Subquery information */
 	bool		make_outerrel_subquery; /* do we deparse outerrel as a
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 812e7646e16..e865a685ca7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3117,16 +3117,31 @@ RESET enable_partitionwise_join;
 -- ===================================================================
 -- test partitionwise aggregates
 -- ===================================================================
-
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+					c_int4array _int4, c_interval interval,
+					c_money money, c_1c text, c_1b bytea,
+					c_bit bit(2), c_1or3int2 int2,
+					c_1or3int4 int4, c_1or3int8 int8,
+					c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+					c_tid tid, c_int4range int4range,
+					c_int4multirange int4multirange,
+					c_time time, c_timetz timetz,
+					c_timestamp timestamp, c_timestamptz timestamptz,
+					c_xid8 xid8)
+	PARTITION BY RANGE(a);
 
 CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
 CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
 CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
 
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
 
 -- Create foreign partitions
 CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
@@ -3134,9 +3149,13 @@ CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO
 CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');
 
 ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
 ANALYZE fpagg_tab_p1;
 ANALYZE fpagg_tab_p2;
 ANALYZE fpagg_tab_p3;
+SET extra_float_digits = 0;
 
 -- When GROUP BY clause matches with PARTITION KEY.
 -- Plan with partitionwise aggregates is disabled
@@ -3150,16 +3169,143 @@ EXPLAIN (COSTS OFF)
 SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
 SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
 
+-- Check partial aggregate over partitioned table
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+
 -- Check with whole-row reference
 -- Should have all the columns in the target list for the given relation
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
 SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
 
--- When GROUP BY clause does not match with PARTITION KEY.
-EXPLAIN (COSTS OFF)
+-- Partial aggregates are safe to push down when there is a HAVING clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
 SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
 
+-- Partial aggregates are safe to push down without having clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+-- Partial aggregates are safe to push down even if we need both variable and variable-based expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT /* aggregate <> partial aggregate */
+    array_agg(c_int4array), array_agg(b),
+    avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval), avg(b::float4), avg(b::float8), avg(b::numeric),
+    corr(b::float8, (b * b)::float8),
+    covar_pop(b::float8, (b * b)::float8),
+    covar_samp(b::float8, (b * b)::float8),
+    regr_avgx((2 * b)::float8, b::float8),
+    regr_avgy((2 * b)::float8, b::float8),
+    regr_intercept((2 * b + 3)::float8, b::float8),
+    regr_r2((b * b)::float8, b::float8),
+    regr_slope((2 * b + 3)::float8, b::float8),
+    regr_sxx((2 * b + 3)::float8, b::float8),
+    regr_sxy((b * b)::float8, b::float8),
+    regr_syy((2 * b + 3)::float8, b::float8),
+    stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+    stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+    stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+    string_agg(c_1c, ','), string_agg(c_1b, ','),
+    sum(b::int8), sum(b::numeric),
+    variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+    var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+    var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+    /* aggregate = partial aggregate */
+    any_value(b * 0),
+    bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+    bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+    bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+    bool_and(c_bool),
+    bool_or(c_bool),
+    count(b), count(*),
+    every(c_bool),
+    max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+    min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+    range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+    regr_count((2 * b + 3)::float8, b::float8),
+    sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4),	sum(c_interval), sum(c_money)
+  FROM pagg_tab WHERE c_serial between 1 and 30;
+
+SELECT /* aggregate <> partial aggregate */
+    array_agg(c_int4array), array_agg(b),
+    avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
+    avg(b::float4), avg(b::float8),
+    corr(b::float8, (b * b)::float8),
+    covar_pop(b::float8, (b * b)::float8),
+    covar_samp(b::float8, (b * b)::float8),
+    regr_avgx((2 * b)::float8, b::float8),
+    regr_avgy((2 * b)::float8, b::float8),
+    regr_intercept((2 * b + 3)::float8, b::float8),
+    regr_r2((b * b)::float8, b::float8),
+    regr_slope((2 * b + 3)::float8, b::float8),
+    regr_sxx((2 * b + 3)::float8, b::float8),
+    regr_sxy((b * b)::float8, b::float8),
+    regr_syy((2 * b + 3)::float8, b::float8),
+    stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+    stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+    stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+    string_agg(c_1c, ','), string_agg(c_1b, ','),
+    sum(b::int8), sum(b::numeric),
+    variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+    var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+    var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+    /* aggregate = partial aggregate */
+    any_value(b * 0),
+    bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+    bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+    bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+    bool_and(c_bool),
+    bool_or(c_bool),
+    count(b), count(*),
+    every(c_bool),
+    max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+    min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+    range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+    regr_count((2 * b + 3)::float8, b::float8),
+    sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4),	sum(c_interval), sum(c_money)
+  FROM pagg_tab WHERE c_serial between 1 and 30;
+
+-- Tests for backward compatibility
+ALTER SERVER loopback OPTIONS (ADD check_partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+ALTER SERVER loopback OPTIONS (SET check_partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '1.0');
+SET enable_partitionwise_join=on;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+RESET enable_partitionwise_join;
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '0.1');
+
+ALTER SERVER loopback OPTIONS (DROP check_partial_aggregate_support);
+
+-- It is unsafe to push down partial aggregates which contain DISTINCT clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(a), count(distinct b) FROM pagg_tab;
+SELECT max(a), count(distinct b) FROM pagg_tab;
+
+-- It is unsafe to push down partial aggregates which contain ORDER BY clauses
+EXPLAIN (VERBOSE, COSTS OFF) SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
+
 -- ===================================================================
 -- access rights and superuser
 -- ===================================================================
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index dbd5661d170..b512bcfb6dc 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1077,9 +1077,14 @@ finalize_aggregate(AggState *aggstate,
 	}
 
 	/*
-	 * Apply the agg's finalfn if one is provided, else return transValue.
+	 * If the agg's finalfn is provided and PARTIAL_AGGREGATE keyword is
+	 * not specified, apply the agg's finalfn.
+	 * If PARTIAL_AGGREGATE keyword is specified and the transValue type
+	 * is internal, apply the agg's serialfn. In this case, if the agg's
+	 * serialfn must not be invalid. Otherwise return transValue.
 	 */
-	if (OidIsValid(peragg->finalfn_oid))
+	if (OidIsValid(peragg->finalfn_oid) &&
+		(peragg->aggref->agg_partial == false))
 	{
 		int			numFinalArgs = peragg->numFinalArgs;
 
@@ -1125,6 +1130,44 @@ finalize_aggregate(AggState *aggstate,
 		}
 		aggstate->curperagg = NULL;
 	}
+	else if (peragg->aggref->agg_partial
+			&& (peragg->aggref->aggtranstype == INTERNALOID))
+	{
+		if(!OidIsValid(peragg->serialfn_oid))
+			elog(ERROR, "serialfunc is note provided for partial aggregate");
+
+		/* set up aggstate->curperagg for AggGetAggref() */
+		aggstate->curperagg = peragg;
+
+		InitFunctionCallInfoData(*fcinfo, &peragg->serialfn, 1,
+								 InvalidOid, (void *) aggstate, NULL);
+
+		/* Fill in the transition state value */
+		fcinfo->args[0].value =
+			MakeExpandedObjectReadOnly(pergroupstate->transValue,
+									   pergroupstate->transValueIsNull,
+									   pertrans->transtypeLen);
+		fcinfo->args[0].isnull = pergroupstate->transValueIsNull;
+		anynull |= pergroupstate->transValueIsNull;
+
+		if (fcinfo->flinfo->fn_strict && anynull)
+		{
+			/* don't call a strict function with NULL inputs */
+			*resultVal = (Datum) 0;
+			*resultIsNull = true;
+		}
+		else
+		{
+			Datum		result;
+
+			result = FunctionCallInvoke(fcinfo);
+			*resultIsNull = fcinfo->isnull;
+			*resultVal = MakeExpandedObjectReadOnly(result,
+													fcinfo->isnull,
+													peragg->resulttypeLen);
+		}
+		aggstate->curperagg = NULL;
+	}
 	else
 	{
 		*resultVal =
@@ -3663,7 +3706,7 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
 		Oid			serialfn_oid,
 					deserialfn_oid;
 		Oid			aggOwner;
-		Expr	   *finalfnexpr;
+		Expr	   *finalfnexpr, *serialfnexpr;
 		Oid			aggtranstype;
 
 		/* Planner should have assigned aggregate to correct level */
@@ -3820,6 +3863,17 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
 			fmgr_info(finalfn_oid, &peragg->finalfn);
 			fmgr_info_set_expr((Node *) finalfnexpr, &peragg->finalfn);
 		}
+		/*
+		 * build expression trees for the serialfn, if it exists and is required.
+		 */
+		if (OidIsValid(aggform->aggserialfn) && peragg->aggref->agg_partial)
+		{
+			build_aggregate_serialfn_expr(aggform->aggserialfn,
+										 &serialfnexpr);
+			peragg->serialfn_oid = aggform->aggserialfn;
+			fmgr_info(aggform->aggserialfn, &peragg->serialfn);
+			fmgr_info_set_expr((Node *) serialfnexpr, &peragg->serialfn);
+		}
 
 		/* get info about the output value's datatype */
 		get_typlenbyval(aggref->aggtype,
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index a02332a1ecb..aac35b9d9a3 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -597,6 +597,7 @@ makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
 	n->agg_within_group = false;
 	n->agg_star = false;
 	n->agg_distinct = false;
+	n->agg_partial = false;
 	n->func_variadic = false;
 	n->funcformat = funcformat;
 	n->location = location;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index be4e182869a..daed187eb6f 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -208,7 +208,7 @@ static PathTarget *make_group_input_target(PlannerInfo *root,
 										   PathTarget *final_target);
 static PathTarget *make_partial_grouping_target(PlannerInfo *root,
 												PathTarget *grouping_target,
-												Node *havingQual);
+												GroupPathExtraData *extra);
 static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
 static void optimize_window_clauses(PlannerInfo *root,
 									WindowFuncLists *wflists);
@@ -5339,6 +5339,99 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_target)
 	return set_pathtarget_cost_width(root, input_target);
 }
 
+/*
+ * setGroupClausePartial
+ *	  Generate a groupClause and a pathtarget for partial aggregate
+ *	  pushdown by FDW and set them to GroupPathExtraData.
+ */
+static void
+setGroupClausePartial(PathTarget *partial_target, List *non_group_exprs,
+					  List *groupClause, GroupPathExtraData *extra)
+{
+	int			exprno,
+				refno;
+	ListCell   *lc;
+	Index		maxRef = 0;
+	List	   *exprs_processed = NIL;
+	int			exprs_num = 0;
+
+	foreach(lc, groupClause)
+	{
+		SortGroupClause *sgc = (SortGroupClause *) lfirst(lc);
+
+		if (sgc->tleSortGroupRef > maxRef)
+			maxRef = sgc->tleSortGroupRef;
+	}
+	maxRef++;
+
+	extra->groupClausePartial = list_copy_deep(groupClause);
+	extra->partial_target = copy_pathtarget(partial_target);
+
+	if (partial_target->exprs)
+		exprs_num = partial_target->exprs->length;
+
+	foreach(lc, non_group_exprs)
+	{
+		Expr	   *expr = (Expr *) lfirst(lc);
+
+		refno = -1;
+		if (list_member(exprs_processed, expr) ||
+			(!IsA(expr, Var) && !IsA(expr, PlaceHolderVar)))
+			continue;
+		exprs_processed = lappend(exprs_processed, expr);
+		for (exprno = 0; exprno < exprs_num; exprno++)
+		{
+			Expr	   *target_expr = (Expr *) list_nth(partial_target->exprs, exprno);
+
+			if (equal(target_expr, expr))
+			{
+				refno = exprno;
+				break;
+			}
+		}
+		if (refno < 0)
+		{
+			SortGroupClause *grpcl = makeNode(SortGroupClause);
+
+			grpcl->tleSortGroupRef = maxRef++;
+			extra->groupClausePartial = lappend(extra->groupClausePartial, grpcl);
+			add_column_to_pathtarget(extra->partial_target, expr, grpcl->tleSortGroupRef);
+		}
+	}
+}
+
+/*
+ * adjustAggrefForPartial
+ * Adjust Aggrefs to put them in partial mode
+ */
+static void
+adjustAggrefForPartial(List *exprs)
+{
+	ListCell   *lc;
+
+	foreach(lc, exprs)
+	{
+		Aggref	   *aggref = (Aggref *) lfirst(lc);
+
+		if (IsA(aggref, Aggref))
+		{
+			Aggref	   *newaggref;
+
+			/*
+			 * We shouldn't need to copy the substructure of the Aggref node,
+			 * but flat-copy the node itself to avoid damaging other trees.
+			 */
+			newaggref = makeNode(Aggref);
+			memcpy(newaggref, aggref, sizeof(Aggref));
+
+			/* For now, assume serialization is required */
+			mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
+
+			lfirst(lc) = newaggref;
+		}
+	}
+}
+
 /*
  * make_partial_grouping_target
  *	  Generate appropriate PathTarget for output of partial aggregate
@@ -5354,11 +5447,15 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_target)
  *
  * grouping_target is the tlist to be emitted by the topmost aggregation step.
  * havingQual represents the HAVING clause.
+ *
+ * Modified PathTarget cannot be used by FDW as-is to deparse this statement.
+ * So, before modifying PathTarget, setGroupClausePartial generates
+ * another Pathtarget and another list of SortGroupClauses.
  */
 static PathTarget *
 make_partial_grouping_target(PlannerInfo *root,
 							 PathTarget *grouping_target,
-							 Node *havingQual)
+							 GroupPathExtraData *extra)
 {
 	PathTarget *partial_target;
 	List	   *non_group_cols;
@@ -5400,8 +5497,8 @@ make_partial_grouping_target(PlannerInfo *root,
 	/*
 	 * If there's a HAVING clause, we'll need the Vars/Aggrefs it uses, too.
 	 */
-	if (havingQual)
-		non_group_cols = lappend(non_group_cols, havingQual);
+	if (extra->havingQual)
+		non_group_cols = lappend(non_group_cols, extra->havingQual);
 
 	/*
 	 * Pull out all the Vars, PlaceHolderVars, and Aggrefs mentioned in
@@ -5414,35 +5511,17 @@ make_partial_grouping_target(PlannerInfo *root,
 									  PVC_INCLUDE_AGGREGATES |
 									  PVC_RECURSE_WINDOWFUNCS |
 									  PVC_INCLUDE_PLACEHOLDERS);
-
+	setGroupClausePartial(partial_target, non_group_exprs, root->parse->groupClause, extra);
 	add_new_columns_to_pathtarget(partial_target, non_group_exprs);
+	add_new_columns_to_pathtarget(extra->partial_target, non_group_exprs);
 
 	/*
 	 * Adjust Aggrefs to put them in partial mode.  At this point all Aggrefs
 	 * are at the top level of the target list, so we can just scan the list
 	 * rather than recursing through the expression trees.
 	 */
-	foreach(lc, partial_target->exprs)
-	{
-		Aggref	   *aggref = (Aggref *) lfirst(lc);
-
-		if (IsA(aggref, Aggref))
-		{
-			Aggref	   *newaggref;
-
-			/*
-			 * We shouldn't need to copy the substructure of the Aggref node,
-			 * but flat-copy the node itself to avoid damaging other trees.
-			 */
-			newaggref = makeNode(Aggref);
-			memcpy(newaggref, aggref, sizeof(Aggref));
-
-			/* For now, assume serialization is required */
-			mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
-
-			lfirst(lc) = newaggref;
-		}
-	}
+	adjustAggrefForPartial(partial_target->exprs);
+	adjustAggrefForPartial(extra->partial_target->exprs);
 
 	/* clean up cruft */
 	list_free(non_group_exprs);
@@ -7095,7 +7174,7 @@ create_partial_grouping_paths(PlannerInfo *root,
 	 */
 	partially_grouped_rel->reltarget =
 		make_partial_grouping_target(root, grouped_rel->reltarget,
-									 extra->havingQual);
+									 extra);
 
 	if (!extra->partial_costs_set)
 	{
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 22a1fa29f36..440f54e4018 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2557,6 +2557,7 @@ convert_combining_aggrefs(Node *node, void *context)
 		parent_agg = copyObject(child_agg);
 		child_agg->args = orig_agg->args;
 		child_agg->aggfilter = orig_agg->aggfilter;
+		child_agg->agg_partial = orig_agg->agg_partial;
 
 		/*
 		 * Now, set up child_agg to represent the first phase of partial
diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c
index 127f25bb2f5..6cdd791f4a3 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -412,6 +412,7 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
 		/* all of the following must be the same or it's no match */
 		if (newagg->inputcollid != existingRef->inputcollid ||
 			newagg->aggtranstype != existingRef->aggtranstype ||
+			newagg->agg_partial != existingRef->agg_partial ||
 			newagg->aggstar != existingRef->aggstar ||
 			newagg->aggvariadic != existingRef->aggvariadic ||
 			newagg->aggkind != existingRef->aggkind ||
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 130f7fc7c3f..fd6e7506c92 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -744,7 +744,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
-	PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD
+	PARALLEL PARAMETER PARSER PARTIAL PARTIAL_AGGREGATE PARTITION PASSING PASSWORD
 	PLACING PLANS POLICY
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -15297,6 +15297,26 @@ func_application: func_name '(' ')'
 					n->agg_distinct = true;
 					$$ = (Node *) n;
 				}
+			| func_name '(' PARTIAL_AGGREGATE func_arg_list opt_sort_clause ')'
+				{
+					FuncCall   *n = makeFuncCall($1, $4,
+												 COERCE_EXPLICIT_CALL,
+												 @1);
+
+					n->agg_order = $5;
+					n->agg_partial = true;
+					$$ = (Node *) n;
+				}
+			| func_name '(' PARTIAL_AGGREGATE '*' ')'
+				{
+					FuncCall   *n = makeFuncCall($1, NIL,
+												 COERCE_EXPLICIT_CALL,
+												 @1);
+
+					n->agg_star = true;
+					n->agg_partial = true;
+					$$ = (Node *) n;
+				}
 			| func_name '(' '*' ')'
 				{
 					/*
@@ -15352,6 +15372,11 @@ func_expr: func_application within_group_clause filter_clause over_clause
 									(errcode(ERRCODE_SYNTAX_ERROR),
 									 errmsg("cannot use DISTINCT with WITHIN GROUP"),
 									 parser_errposition(@2)));
+						if (n->agg_partial)
+							ereport(ERROR,
+									(errcode(ERRCODE_SYNTAX_ERROR),
+									 errmsg("cannot use PARTIAL_AGGREGATE with WITHIN GROUP"),
+									 parser_errposition(@2)));
 						if (n->func_variadic)
 							ereport(ERROR,
 									(errcode(ERRCODE_SYNTAX_ERROR),
@@ -17618,6 +17643,7 @@ reserved_keyword:
 			| ONLY
 			| OR
 			| ORDER
+			| PARTIAL_AGGREGATE
 			| PLACING
 			| PRIMARY
 			| REFERENCES
@@ -17924,6 +17950,7 @@ bare_label_keyword:
 			| PARAMETER
 			| PARSER
 			| PARTIAL
+			| PARTIAL_AGGREGATE
 			| PARTITION
 			| PASSING
 			| PASSWORD
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 9d151a880b8..432d18bc47a 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -101,8 +101,8 @@ static Node *make_agg_arg(Oid argtype, Oid argcollation);
  * pstate level.
  */
 void
-transformAggregateCall(ParseState *pstate, Aggref *agg,
-					   List *args, List *aggorder, bool agg_distinct)
+transformAggregateCall(ParseState *pstate, Aggref *agg, List *args,
+					   List *aggorder, bool agg_distinct, bool agg_partial)
 {
 	List	   *argtypes = NIL;
 	List	   *tlist = NIL;
@@ -147,8 +147,8 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
 										 torder, tlist, sortby);
 		}
 
-		/* Never any DISTINCT in an ordered-set agg */
-		Assert(!agg_distinct);
+		/* Never any DISTINCT and PARTIAL_AGG in an ordered-set agg */
+		Assert(!agg_distinct || !agg_partial);
 	}
 	else
 	{
@@ -222,6 +222,22 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
 	agg->args = tlist;
 	agg->aggorder = torder;
 	agg->aggdistinct = tdistinct;
+	agg->agg_partial = agg_partial;
+	if(agg->agg_partial){
+		HeapTuple	aggtup;
+		Form_pg_aggregate aggform;
+
+		aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
+		if (!HeapTupleIsValid(aggtup))
+			elog(ERROR, "cache lookup failed for aggregate %u", agg->aggfnoid);
+		aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+		ReleaseSysCache(aggtup);
+		if(aggform->aggtranstype == INTERNALOID){
+			agg->aggtype = BYTEAOID;
+		} else {
+			agg->aggtype = aggform->aggtranstype;
+		}
+	}
 
 	/*
 	 * Now build the aggargtypes list with the type OIDs of the direct and
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 4b50278fd0d..6b24f047f82 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -543,6 +543,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
 				fc->over == NULL &&
 				!fc->agg_star &&
 				!fc->agg_distinct &&
+				!fc->agg_partial &&
 				!fc->func_variadic &&
 				coldeflist == NIL)
 			{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9300c7b9abc..3c34df48bb1 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3776,7 +3776,8 @@ transformJsonAggConstructor(ParseState *pstate, JsonAggConstructor *agg_ctor,
 		aggref->aggtransno = -1;
 		aggref->location = agg_ctor->location;
 
-		transformAggregateCall(pstate, aggref, args, agg_ctor->agg_order, false);
+		transformAggregateCall(pstate, aggref, args, agg_ctor->agg_order, false,
+							   false);
 
 		node = (Node *) aggref;
 	}
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index fdb3e6df338..0c30afb1c79 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -97,6 +97,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 	bool		agg_within_group = (fn ? fn->agg_within_group : false);
 	bool		agg_star = (fn ? fn->agg_star : false);
 	bool		agg_distinct = (fn ? fn->agg_distinct : false);
+	bool		agg_partial = (fn ? fn->agg_partial : false);
 	bool		func_variadic = (fn ? fn->func_variadic : false);
 	CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
 	bool		could_be_projection;
@@ -222,7 +223,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 	 */
 	could_be_projection = (nargs == 1 && !proc_call &&
 						   agg_order == NIL && agg_filter == NULL &&
-						   !agg_star && !agg_distinct && over == NULL &&
+						   !agg_star && !agg_distinct &&
+						   !agg_partial && over == NULL &&
 						   !func_variadic && argnames == NIL &&
 						   list_length(funcname) == 1 &&
 						   (actual_arg_types[0] == RECORDOID ||
@@ -322,6 +324,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 					 errmsg("DISTINCT specified, but %s is not an aggregate function",
 							NameListToString(funcname)),
 					 parser_errposition(pstate, location)));
+		if (agg_partial)
+			ereport(ERROR,
+					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					 errmsg("PARTIAL_AGGREGATE specified, but %s is not an aggregate function",
+							NameListToString(funcname)),
+					 parser_errposition(pstate, location)));
 		if (agg_within_group)
 			ereport(ERROR,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -392,6 +400,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 						 parser_errposition(pstate, location)));
 			/* gram.y rejects DISTINCT + WITHIN GROUP */
 			Assert(!agg_distinct);
+			/* gram.y rejects PARTIAL_AGGREGATE */
+			Assert(!agg_partial);
 			/* gram.y rejects VARIADIC + WITHIN GROUP */
 			Assert(!func_variadic);
 
@@ -814,7 +824,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 					 parser_errposition(pstate, location)));
 
 		/* parse_agg.c does additional aggregate-specific processing */
-		transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct);
+		transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct,
+							   agg_partial);
 
 		retval = (Node *) aggref;
 	}
@@ -845,6 +856,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 					 errmsg("DISTINCT is not implemented for window functions"),
 					 parser_errposition(pstate, location)));
 
+		/*
+		 * partial aggregates not allowed in windows yet
+		 */
+		if (agg_partial)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("PARTIAL_AGGREGATE is not implemented for window functions"),
+					 parser_errposition(pstate, location)));
+
 		/*
 		 * Reject attempt to call a parameterless aggregate without (*)
 		 * syntax.  This is mere pedantry but some folks insisted ...
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a928a8c55df..21265ce4da4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10123,7 +10123,8 @@ get_agg_expr_helper(Aggref *aggref, deparse_context *context,
 
 	/* Print the aggregate name, schema-qualified if needed */
 	appendStringInfo(buf, "%s(%s", funcname,
-					 (aggref->aggdistinct != NIL) ? "DISTINCT " : "");
+					 (aggref->aggdistinct != NIL) ? "DISTINCT " :
+					 aggref->agg_partial ? "PARTIAL_AGGREGATE " : "");
 
 	if (AGGKIND_IS_ORDERED_SET(aggref->aggkind))
 	{
diff --git a/src/include/executor/nodeAgg.h b/src/include/executor/nodeAgg.h
index 684779a6a32..079360c1533 100644
--- a/src/include/executor/nodeAgg.h
+++ b/src/include/executor/nodeAgg.h
@@ -206,6 +206,15 @@ typedef struct AggStatePerAggData
 	 */
 	FmgrInfo	finalfn;
 
+	/* Optional Oid of serial function (may be InvalidOid) */
+	Oid			serialfn_oid;
+
+	/*
+	 * fmgr lookup data for serial function --- only valid when serialfn_oid is
+	 * not InvalidOid.
+	 */
+	FmgrInfo	serialfn;
+
 	/*
 	 * Number of arguments to pass to the finalfn.  This is always at least 1
 	 * (the transition state value) plus any ordered-set direct args. If the
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index baa6a97c7e2..e5d0d0ddb97 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -421,6 +421,7 @@ typedef struct FuncCall
 	bool		agg_within_group;	/* ORDER BY appeared in WITHIN GROUP */
 	bool		agg_star;		/* argument was really '*' */
 	bool		agg_distinct;	/* arguments were labeled DISTINCT */
+	bool		agg_partial;	/* arguments were labeled PARTIAL_AGGREGATE */
 	bool		func_variadic;	/* last argument was labeled VARIADIC */
 	CoercionForm funcformat;	/* how to display this node */
 	int			location;		/* token location, or -1 if unknown */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 534692bee18..c29e91f1442 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -3258,6 +3258,9 @@ typedef enum
  * havingQual gives list of quals to be applied after aggregation.
  * targetList gives list of columns to be projected.
  * patype is the type of partitionwise aggregation that is being performed.
+ * groupClausePartial is List of SortGroupClauses for partial aggregate
+ * 		pushdown by FDW
+ * partial_target is PathTarget for partial aggregate pushdown by FDW
  */
 typedef struct
 {
@@ -3272,6 +3275,8 @@ typedef struct
 	Node	   *havingQual;
 	List	   *targetList;
 	PartitionwiseAggregateType patype;
+	List	   *groupClausePartial;
+	PathTarget *partial_target;
 } GroupPathExtraData;
 
 /*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4a154606d2b..7ccbdc44f3a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -459,6 +459,9 @@ typedef struct Aggref
 	/* DISTINCT (list of SortGroupClause) */
 	List	   *aggdistinct;
 
+	/* true if there is PARTIAL_AGGREGATE keyword */
+	bool        agg_partial;
+
 	/* FILTER expression, if any */
 	Expr	   *aggfilter;
 
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 2331acac091..942081a5ad0 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -323,6 +323,7 @@ PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("parameter", PARAMETER, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("partial_aggregate", PARTIAL_AGGREGATE, RESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_agg.h b/src/include/parser/parse_agg.h
index 4df9b37e6d9..58cbad75246 100644
--- a/src/include/parser/parse_agg.h
+++ b/src/include/parser/parse_agg.h
@@ -17,7 +17,7 @@
 
 extern void transformAggregateCall(ParseState *pstate, Aggref *agg,
 								   List *args, List *aggorder,
-								   bool agg_distinct);
+								   bool agg_distinct, bool agg_partial);
 
 extern Node *transformGroupingFunc(ParseState *pstate, GroupingFunc *p);
 
-- 
2.34.1

---

From: Alexander Pyhalov <[email-masked]>
Date: Fri, 29 Mar 2024 17:46:31 +0300

Alexander Pyhalov писал(а) 2024-03-26 14:33:
> table, but convert_combining_aggrefs() will make non-partial copy, 
> which leads to

Hi.

There was an issue in previous patch version - setGroupClausePartial() 
looked at root->parse->groupClause, not at root->processed_groupClause.
Fixed and added test to cover this.

Also denied partial agregates pushdown on server version mismatch. 
Should check_partial_aggregate_support be 'true' by default?

I'm not sure what to do with current grammar - it precludes partial 
distinct aggregates. I understand that it's currently impossible to have 
partial aggregation for distinct agregates -but does it worth to have 
such restriction at grammar level?

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

From 2daccde58cb62a919a44cd7e4654fb1b96e78a95 Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email-masked]>
Date: Fri, 29 Mar 2024 17:29:33 +0300
Subject: [PATCH] Partial aggregates push down SQL keyword v4

---
 contrib/postgres_fdw/deparse.c                |  85 ++-
 .../postgres_fdw/expected/postgres_fdw.out    | 713 ++++++++++++++++--
 contrib/postgres_fdw/sql/postgres_fdw.sql     | 165 +++-
 23 files changed, 1196 insertions(+), 123 deletions(-)

index 8fc66fa11c7..fde0204a4fe 100644
 
 /*

 

 
-	/* Add DISTINCT */
 
 	if (AGGKIND_IS_ORDERED_SET(node->aggkind))
 	{
@@ -3865,9 +3883,10 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
 	Query	   *query = context->root->parse;
 	ListCell   *lc;
 	bool		first = true;
+	PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
 
 	/* Nothing to be done, if there's no GROUP BY clause in the query. */
-	if (!query->groupClause)
+	if (!fpinfo->group_clause)
 		return;
 
 	appendStringInfoString(buf, " GROUP BY ");
 
+		if (fpinfo->remoteversion != PG_VERSION_NUM)
index c355e8f3f7d..b405aa30c1e 100644

 
 SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
  a  | count 
@@ -10118,27 +10168,578 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
  21 |   100
 (6 rows)
 
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+ Limit
+   Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+         Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+         Sort Key: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric)
+               Output: avg(pagg_tab.a), (((pagg_tab.b / 2)))::numeric, 'test'::text, ((pagg_tab.b / 2))
+                           Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), pagg_tab.b
+                           Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p1 GROUP BY 1, 2
+                           Output: ((pagg_tab_1.b / 2)), (PARTIAL avg(pagg_tab_1.a)), pagg_tab_1.b
+                           Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p2 GROUP BY 1, 2
+                           Output: ((pagg_tab_2.b / 2)), (PARTIAL avg(pagg_tab_2.a)), pagg_tab_2.b
+                           Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p3 GROUP BY 1, 2
+(21 rows)
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+         avg         | numeric | test 
+---------------------+---------+------
+ 10.5000000000000000 |       0 | test
+ 10.5000000000000000 |       5 | test
+ 10.5000000000000000 |      10 | test
+ 10.5000000000000000 |      15 | test
+ 10.5000000000000000 |      20 | test
+ 12.5000000000000000 |       1 | test
+ 12.5000000000000000 |       6 | test
+ 12.5000000000000000 |      11 | test
+ 12.5000000000000000 |      16 | test
+ 12.5000000000000000 |      21 | test

 
 
 	/* Get user mapping */

 

 
 
 	/*

 	/*

index 812e7646e16..6ac0c12e598 100644

 
 
 ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
 ANALYZE fpagg_tab_p1;
 ANALYZE fpagg_tab_p2;
 ANALYZE fpagg_tab_p3;
 
 -- When GROUP BY clause matches with PARTITION KEY.
 -- Plan with partitionwise aggregates is disabled
@@ -3150,16 +3169,148 @@ EXPLAIN (COSTS OFF)
 SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
 SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;

 
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
 
 	/*

 
index be4e182869a..d68a0ec15b7 100644
 
+/*
 
 	/*
+	setGroupClausePartial(partial_target, non_group_exprs, root->processed_groupClause, extra);
 
 	/*
 
 	/* clean up cruft */

 		/*

 
 	/*

 		node = (Node *) aggref;
 	}

 
 
+		/*

 
 
 /*

 

-- 
2.34.1

---

From: "[email-masked]" <[email-masked]>
Date: Mon, 27 May 2024 21:30:59 +0000

Hi Mr. Pyhalov.

Sorry for the late reply.
Thank you for your modification and detailed review.
I attach a fixed patch, have been not yet rebased.

Monday, 25 March 2024 16:01 Alexander Pyhalov <[email-masked]>:.
Fixed.

Fixed.

> Its results have changed since last patch. Do they depend on daylight
You are right. In my environment, TimeZone is set to 'PST8PDT'
with which timetz values depends on daylight saving time.
Changed TimeZone to 'UTC' in this test.

> conditions from having qual at all, all filters will be applied on upper
Thank you for your modification.

> 'variable not found in subplan target list' error.
Thanks for the correction as well.
As you pointed out,
the original patch certainly had the potential to cause problems.
However, I could not actually reproduce the problem in cases such as the following.

  Settings:
    t(c1, c2) is a patitioned table whose partition key is c1.
    t1, t2 are patitions of t and are partitioned table.
    t11, t12: partitions of t1 and foreign table of postgres_fdw.
    t21, t22: partitions of t2 and foreign table of postgres_fdw.
  Query:
    select c2 / 2, sum(c1) from t group by c2 / 2 order by 1

If you have a reproducible example, I would like to add it to
the regression test.
Do you have a reproducible example?

> Should check_partial_aggregate_support be 'true' by default?
Could we discuss this point after we determine how to transfer state values?
If we determine this point, we can easly determine whether check_partial_aggregate_support shold be 'true' by default.

> such restriction at grammar level?
If partial aggregation for distinct agregates becomes possible in the future,
I see no problem with the policy of accepting new SQL keywords,
such as "PARTIL_AGGREGATE DISTINCT".

Sincerely yours,
Yuuki Fujii

---

From: Alexander Pyhalov <[email-masked]>
Date: Tue, 28 May 2024 08:45:04 +0300

Hi.

>> which

The fix was to set child_agg->agg_partial to orig_agg->agg_partial in 
convert_combining_aggrefs(), it's already in the patch,
as well as the example - without this fix

-- Check partial aggregate over partitioned table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;

fails with

ERROR:  variable not found in subplan target list
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

---

From: Alexander Pyhalov <[email-masked]>
Date: Tue, 28 May 2024 08:57:39 +0300

[email-masked] писал(а) 2024-05-28 00:30:
> If we determine this point, we can easly determine whether 
> check_partial_aggregate_support shold be 'true' by default.
> 

BTW, there's I have an issue with test results in the last version of 
the patch. Attaching regression diffs.
I have partial sum over c_interval instead of sum(c_interval).

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

diff -U3 /home/leoric/srcs/shardman-backup/shardman/contrib/postgres_fdw/expected/postgres_fdw.out /home/leoric/srcs/shardman-backup/shardman/contrib/postgres_fdw/results/postgres_fdw.out
--- /home/leoric/srcs/shardman-backup/shardman/contrib/postgres_fdw/expected/postgres_fdw.out	2024-05-28 08:48:27.236520098 +0300
+++ /home/leoric/srcs/shardman-backup/shardman/contrib/postgres_fdw/results/postgres_fdw.out	2024-05-28 08:51:24.395704846 +0300
@@ -10438,15 +10438,15 @@
          ->  Foreign Scan
                Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
          ->  Foreign Scan
                Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
          ->  Foreign Scan
                Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
 (15 rows)

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 4 Jun 2024 13:12:04 -0400

> I attach a fixed patch, have been not yet rebased.

I know this patch was discussed at the Vancouver conference.  What are
the open issues?  I know of several:

*  add tests that were requested by Fujii-san and now posted by
   Alexander Pyhalov
*  Where is the documentation?  I know the original patch had some, and
   I improved it, but it seems to be missing.
*  Passes unsafe binary data from the foreign server.

Can someone show me where that last item is in the patch, and why can't
we just pass back values cast to text?

---

From: "[email-masked]" <[email-masked]>
Date: Wed, 5 Jun 2024 00:14:45 +0000

Hi. Bruce, Robert, Tom, Alexander, hackers.

Apologies for the late response. 
Thanks to Bruce, I had the opportunity to have individual discussions with Robert and Tom at PGConf.dev 2024.
I'm truly grateful to Bruce, Robert, and Tom for their time. 
I've attached the presentation slides used during our discussions. 
Below is a summary of our conversations. 
As my English is not perfect, there may be some misunderstandings. 
If so, I apologize and would appreciate any corrections or comments.

[Summary]
Basically, I will make the prototype as the presentation slides.
In specialty, the prototype has the following two limits.
  Limit1. The server versions of the coordinator and the worker match.
  Limit2. Supported built-in aggregate functions are a few subset
    (Ex. avg, sum, count).
    However, there are many build-in aggregate functions in which import and export functions are not necessary.
    (See p.18 in the presentation slides).
    I will support these aggregate functions.

In this prototype, It is necessary for the following requirements.
  Requirement1. Ensure the compatibility even if the server setting(Ex. encodings) of the coordinator and the worker are different. (with Tom)
  Requirement2. Consider appropriate position of the new keyword "PARTIAL_AGGREGATE". (with Robert)
    Existing patch: Before the target expression. Ex. avg(PARTIAL_AGGREGATE c1)
    Ideal: Before the aggregate function. Ex. PARTIAL_AGGREGATE avg(c1)
  Requirement3. Consider to avoid to make the new keyword "PARTIAL_AGGREGATE" become a reserved word. (with Robert)
    In the existing patch, "PARTIAL_AGGREGATE" is a reserved word.

In addition to the summary, 
I will add sufficient document and comments to the next patch as Bruce and Robert said.
And, I will fix the problem as Alexander pointed in the last week.

Sincerely yours,
Yuuki Fujii

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 4 Jun 2024 21:35:46 -0400

> I will add sufficient document and comments to the next patch as Bruce and Robert said.

Great, I am available to help improve the documentation.

---

From: Alexander Pyhalov <[email-masked]>
Date: Wed, 05 Jun 2024 08:19:04 +0300

Bruce Momjian писал(а) 2024-06-04 20:12:
> On Mon, May 27, 2024 at 09:30:59PM +0000, 
> [email-masked] wrote:
>> Hi Mr. Pyhalov.
>> 

Hi.

In finalize_aggregate() when we see partial aggregate with 
peragg->aggref->aggtranstype = INTERNALOID
we call aggregate's serialization function and return it as bytea.

The issue is that this internal representation isn't guaranteed to be 
compatible between servers
of different versions (or architectures?). So, likely, we instead should 
have called some export function for aggregate
and later - some import function on postgres_fdw side. It doesn't matter 
much, what this export function
generates - text, json or some portable binary format,
1) export/import functions should just "understand" it,
2) it should be a stable representation.
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 5 Jun 2024 10:04:29 -0400

> 2) it should be a stable representation.

Okay, so looking at the serialization output functions already defined, I
see many zeros, which I assume means just the base data type, and eight
more:

	SELECT DISTINCT aggserialfn from pg_aggregate WHERE aggserialfn::oid != 0;
	        aggserialfn
	---------------------------
	 numeric_avg_serialize
	 string_agg_serialize
	 array_agg_array_serialize
	 numeric_serialize
	 int8_avg_serialize
	 array_agg_serialize
	 interval_avg_serialize
	 numeric_poly_serialize

I realize we need to return the sum and count for average, so that makes
sense.

So, we need import/export text representation for the partial aggregate
mode for these eight, and call the base data type text import/export
functions for the zero ones when in this mode?

---

From: "[email-masked]" <[email-masked]>
Date: Tue, 11 Jun 2024 11:39:34 +0000

Hi. Bruce.

Sorry for the late. Thank you for comment.

> From: Bruce Momjian <[email-masked]>
> Sent: Wednesday, June 5, 2024 11:04 PM
> > > Can someone show me where that last item is in the patch, and why
> > > can't we just pass back values cast to text?
> >
> > compatible between servers of different versions (or architectures?).
> > So, likely, we instead should have called some export function for
> > aggregate and later - some import function on postgres_fdw side. It
> > doesn't matter much, what this export function generates - text, json
> > or some portable binary format,
> Okay, so looking at the serialization output functions already defined, I see many zeros, which I assume means just the base
> data type, and eight
> more:
> 
> I realize we need to return the sum and count for average, so that makes sense.
> 
> So, we need import/export text representation for the partial aggregate mode for these eight, and call the base data type
> text import/export functions for the zero ones when in this mode?

I think that you are basically right.
But, I think, in a perfect world we should also add an import/export function for the following
two category.

  Category1. Validation Chek is needed for Safety.
    For example, I think a validation check is needed for avg(float4),
     whose transition type is not internal. (See p.18 in [1])
    I plan to add import functions of avg, count (See p.18, p.19 in [1]).
  Category1. Transition type is a pseudo data type.
    Aggregate functions of this category needs to accept many actual data types,
    including user-defined types. So I think that it is hard to implement import/export functions.
    Consequently, I do not plan to support these category. (See p.19 in [1])

Sincerely yours,
Yuki Fujii

--
Yuki Fujii

[1] https://www.postgresql.org/message-id/attachment/160659/PGConfDev2024_Presentation_Aggregation_Scaleout_FDW_Sharding_20240531.pdf

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 11 Jun 2024 15:59:36 +0200

>     Consequently, I do not plan to support these category. (See p.19 in [1])

How about instead of trying to serialize the output of
serialfn/deserialfn, instead we don't use the "internal" type and
create actual types in pg_type for these transtypes? Then we can
simply use the in/out and recv/send functions of those types to
serialize the values of the partial aggregate over the network.
Instead of having to rely on serialfn/deserialfn to be network-safe
(which they probably aren't).

That indeed still leaves the pseudo types. Since non of those
pseudotypes have a working in/recv function (they always error by
definition), I agree that we can simply not support those.

Basically that would mean that any aggregate with a non-internal and
non-pseudotype as a transtype could be used in this multi-node partial
aggregate pushdown.

---

From: "[email-masked]" <[email-masked]>
Date: Wed, 12 Jun 2024 05:27:02 +0000

Hi Jelte,

Thank you for your comment!
> From: Jelte Fennema-Nio <[email-masked]>
> Sent: Tuesday, June 11, 2024 11:00 PM
> How about instead of trying to serialize the output of serialfn/deserialfn, instead we don't use the "internal" type and
> create actual types in pg_type for these transtypes? Then we can simply use the in/out and recv/send functions of those
> types to serialize the values of the partial aggregate over the network.
> Instead of having to rely on serialfn/deserialfn to be network-safe (which they probably aren't).
> 
> That indeed still leaves the pseudo types. Since non of those pseudotypes have a working in/recv function (they always
> error by definition), I agree that we can simply not support those.
> 
> Basically that would mean that any aggregate with a non-internal and non-pseudotype as a transtype could be used in this
> multi-node partial aggregate pushdown.
Could you please clarify what you mean?
Are you referring to:
  Option 1: Modifying existing aggregate functions to minimize the use of internal state values.
  Option 2: Not supporting the push down of partial aggregates for functions with internal state values.
  Option 3: Something other than Option 1 and Option 2.

There are many aggregate functions with internal state values, so if we go with Option 1,
we might need to change a lot of existing code, like transition functions and finalize functions.
Also, I'm not sure how many existing aggregate functions can be modified this way.

There are also many popular functions with internal state values,
like sum(int8) and avg(int8)(see [1]), so I don't think Option2 would be acceptable.

Best regards, Yuki Fujii

--
Yuki Fujii
Information Technology R&D Center, Mitsubishi Electric Corporation

[1] https://github.com/postgres/postgres/blob/REL_16_STABLE/src/include/catalog/pg_aggregate.dat

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Wed, 12 Jun 2024 10:02:17 +0200

>   Option 2: Not supporting the push down of partial aggregates for functions with internal state values.

Basically I mean both Option 1 and Option 2 together. i.e. once we do
option 1, supporting partial aggregate pushdown for all important
aggregates with internal state values, then supporting pushdown of
internal state values becomes unnecessary.

There are indeed 57 aggregate functions with internal state values:

> SELECT count(*) from pg_aggregate where aggtranstype = 'internal'::regtype;
 count
───────
    57
(1 row)

But there are only 26 different aggtransfns. And the most used 8 of
those cover 39 of those 57 aggregates.

> SELECT
    sum (count) OVER(ORDER BY count desc, aggtransfn ROWS BETWEEN
unbounded preceding and current row) AS cumulative_count
    , *
FROM (
    SELECT
        count(*),
        aggtransfn
    from pg_aggregate
    where aggtranstype = 'internal'::regtype
    group by aggtransfn
    order by count(*) desc, aggtransfn
);
 cumulative_count │ count │               aggtransfn
──────────────────┼───────┼────────────────────────────────────────
                7 │     7 │ ordered_set_transition
               13 │     6 │ numeric_accum
               19 │     6 │ int2_accum
               25 │     6 │ int4_accum
               31 │     6 │ int8_accum
               35 │     4 │ ordered_set_transition_multi
               37 │     2 │ int8_avg_accum
               39 │     2 │ numeric_avg_accum
               40 │     1 │ array_agg_transfn
               41 │     1 │ json_agg_transfn
               42 │     1 │ json_object_agg_transfn
               43 │     1 │ jsonb_agg_transfn
               44 │     1 │ jsonb_object_agg_transfn
               45 │     1 │ string_agg_transfn
               46 │     1 │ bytea_string_agg_transfn
               47 │     1 │ array_agg_array_transfn
               48 │     1 │ range_agg_transfn
               49 │     1 │ multirange_agg_transfn
               50 │     1 │ json_agg_strict_transfn
               51 │     1 │ json_object_agg_strict_transfn
               52 │     1 │ json_object_agg_unique_transfn
               53 │     1 │ json_object_agg_unique_strict_transfn
               54 │     1 │ jsonb_agg_strict_transfn
               55 │     1 │ jsonb_object_agg_strict_transfn
               56 │     1 │ jsonb_object_agg_unique_transfn
               57 │     1 │ jsonb_object_agg_unique_strict_transfn
(26 rows)

And actually most of those don't have a serialfn, so they wouldn't be
supported by your suggested approach either. Looking at the
distribution of aggserialfns instead we see the following:

> SELECT
    sum (count) OVER(ORDER BY count desc, aggserialfn ROWS BETWEEN
unbounded preceding and current row) AS cumulative_count
    , *
FROM (
    SELECT
        count(*),
        aggserialfn
    from pg_aggregate
    where
        aggtranstype = 'internal'::regtype
        AND aggserialfn != 0
    group by aggserialfn
    order by count(*) desc, aggserialfn
);
 cumulative_count │ count │        aggserialfn
──────────────────┼───────┼───────────────────────────
               12 │    12 │ numeric_serialize
               24 │    12 │ numeric_poly_serialize
               26 │     2 │ numeric_avg_serialize
               28 │     2 │ int8_avg_serialize
               30 │     2 │ string_agg_serialize
               31 │     1 │ array_agg_serialize
               32 │     1 │ array_agg_array_serialize
(7 rows)

So there are only 7 aggserialfns, and thus at most 7 new postgres
types that you would need to create to support the same aggregates as
in your current proposal. But looking at the implementations of these
serialize functions even that is an over-estimation: numeric_serialize
and numeric_avg_serialize both serialize a NumericAggState, and
numeric_poly_serialize and int8_avg_serialize both serialize a
PolyNumAggState. So probably a we could even do with only 5 types. And
to be clear: only converting PolyNumAggState and NumericAggState to
actual postgres types would already cover 28 out of the 32 aggregates.
That seems quite feasible to do.

So I agree it's probably more code than your current approach. At the
very least because you would need to implement in/out text
serialization functions for these internal types that currently don't
have them. But I do think it would be quite a feasible amount. And to
clarify, I see a few benefits of using the approach that I'm
proposing:

1. So far aggserialfn and aggdeserialfn haven't been required to be
network safe at all. In theory extensions might reference shared
memory pointers with in them, that are valid for serialization within
the same postgres process tree but not outside of it. Or they might
serialize to bytes in a way that does not work across different
bigendian/littleendian systems, thus causing wrong aggregation
results. Never sending results of serialfn over the network solves
that issue.
2. Partial aggregate pushdown across different postgres version could
be made to work by using the in/out functions instead of receive/send
functions, to use the text based serialization format (which should be
stable across versions)
3. It seems nice to be able to get the text representation of all
PARTIAL_AGGREGATE output for debugging purposes. With your approach I
think what currently happens is that it will show a bytea for when
using PARTIAL_AGGREGATE for avg(bigint) directly from psql.
4. In my experience it's easier to get patches merged if they don't
change a lot at once and are useful by themselves. This way you could
split your current patch up into multiple smaller patches, each of
which could be merged separately (appart from b relying on a).
a. Introduce PARTIAL_AGGREGATE syntax for non-internal & non-pseudo types
b. Start using PARTIAL_AGGREGATE for FDW pushdown
c. Convert NumericAggState to non-internal
d. Convert PolyNumAggState to non-internal
e. Use non-internal for string_agg_serialize aggregates
f. Use non-internal for array_agg_serialize
g. Use non-internal for array_agg_array_serialize

P.S. The problem described in benefit 1 could also be solved in your
approach by adding a boolean opt in flag to CREATE AGGREGATE. e.g.
CREATE AGGREGATE ( ..., NETWORKSAFESERIALFUNCS = true)

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 23 Jun 2024 08:23:32 +0000

Hi. Jelte, hackers.

Sorry for the late response.
Thank you for detailed and useful comments.

> internal state values becomes unnecessary.
Understood.
However, there are points that I agree with and others that I don't agree with.

I will show my opinion using one aggregate function avg(int8), whose transtype is internal.

I agree that, in general, any remote server should transmit the state value to the local server using a format whose data type is a native data type and is not serialized, whenever possible. I call this format standard format along with [1].
For avg(int8), I think that it is rational that any remote server transmit the state value to the local server using the format whose data type is _numeric of count and sum.
Before your advice, I plan to use the standard format whose data type is text, like "count=5 sum=13.3". But now, I think that it is rational to use _numeric.
I attached the POC patch(the second one) which supports avg(int8) whose standard format is _numeric type.

However, I do not agree that I modify the internal transtype to the native data type. The reasons are the following three.
1. Generality
I believe we should develop a method that can theoretically apply to any aggregate function, even if we cannot implement it immediately. However, I find it exceptionally challenging to demonstrate that any internal transtype can be universally converted to a native data type for aggregate functions that are parallel-safe under the current parallel query feature. Specifically, proving this for user-defined aggregate functions presents a significant difficulty in my view. 
On the other hand, I think that the usage of export and import functions can theoretically apply to any aggregate functions.

2. Amount of codes.
It could need more codes.

3. Concern about performance
I'm concerned that altering the current internal data types could impact performance.

> results. Never sending results of serialfn over the network solves
I know. In my proposal, the standard format is not seriarized data by serialfn, instead, is text or other native data type.
Just to clarify, I'm writing this to avoid any potential misunderstanding.

> functions, to use the text based serialization format (which should be
Thank you for your advice. I agree that, as mentioned earlier, standard formats should generally use native data types whenever possible.

> using PARTIAL_AGGREGATE for avg(bigint) directly from psql.
I may have caused some misunderstanding.
To clarify and prevent any potential misunderstanding: In my proposal, the standard format does not involve serialized data by serialfn; rather, it utilizes text or other native data types.

> g. Use non-internal for array_agg_array_serialize
I understand. Thank you for advice.
Basically responding to your advice,
for now, I prepare two POC patches.
The first supports case a, currently covering only avg(int4) and other aggregate functions that do not require import or export functions, such as min, max, and count.
The second supports case b and commonly used functions like sum and avg. Currently, it only includes avg(int8).

Best regards, Yuki Fujii

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 23 Jun 2024 08:25:42 +0000

Hi Alexander, hackers.

> Sent: Tuesday, May 28, 2024 2:45 PM
> convert_combining_aggrefs(), it's already in the patch, as well as the example -
> without this fix
I've just realized that you've added the necessary tests. I forgot to respond, my apologies.

> Sent: Tuesday, May 28, 2024 2:58 PM
> BTW, there's I have an issue with test results in the last version of the patch.
> Attaching regression diffs.
I think the difference stems from the commit[1], which add serialization function
to sum(interval). I will fix it.

Best regards, Yuki Fujii

[1] https://github.com/postgres/postgres/commit/519fc1bd9e9d7b408903e44f55f83f6db30742b7

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 24 Jun 2024 11:08:50 +0200

> I attached the POC patch(the second one) which supports avg(int8) whose standard format is _numeric type.

Okay, very interesting. So instead of defining the
serialization/deserialization functions to text/binary, you serialize
the internal type to an existing non-internal type, which then in turn
gets serialized to text. In the specific case of avg(int8) this is
done to an array of numeric (with length 2).

> On the other hand, I think that the usage of export and import functions can theoretically apply to any aggregate functions.

The only thing required when doing CREATE TYPE is having an INPUT and
OUTPUT function for the type, which (de)serialize the type to text
format. As far as I can tell by definition that requirement should be
fine for any aggregates that we can do partial aggregation pushdown
for. To clarify I'm not suggesting we should change any of the
internal representation of the type for the current internal
aggregates. I'm suggesting we create new native types (i.e. do CREATE
TYPE) for those internal representations and then use the name of that
type instead of internal.

> It could need more codes.

I think it would be about the same as your proposal. Instead of
serializing to an intermediary existing type you serialize to string
straight away. I think it would probably be slightly less code
actually, since you don't have to add code to handle the new
aggpartialexportfn and aggpartialimportfn columns.

> I'm concerned that altering the current internal data types could impact performance.

As explained above in my proposal all the aggregation code would
remain unchanged, only new native types will be added. Thus
performance won't be affected, because all aggregation code will be
the same. The only thing that's changed is that the internal type now
has a name and an INPUT and OUTPUT function.

> Just to clarify, I'm writing this to avoid any potential misunderstanding.

Ah alright, that definitely clarifies the proposal. I was looking at
the latest patch file on the thread and that one was still using
serialfn. Your new one indeed doesn't, so this is fine.

> for now, I prepare two POC patches.

Great! I definitely think this makes the review/discussion easier.

> The first supports case a, currently covering only avg(int4) and other aggregate functions that do not require import or export functions, such as min, max, and count.

Not a full review but some initial notes:

1. Why does this patch introduce aggpartialpushdownsafe? I'd have
expected that any type with a non-pseudo/internal type as aggtranstype
would be safe to partially push down.
2. It seems the int4_avg_import function shouldn't be part of this
patch (but maybe of a future one).
3. I think splitting this patch in two pieces would make it even
easier to review: First adding support for the new PARTIAL_AGGREGATE
keyword (adds the new feature). Second, using PARTIAL_AGGREGATE in
postgres_fdw (starts using the new feature). Citus would only need the
first patch not the second one, so I think the PARTIAL_AGGREGATE
feature has merit to be added on its own, even without the
postgres_fdw usage.
4. Related to 3, I think it would be good to have some tests of
PARTIAL_AGGREGATE that don't involve postgres_fdw at all. I also
spotted some comments too that mention FDW, even though they apply to
the "pure" PARTIAL_AGGREGATE code.
5. This comment now seems incorrect:
-    * Apply the agg's finalfn if one is provided, else return transValue.
+    * If the agg's finalfn is provided and PARTIAL_AGGREGATE keyword is
+    * not specified, apply the agg's finalfn.
+    * If PARTIAL_AGGREGATE keyword is specified and the transValue type
+    * is internal, apply the agg's serialfn. In this case the agg's
+    * serialfn must not be invalid. Otherwise return transValue.

6. These errors are not on purpose afaict (if they are a comment in
the test would be good to explain why)

+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ERROR:  could not connect to server "loopback"
+DETAIL:  invalid connection option "partial_aggregate_support"

---

From: "[email-masked]" <[email-masked]>
Date: Mon, 24 Jun 2024 13:03:04 +0000

Hi. Jelte, hackers.

Thank you for your proposal and comments.

> Sent: Monday, June 24, 2024 6:09 PM
> > 1. Generality
> > I believe we should develop a method that can theoretically apply to any
> aggregate function, even if we cannot implement it immediately. However, I find
> it exceptionally challenging to demonstrate that any internal transtype can be
> universally converted to a native data type for aggregate functions that are
> parallel-safe under the current parallel query feature. Specifically, proving this
> for user-defined aggregate functions presents a significant difficulty in my
> view.
> > On the other hand, I think that the usage of export and import functions can
> theoretically apply to any aggregate functions.
> 
> OUTPUT function for the type, which (de)serialize the type to text format. As
> far as I can tell by definition that requirement should be fine for any aggregates
> that we can do partial aggregation pushdown for. To clarify I'm not suggesting
> we should change any of the internal representation of the type for the current
> internal aggregates. I'm suggesting we create new native types (i.e. do CREATE
> TYPE) for those internal representations and then use the name of that type
> instead of internal.
I see. I maybe got your proposal.
Refer to your proposal, for avg(int8), 
I create a new native type like state_int8_avg
with the new typsend/typreceive functions
and use them to transmit the state value, right?

That might seem to be a more fundamental solution
because I can avoid adding export/import functions of my proposal,
which are the new components of aggregate function.
I have never considered the solution.
I appreciate your proposal.

However, I still have the following two questions.

1. Not necessary components of new native types
Refer to pg_type.dat, typinput and typoutput are required.
I think that in your proposal they are not necessary,
so waste. I think that it is not acceptable.
How can I resolve the problem? 

2. Many new native types
I think that, basically, each aggregate function does need a new native type.
For example,
avg(int8), avg(numeric), and var_pop(int4) has the same transtype, PolyNumAggState.
You said that it is enough to add only one native type like state_poly_num_agg
for supporting them, right?

But the combine functions of them might have quite different expectation
on the data items of PolyNumAggState like
the range of N(means count) and the true/false of calcSumX2
(the flag of calculating sum of squares).
The final functions of them have the similar expectation.
So, I think that, responded to your proposal,
each of them need a native data type
like state_int8_avg, state_numeric_avg, for safety.

And, we do need a native type for an aggregate function
whose transtype is not internal and not pseudo.
For avg(int4), the current transtype is _int8.
However, I do need a validation check on the number of the array
And the positiveness of count(the first element of the array).
Responded to your proposal,
I do need a new native type like state_int4_avg.

Consequently, I think that, responded to your proposal, finally
each of aggregate functions need a new native type
with typinput and typoutput.
That seems need the same amount of codes and more catalog data,
right?

> I think it would be about the same as your proposal. Instead of serializing to an
> intermediary existing type you serialize to string straight away. I think it would
> probably be slightly less code actually, since you don't have to add code to
> handle the new aggpartialexportfn and aggpartialimportfn columns.
> 
> As explained above in my proposal all the aggregation code would remain
> unchanged, only new native types will be added. Thus performance won't be
> affected, because all aggregation code will be the same. The only thing that's
> changed is that the internal type now has a name and an INPUT and OUTPUT
> function.
I got it. Thank you.

> Not a full review but some initial notes:
Thank you. I don't have time today, so I'll answer after tomorrow.

Best regards, Yuki Fujii

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 24 Jun 2024 22:49:13 +0200

> and use them to transmit the state value, right?

Yes, that's roughly what I had in mind indeed.

> I appreciate your proposal.

Thank you :)

> How can I resolve the problem?

I think requiring typinput/typoutput is a benefit personally, because
that makes it possible to do PARTIAL_AGGREGATE pushdown to a different
PG major version. Also it makes it easier to debug the partial
aggregate values when using psql/pgregress. So yes, it requires
implementing both binary (send/receive) and text (input/output)
serialization, but it also has some benefits. And in theory you might
be able to skip implementing the binary serialization, and rely purely
on the text serialization to send partial aggregates between servers.

> for supporting them, right?

Yes, correct. That's what I had in mind.

> I do need a new native type like state_int4_avg.

To help avoid confusion let me try to restate what I think you mean
here: You're worried about someone passing in a bogus native type into
the final/combine functions and then getting crashes and/or wrong
results. With internal type people cannot do this because they cannot
manually call the combinefunc/finalfunc because the argument type is
internal. To solve this problem your suggestion is to make the type
specific to the specific aggregate such that send/receive or
input/output can validate the input as reasonable. But this would then
mean that we have many native types (and also many
deserialize/serialize functions).

Assuming that's indeed what you meant, that's an interesting thought,
I didn't consider that much indeed. My thinking was that we only need
to implement send/receive & input/output functions for these types,
and even though their meaning is very different we can serialize them
in the same way.

As you say though, something like that is already true for avg(int4)
today. The way avg(int4) handles this issue is by doing some input
validation for every call to its trans/final/combinefunc (see
int4_avg_accum, int4_avg_combine, and int8_avg). It checks the length
of the array there, but it doesn't check the positiveness of the
count. I think that makes sense. IMHO these functions only need to
protect against crashes (e.g. null pointer dereferences). But I don't
think there is a good reason for them to protect the user against
passing in weird data. These functions aren't really meant to be
called manually in the first place anyway, so if the user does that
and they pass in weird data then I'm fine with them getting a weird
result back, even errors are fine (only crashes are not).

So as long as our input/output & send/receive functions for
state_poly_num_agg handle all the inconsistencies that could cause
crashes later on (which I think is pretty simple to do for
PolyNumAggState), then I don't think we need state_int8_avg,
state_numeric_avg, etc.

> Thank you. I don't have time today, so I'll answer after tomorrow.

Sure, no rush.

---

From: "[email-masked]" <[email-masked]>
Date: Tue, 25 Jun 2024 06:33:07 +0000

Hi Jelte, hackers.

Thank you for explanations.

Actually, I have other tasks about "PARTIAL_AGGREAGATE" keyword
to respond Requirement1 and Requirement2 in the following mail.
https://www.postgresql.org/message-id/TYAPR01MB3088755F2281D41F5EEF06D495F92%40TYAPR01MB3088.jpnprd01.prod.outlook.com

After that tasks, I plan to compare your proposal with mine seriously, with additional POC patches if necessary.

I think that your proposal might seem to be a more fundamental solution.
However, to be honest, so far, I don't perfectly get the benefits and impacts by stopping usage of internal types
instead using a native types, especially on handling memory contexts of existing deserialization functions and
on the amount of codes to be modified or added.
The followings are the answers with the knowledge I have right now.

> Sent: Tuesday, June 25, 2024 5:49 AM
> > 1. Not necessary components of new native types Refer to pg_type.dat,
> > typinput and typoutput are required.
> > I think that in your proposal they are not necessary, so waste. I
> > think that it is not acceptable.
> I think requiring typinput/typoutput is a benefit personally, because that makes it possible to do PARTIAL_AGGREGATE
> pushdown to a different PG major version. Also it makes it easier to debug the partial aggregate values when using
> psql/pgregress. So yes, it requires implementing both binary (send/receive) and text (input/output) serialization, but it also
> has some benefits. And in theory you might be able to skip implementing the binary serialization, and rely purely on the text
> serialization to send partial aggregates between servers.
I see. It seems that adding new natives might make it easier to transmit the state values between local and remote have different major versions.
However, in my opinion, we should be careful to support the case in which local and remote have different major versions,
because the transtype of an aggregate function would may change in future major version due to
something related to the implementation.
Actually, something like that occurs recently, see
https://github.com/postgres/postgres/commit/519fc1bd9e9d7b408903e44f55f83f6db30742b7
I think the transtype of an aggregate function quite more changeable than retype.
Consequently, so far, I want to support the cases in which local and remote have the same major version.
If we try to resolve the limitation, it seems to need more additional codes.

And, I'm afraid that adding typinput/typoutput bothers the developers.
They also have to create a new native types in addition to create their new aggregate functions.
I wonder if this concern might outweigh the benefits for debugging.
And, if skipping send/receive, they have to select only the text representation on
the transmission of the state value. I think it is narrow.

> > You said that it is enough to add only one native type like
> > state_poly_num_agg for supporting them, right?
> 
> > But the combine functions of them might have quite different
> > expectation on the data items of PolyNumAggState like the range of
> > N(means count) and the true/false of calcSumX2 (the flag of
> > calculating sum of squares).
> > So, I think that, responded to your proposal, each of them need a
> > native data type like state_int8_avg, state_numeric_avg, for safety.
> >
> > And, we do need a native type for an aggregate function whose
> > transtype is not internal and not pseudo.
> > However, I do need a validation check on the number of the array And
> > the positiveness of count(the first element of the array).
> here: You're worried about someone passing in a bogus native type into the final/combine functions and then getting
> crashes and/or wrong results. With internal type people cannot do this because they cannot manually call the
> combinefunc/finalfunc because the argument type is internal. To solve this problem your suggestion is to make the type
> specific to the specific aggregate such that send/receive or input/output can validate the input as reasonable. But this
> would then mean that we have many native types (and also many deserialize/serialize functions).
Yes, right.

> Assuming that's indeed what you meant, that's an interesting thought, I didn't consider that much indeed. My thinking was
> that we only need to implement send/receive & input/output functions for these types, and even though their meaning is
> very different we can serialize them in the same way.
> 
> As you say though, something like that is already true for avg(int4) today. The way avg(int4) handles this issue is by doing
> some input validation for every call to its trans/final/combinefunc (see int4_avg_accum, int4_avg_combine, and int8_avg).
> It checks the length of the array there, but it doesn't check the positiveness of the count. I think that makes sense. IMHO
> these functions only need to protect against crashes (e.g. null pointer dereferences). But I don't think there is a good reason
> for them to protect the user against passing in weird data. These functions aren't really meant to be called manually in the
> first place anyway, so if the user does that and they pass in weird data then I'm fine with them getting a weird result back,
> even errors are fine (only crashes are not).
> 
> So as long as our input/output & send/receive functions for state_poly_num_agg handle all the inconsistencies that could
> cause crashes later on (which I think is pretty simple to do for PolyNumAggState), then I don't think we need state_int8_avg,
> state_numeric_avg, etc.
I see. Certainly, it might sufficient that, receive functions perform validation checks to avoid crash,
and combine functions and final functions are responsible for avoiding crash
in the range of values of data items of the native type.

Best regards, Yuki Fujii

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 25 Jun 2024 11:28:03 +0200

> https://www.postgresql.org/message-id/TYAPR01MB3088755F2281D41F5EEF06D495F92%40TYAPR01MB3088.jpnprd01.prod.outlook.com

No problem. I totally think it makes sense to focus on basic
PARTIAL_AGGREGATE first. Which is also why I suggested splitting the
patchset up in multiple patches. That way it's easier to get everyone
aligned on PARTIAL_AGGREGATE behaviour for non-internal transtypes,
which would already be a huge improvement over the current situation
in my opinion.

> After that tasks, I plan to compare your proposal with mine seriously, with additional POC patches if necessary.

Sounds great! To be clear, I'm not sure which proposal is best. I
mainly thought mine seemed interesting because it doesn't require
additional columns. But maybe the benefits that the extra columns in
your proposal brings are worth adding those extra columns.

> If we try to resolve the limitation, it seems to need more additional codes.

Hmm, that's a very good point. Indeed cross-major-version partial
aggregates pushdown would not be fully solved with this yet.

> the transmission of the state value. I think it is narrow.

I kinda agree with this argument. But really this same argument
applies just as well for regular CREATE TYPE. Developers are forced to
implement typinput/typoutput, even though send/receive might really be
enough for their usecase. So in a sense with your proposal, you give
transtypes a special status over regular types: i.e. transtypes are
the only types where only send/receive is necessary.

So that leaves me two questions:
1. Maybe CREATE TYPE should allow types without input/output functions
as long as send/receive are defined. For these types text
representation could fall back to the hex representation of bytea.
2. If for some reason 1 is undesired, then why are transtypes so
special. Why is it fine for them to only have send/receive functions
and not for other types?

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 30 Jun 2024 21:42:19 +0000

Hi hackers.

>     In the existing patch, "PARTIAL_AGGREGATE" is a reserved word.
I considered the above two requirement.
Based on my research, there is no way to use PARTIAL_AGGREGATE in front of a function name without making it a reserved word.

Instead, I can make PARTIAL_AGGREGATE an unreserved word by placing it after the FILTER clause, like avg(c1) FILTER (WHERE c2 > 0) PARTIAL_AGGREGATE, and by marking it as an ASLABEL word like FILTER.
I attached the patch of the method.
If there are no objections, I would like to proceed with the method described above.
I'd appreciate it if anyone comment the method.

I have addressed several comments, though not all of them.

Fixed.

Best regards, Yuki Fujii

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 30 Jun 2024 22:07:26 +0000

> From: Fujii Yuki <[email-masked]>
> Sent: Monday, July 1, 2024 6:42 AM
> Hi hackers.
> 
With this way, I couldn't resolve shift/reduce conflicts.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 5 Jul 2024 13:35:43 -0400

> > +DETAIL:  invalid connection option "partial_aggregate_support"

Is there a reason the documentation is no longer a part of this patch? 
Can I help you keep it current?

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 7 Jul 2024 21:46:31 +0000

Hi Jelte and hackers,

I've reconsidered which of the following two approaches is the best.
  Approach1: Adding export/import functions to transmit state values.
  Approach 2: Adding native types which are equal to state values.

In my mind, Approach1 is superior. Therefore, if there are no objections this week, I plan to resume implementing Approach1 next week. I would appreciate it if anyone could discuss the topic with me or ask questions.

I believe that while Approach1 has the extendability to support situations where local and remote major versions differ, Approach2 lacks this extendability. Additionally, it seems that Approach1 requires fewer additional lines of code compared to Approach2. I'm also concerned that Approach2 may cause the catalog pg_type to bloat.

Although Approach2 offers the benefit of avoiding the addition of columns to pg_aggregate, I think this benefit is smaller than the advantages of Approach1 mentioned above.

Next, I will present my complete comparison. The comparison points are as follows:
  1. Extendability
  2. Amount of codes
  3. Catalog size
  4. Developer burden
  5. Additional columns to catalogs

1. Extendability
I believe it is crucial to support scenarios where the local and remote major versions may differ in the future (see the below).

https://www.postgresql.org/message-id/4012625.1701120204%40sss.pgh.pa.us

Regarding this aspect, I consider Approach1 superior to Approach2. The reason is that:
・The data type of an aggregate function's state value may change with each major version increment.
・In Approach1, by extending the export/import functionalities to include the major version in which the state value was created (refer to p.16 and p.17 of [1]), I can handle such situations.
・On the other hand, it appears that Approach2 fundamentally lacks the capability to support these scenarios.

2. Amount of codes
Regarding this aspect, I find Approach1 to be better than Approach2.
In Approach1, developers only need to export/import functions and can use a standardized format for transmitting state values.
In Approach2, developers have two options:
  Option1: Adding typinput/typoutput and typsend/typreceive.
  Option2: Adding typinput/typoutput only.
Option1 requires more lines of code, which may be seen as cumbersome by some developers.
Option2 restricts developers to using only text representation for transmitting state values, which I consider limiting.

3. Catalog size
Regarding this point, I believe Approach1 is better than Approach2.
In Approach1, theoretically, it is necessary to add export/import functions to pg_proc for each aggregate.
In Approach2, theoretically, it is necessary to add typoutput/typinput functions (and typsend/typreceive if necessary) to pg_proc and add a native type to pg_type for each aggregate.
I would like to emphasize that we should consider user-defined functions in addition to built-in aggregate functions.
I think most developers prefer to avoid bloating catalogs, even if they may not be able to specify exact reasons.
In fact, in Robert's previous review, he expressed a similar concern (see below).

https://www.postgresql.org/message-id/CA%2BTgmobvja%2Bjytj5zcEcYgqzOaeJiqrrJxgqDf1q%3D3k8FepuWQ%40mail.gmail.com

4. Developer burden.
Regarding this aspect, I believe Approach1 is better than Approach2.
In Approach1, developers have the following additional tasks:
  Task1-1: Create and define export/import functions.

In Approach2, developers have the following additional tasks:
  Task2-1: Create and define typoutput/input functions (and typesend/typreceive functions if necessary).
  Task2-2: Define a native type.

Approach1 requires fewer additional tasks, although the difference may be not substantial.

5. Additional columns to catalogs.
Regarding this aspect, Approach2 is better than Approach1.
Approach1 requires additional three columns in pg_aggregate, specifically the aggpartialpushdownsafe flag, export function reference, and import function reference.
Approach2 does not require any additional columns in catalogs.
However, over the past four years of discussions, no one has expressed concerns about additional columns in catalogs.

Best regards, Yuki Fujii

---

From: "[email-masked]" <[email-masked]>
Date: Sun, 7 Jul 2024 21:52:27 +0000

Hi Bruce.

> Can I help you keep it current?

Here are the reasons:
  Reason1. The approach differs significantly from the previous patch that included documentation, the below.
    https://www.postgresql.org/message-id/attachment/152086/0001-Partial-aggregates-push-down-v34.patch
  Reason2. I have two options for transmitting the state value and I'm evaluating which one is optimal.
   One is what I presented you in PGConf.dev2024. The other is Jelte's one.
   He listened to my talk at the conference and gave me some useful comments on hackers. I'm very grateful that.
  Reason3. The implementation and test have been not finished yet.
Regarding Reason 2, I provided my conclusion in the previous message.

My plan for advancing the patch involves the following steps:
  Step1. Decide the approach on transmitting state value.
  Step2. Implement code (including comments) and tests to support a subset of aggregate functions.
    Specifically, I plan to support avg, sum, and other aggregate functions like min and max which don't need export/import functions.
  Step3. Add documentations.

To clarify my approach, should I proceed with Step 3 before Step2?
I would appreciate your feedback on this.

Best regards, Yuki Fujii

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 8 Jul 2024 10:31:08 +0200

> In my mind, Approach1 is superior. Therefore, if there are no objections this week, I plan to resume implementing Approach1 next week. I would appreciate it if anyone could discuss the topic with m…

Honestly, the more I think about this, the more I like Approach2. Not
because I disagree with you about some of the limitations of
Approach2, but because I'd rather see those limitations fixed in
CREATE TYPE, instead of working around these limitations in CREATE
AGGREGATE. That way more usages can benefit. Detailed explanation and
arguments below.

>From my reading, Tom's concern is that different server versions
cannot talk to each other anymore. So as long as this perf
optimization is only enabled when server versions are the same, I
don't think there is a huge problem if we never implement this.
Honestly, I even think making this optimization opt-in at the FDW
server creation level would already solve Tom's concert. I do agree
that it would be good if we could have cross version partial
aggregates though, so it's definitely something to consider.

> ・On the other hand, it appears that Approach2 fundamentally lacks the capability to support these scenarios.

Approach 2 definitely has some cross-version capabilities, e.g.
jsonb_send includes a version. Such an approach can be used to solve a
newer coordinator talking to an older worker, if the transtypes are
the same.

I personally don't think it's worth supporting this optimization for
an older coordinator talking to a newer worker. Using binary protocol
to talk to from an older server to a newer server doesn't work either.

Finally, based on p.16 & p.17 it's unclear to me how cross-version
with different transtypes would work. That situation seems inherently
incompatible to me.

> Option2 restricts developers to using only text representation for transmitting state values, which I consider limiting.

In my opinion this is your strongest argument for Approach1. But you
didn't answer my previous two questions yet:

> special. Why is it fine for them to only have send/receive functions

Basically: I agree with this argument, but I feel like this being a
problem for this usecase is probably a sign that we should take the
solution a step further and solve this at the CREATE TYPE level
instead of allowing people to hack around CREATE TYPE its limitations
just for these partial aggregates.

> https://www.postgresql.org/message-id/CA%2BTgmobvja%2Bjytj5zcEcYgqzOaeJiqrrJxgqDf1q%3D3k8FepuWQ%40mail.gmail.com

So, to summarize the difference (assuming we change CREATE TYPE to
allow only typsend/typreceive): "Approach 2 adds an additional pg_type
entry per aggregate"

IMHO this is fine, especially since these types can usually be shared
across multiple aggregates. I think the main reason Robert expressed
concern before was the level of catalog bloat that was added in that
version of the proposal: It required a new function to be added to
every existing aggregate.

Both Approach1 and Approach2 only require new catalog entries to be
added for aggregates with internal transtypes. That means both
approaches introduce significantly less bloat than the proposal that
Robert expressed concern about.

> Approach1 requires fewer additional tasks, although the difference may be not substantial.

I think the difference here is so small that this isn't really an
argument. Especially since you're skipping over Task-1-2: Use new
export/import functions in aggregate definition.

Which means the effective difference in SQL that needs to be typed is
really "CREATE TYPE mytype" (the arguments of the CREATE TYPE, would
be part of the aggregate definition in Approach1). In a sense this
same counter-argument applies to the catalog bloat section. The only
extra data that gets to the catalog for Approach2 is the typename and
typeoid, the export/import vs send/receive functions just move between
pg_type and pg_aggregate.

> However, over the past four years of discussions, no one has expressed concerns about additional columns in catalogs.

I agree that the columns aren't a big deal. The main thing I don't
like is that now we have two ways of serializing types to be sent over
the network, one way for actual types and one way for transtypes of
type internal.

FINALLY: I thought of one other advantage of using actual types is
that at the protocol level these types will be visible too. This
allows for some extra safety checks to be performed on the coordinator
in case the transtypes are not the same across servers, which might
happen with aggregates of extensions even if the PG server versions
are the same, but the extension versions aren't. With Approach1 the
only way to detect this is the importfunc complaining, but that can
only fail with a vague error, like "cannot parse message". Instead of
saying something more useful, like "expected type avg_int4_transtype,
got type avg_int4_transtype_v2"

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 8 Jul 2024 10:58:41 +0200

> I'd appreciate it if anyone comment the method.

I like this approach of using PARTIAL_AGGREGATE in the same place as
the FILTER clause.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 8 Jul 2024 10:59:31 +0200

> To clarify my approach, should I proceed with Step 3 before Step2?

(my opinion, Bruce might have a different one)

I think it's good that you split the original patch in two:
0001: non-internal partial aggregates
0002: internal partial aggregates

I think we're aligned on the general design of 0001. So I think now is
definitely the time to include documentation there, so we can discuss
this patch in more detail, and move it forward.

I think generally for 0002 it would also be useful to have
documentation, I personally like reading it to understand the general
design and then comparing that to the code. But I also understand that
the language differences between Japanese and English, makes writing
such docs a significant effort for you. So I think it would be fine to
skip docs for 0002 for now until we decide on the approach we want to
take for internal partial aggregates.

---

From: "[email-masked]" <[email-masked]>
Date: Mon, 8 Jul 2024 12:11:46 +0000

Hi Jelte,

Thank you for comments and advises.

> Sent: Monday, July 8, 2024 5:31 PM
> > In my mind, Approach1 is superior. Therefore, if there are no objections this week, I plan to resume implementing
> Approach1 next week. I would appreciate it if anyone could discuss the topic with me or ask questions.
> 
> Honestly, the more I think about this, the more I like Approach2. Not because I disagree with you about some of the
> limitations of Approach2, but because I'd rather see those limitations fixed in CREATE TYPE, instead of working around
> these limitations in CREATE AGGREGATE. That way more usages can benefit. Detailed explanation and arguments below.
Firstly, I may have jumped to conclusions too quickly. I apologize that.
I would appreciate it if we clarify Approach 1 and Approach 2 more precisely and we can proceed with the discussion.

Before we get into the details, let me break down the main differences between Approach 1 and Approach 2.

The best thing about Approach2 is that it lets us send state values using the existing data type system.
I'm worried that if we choose Approach2, we might face some limits because we have to create new types.
But, we might be able to fix these limits if we look into it more. 

Approach1 doesn't make new types, so we can avoid these limits.
But, it means we have to make export/import functions that are similar to the typsend/typreceive functions.
So, we need to make sure if we really need this method.

Is this the right understanding?

> Sent: Monday, July 8, 2024 5:59 PM
> > Instead, I can make PARTIAL_AGGREGATE an unreserved word by placing it after the FILTER clause, like avg(c1) FILTER
> (WHERE c2 > 0) PARTIAL_AGGREGATE, and by marking it as an ASLABEL word like FILTER.
> I like this approach of using PARTIAL_AGGREGATE in the same place as the FILTER clause.
Thank you for comment.

> I think we're aligned on the general design of 0001. So I think now is definitely the time to include documentation there, so
> we can discuss this patch in more detail, and move it forward.
> 
> I think generally for 0002 it would also be useful to have documentation, I personally like reading it to understand the
> general design and then comparing that to the code. But I also understand that the language differences between Japanese
> and English, makes writing such docs a significant effort for you. So I think it would be fine to skip docs for 0002 for now
> until we decide on the approach we want to take for internal partial aggregates.
At least for 0001, it seems like it would be a good idea to attach a document at this stage.

Best regards, Yuki Fujii

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 15 Aug 2024 14:48:42 -0400

> I would appreciate your feedback on this.

Thanks, I now understand why the docs were remove, and I agree.  I will
post about the options now in a new email.