From: "Fujii.Yuki@df.MitsubishiElectric.co.jp"
	<Fujii.Yuki@df.MitsubishiElectric.co.jp>
To: Bruce Momjian <bruce@momjian.us>, Alexander Pyhalov
	<a.pyhalov@postgrespro.ru>, PostgreSQL-development
	<pgsql-hackers@postgresql.org>
CC: Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Tomas
 Vondra <tomas.vondra@enterprisedb.com>, Julien Rouhaud <rjuju123@gmail.com>,
	Daniel Gustafsson <daniel@yesql.se>, Ilya Gladyshev
	<i.gladyshev@postgrespro.ru>, "Fujii.Yuki@df.MitsubishiElectric.co.jp"
	<Fujii.Yuki@df.MitsubishiElectric.co.jp>
Subject: RE: Partial aggregates pushdown
Thread-Topic: Partial aggregates pushdown
Thread-Index:
 AQHYPYasTfJpCf0a4028YJm7JfY5bazLVbIAgAA/QgCAzsLGAICupJqAgAOhGYCAC47OQIAAf6sAgAAdMNCAAEyGgIAAtjrAgAD/KACABTrjIIAEWzOAgAyCOHCApWPpgIAAT1AggArXpoCAAFPI0IABO+iAgAMFFICABR/vAIAACpQAgABDk4CAAU/RgIBJ2XYAgAgC/oCAAC5WgIAGnISAgAQxEbA=
Date: Mon, 12 Jun 2023 08:51:30 +0000
Deferred-Delivery: Mon, 12 Jun 2023 08:51:00 +0000
Message-ID:
 <OS3PR01MB6660B7C247726BC0E36DF4D69554A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
References:
 <OSZPR01MB6662875933E87F37C711B4FC95969@OSZPR01MB6662.jpnprd01.prod.outlook.com>
 <ZDDIU2fJxZm/k5kj@momjian.us>
 <OS3PR01MB66606C1DA960336516D9A08395959@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZDedXNarI32SmN06@momjian.us> <ZDemPMLOyXNzKkhw@momjian.us>
 <OS3PR01MB6660EBFFFA06646F402813A395989@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZDj4nwJANUU+kam8@momjian.us>
 <OS3PR01MB66607DC466F7F3B3909DA3CA954EA@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <8ffedecae0d21a3cd93805baf1dcc0de@postgrespro.ru>
 <OS3PR01MB66604E404B003A621B0C03A69552A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZINW022iuYzf2ZwO@momjian.us>
In-Reply-To: <ZINW022iuYzf2ZwO@momjian.us>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-2022-jp?B?Ry9HeFBTZEdPdnh4UDVCazVpb3BCREltRG9SaXUwSzNWWW5EM1ZCT3hC?=
 =?iso-2022-jp?B?TW1Fc2ZkTlNXNlMrS2tVWHkvY2lKS2FwQXBRY0RCd25wMEdYNFhKMGhM?=
 =?iso-2022-jp?B?MjZyRVdtZXlyaDhKci83UitxcUM0b0U5SjNSb1hzc2JibjFjb3NObFFx?=
 =?iso-2022-jp?B?SDRPclU5KzBYeExXNlM3NkhkVUREZTdiakt0ZnVBRVE1ZXpINzkvNzcv?=
 =?iso-2022-jp?B?SnptUVFLYUZ4ektTREhFRzR4RTBDOWdRcGtGdS9XSlA0a01lN09VNy9P?=
 =?iso-2022-jp?B?VUdUdXFRU3ZnQlQ5YVBsQVRvYlRvd21iNzJHbmFFUEZRSDYzNjJRV0RF?=
 =?iso-2022-jp?B?Y25RUW56S0dqdmF5UnYzdElZdnZGU29kaUlsby9KZzdMemhpSmNzR2Mx?=
 =?iso-2022-jp?B?TTBQSC9RUWxCWEx4VGIyTEpFTmUwUG82TENnQU9nZmN2VXlxYXlkTGo1?=
 =?iso-2022-jp?B?YlFvazczakFMWnphUk9BNHRSaVZNTUliQzZMT2VRZm9FNUdBRjVQTmJP?=
 =?iso-2022-jp?B?dEFKRnJ1MUtrZjN0eGdGSzRRSEwvTmRWSTg0YVNmUXNyckZYZFhyQ0tr?=
 =?iso-2022-jp?B?WDJXRHhMd2pJZHpHZXB5ME52eCt2SnV0OXdEVEUzeExvZ3crK1FRcU4y?=
 =?iso-2022-jp?B?T1M2U3FOU0RuMHBBZ2RRMHNGUW95Zm55cFYzRjZldXY3M3ZxaW9Sa0hp?=
 =?iso-2022-jp?B?UXFKalZGVEFHWTJqMEFhQjZhMk5NUWxQU2NzUktLOUlObEFaQ1o2Ylo1?=
 =?iso-2022-jp?B?TTFqd01nRHZtVExwek5GUUZMUGQ0eGwwNWNKMGtVNzVLejB6UVZuRHRk?=
 =?iso-2022-jp?B?Tm9ZaUs3aktRcmFPNlIzaS9CSUVrRGdyM2Mrb2N2WjdiZXcyRlltejZM?=
 =?iso-2022-jp?B?ZXNuSGxTQmVXTDJ1L1pRek0ycWFKeTFSNU9ET292RkV1VFIzN21wbW4r?=
 =?iso-2022-jp?B?Z1k5RVJsbVNJK0p0VjhrL09kdGpWS2VSUzVvVUphZEVNbnJtb2xtOTlS?=
 =?iso-2022-jp?B?MFRsaG8rMTVkTlJJY3FXTHJmWEtYZzhOVG02UlV3Z1FpWkVldzE3aFFy?=
 =?iso-2022-jp?B?TTJnK044YnRGREI2bXhlTEJSQ1V0RGJBVDRKaThTN1NHN2dYN1BINVJ0?=
 =?iso-2022-jp?B?L3RmZThVZDlDMkVhdEtldkFSZWc4WGFMWGpTZDlXQ1pmNFlKWlBrN0Zz?=
 =?iso-2022-jp?B?NUs3MUgxS1p4dEhqQzFZUDdUODFlSUxzYkt4U3c1Z0FpaVlMSDdnWE9K?=
 =?iso-2022-jp?B?OUp5NXNFK1JCZ3dVcldXRzRCTVVKNEM2c1V5Mm1yODJ6elh0NnNtSEVB?=
 =?iso-2022-jp?B?czdoTm9YTHRrYzF2RVpQbENjRVFjTTJRRk1COVRiaVhEUG1oRVlPY2dD?=
 =?iso-2022-jp?B?S1pIam9JOEpBUnRBMkFJb1k5YTNsNmJGYUErd0xVYk5CYVg4R2VhTnZF?=
 =?iso-2022-jp?B?aTV1YitpOWFLUWtCZlNWTXM2YlI0ME42aXFYZ2JWWHdRNFdEY2ZmNGNY?=
 =?iso-2022-jp?B?aHlGOW9rZHl0UjE4QWxMdmFBdGlyKzloMUNyQ1RIYmdteU1pWGtsVi9F?=
 =?iso-2022-jp?B?RHhUcmVzTXpDZmxpM0NBYWNIWExZc1ovQ1lWcXFWSmMyVDA1c2doUGlE?=
 =?iso-2022-jp?B?alRoVlVnK1k4TlVvUTZhck5kT1pNVlBqTWEyNlB1eU9MalcwM2l6Z0pV?=
 =?iso-2022-jp?B?dUtBWGswaXVwUWc5ZEhzcXJJRit5QmRaeGlLV2FheHRmRk96NVBxbEdR?=
 =?iso-2022-jp?B?ZTRpbWtBQlE1TmZ4a0RZYkdhaS9LVEtlT3FPUGZGa3U5Y09KeHRWVldk?=
 =?iso-2022-jp?B?REpUbVJHdjIzbVFYZE9Cb2dWVkQ0UWN4WVFTVnQvWFlMb3VOUUJYZGFV?=
 =?iso-2022-jp?B?TCtqYlRKcVYzNWx0eDJLZk15bnFLSGs2K3k5Q3Y0djNmNG5Jd3VreGtX?=
 =?iso-2022-jp?B?Y040dHlrZE1pRG52K3UrMG1YWmRndjZzTXdHaWkrWGUra3l3bHdocFpx?=
 =?iso-2022-jp?B?T2FuZVd5Rng2cm1DUzZYeDBxbjJsV0g4cmU1RC8ycjVmazh2K3dic3lD?=
 =?iso-2022-jp?B?QnJRalI2SkhCQmZrUVRqYmhxdVIwTXBKVlFQeVU2eUNtSmRnYnhXWG95?=
 =?iso-2022-jp?B?YnVORUNUTG1ITkl5bGEvMndWNksybGcrVnNaRTk4Q3pNQmJlWi9qT2Y3?=
 =?iso-2022-jp?B?VTlvQk5rRVBsclZ6cms2Lzg3SEp6c0N5NXR4eC9SSDUwa1IrUWlvUmNp?=
 =?iso-2022-jp?B?dlp1cGJaVlpyR2RacFNhcjZlU3FBSHRINUlaTS96L2ZJWndueGRCc0pU?=
 =?iso-2022-jp?B?RzlUZA==?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: df.MitsubishiElectric.co.jp
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: OS3PR01MB6660.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: b74740ef-343e-48c4-2bde-08db6b2248dd
X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Jun 2023 08:51:59.8115
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c5a75b62-4bff-4c96-a720-6621ce9978e5
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: XTOtWNM7T5tDJxmetFlGZRAGT8xAnM20opFX20OB1cSFCR/YOB5OMZzubkK+4iRL+5SWrVgw3VazH+iyZmyntg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS3PR01MB10325
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/OS3PR01MB6660B7C247726BC0E36DF4D69554A%40OS3PR01MB6660.jpnprd01.prod.outlook.com>
Precedence: bulk

Hi Mr.Bruce, Mr.Pyhalov, hackers.

Thank you for comments. I will try to respond to both of your comments as f=
ollows.
I plan to start revising the patch next week. If you have any comments on t=
he following
respondences, I would appreciate it if you could give them to me this week.

> From: Bruce Momjian <bruce@momjian.us>
> Sent: Saturday, June 10, 2023 1:44 AM
> I agree that this feature is designed for built-in sharding, but it is po=
ssible people could be using aggregates on partitions
> backed by foreign tables without sharding.  Adding a requirement for non-=
sharding setups to need PG 17+ servers might
> be unreasonable.
Indeed, it is possible to use partial aggregate pushdown feature for purpos=
es other than sharding.
The description of the section "F.38.6. Built-in sharding in PostgreSQL" as=
sumes the use of
Built-in sharding and will be modified to eliminate this assumption.
The title of this section should be changed to something like "Aggregate on=
 partitioned table".

> From: Bruce Momjian <bruce@momjian.us>
> Sent: Saturday, June 10, 2023 1:44 AM
> Looking at previous release note incompatibilities, we don't normally cha=
nge non-administrative functions in a way that
> causes errors if run on older servers.  Based on Alexander's observations=
, I wonder if we need to re-add the postgres_fdw
> option to control partial aggregate pushdown, and default it to enabled.
>=20
> If we ever add more function breakage we might need more postgres_fdw opt=
ions.  Fortunately, such changes are rare.

I understand what the problem is. I will put a mechanism maintaining compat=
ibility into the patch.
I believe there are three approaches.
Approach 1-1 is preferable because it does not require additional options f=
or postgres_fdw.
I will revise the patch according to Approach 1-1, unless otherwise comment=
ed.

Approach1:
I ensure that postgres_fdw retrieves the version of each remote server
and does not partial aggregate pushd down if the server version is less tha=
n 17.
There are two approaches to obtaining remote server versions.
Approach1-1: postgres_fdw connects a remote server and use PQserverVersion(=
).
Approach1-2: Adding a postgres_fdw option about a remote server version (li=
ke "server_version").

Approach2:
Adding a postgres_fdw option for partial aggregate pushdown is enable or no=
t
(like enable_partial_aggregate_pushdown).

Sincerely yours,
Yuuki Fujii

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