From: "Fujii.Yuki@df.MitsubishiElectric.co.jp"
	<Fujii.Yuki@df.MitsubishiElectric.co.jp>
To: Bruce Momjian <bruce@momjian.us>
CC: Alexander Pyhalov <a.pyhalov@postgrespro.ru>, PostgreSQL-development
	<pgsql-hackers@postgresql.org>, 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/oCAAC5WgIAGnISAgAQxEbCAAFHVgIAA0HSA
Date: Tue, 13 Jun 2023 02:18:15 +0000
Deferred-Delivery: Tue, 13 Jun 2023 02:18:13 +0000
Message-ID:
 <OS3PR01MB666038A12CE1F9D81785D27E9555A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
References:
 <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>
 <OS3PR01MB6660B7C247726BC0E36DF4D69554A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZIcfn8pnO4t29O5g@momjian.us>
In-Reply-To: <ZIcfn8pnO4t29O5g@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?K0k3SHIzZVB6VGN5aUlxYWlYcFBCMWFzMnk2SzRMdWpJSmRSaHVYYktD?=
 =?iso-2022-jp?B?a1JlcjRBZU42azhmMFhRY09NUWRDZG5iSUxmWEZ4L2plS3hIeHdaVEZB?=
 =?iso-2022-jp?B?WGRaL0lUMGZ5SDZmNklLdDhRV0J4V0ROMUlZTVJyTTFTZ204WEM0QmNk?=
 =?iso-2022-jp?B?ZFg1ZGVHOEZSVC81c3ZEcVF1Q0c3OCtMdUVseW1QbCtQQlBibW9MMGo5?=
 =?iso-2022-jp?B?SzBSMmJBN0JYeDJ4am8xOUtqZlI0MkhHZXJDd3BUSzFRakZPSkhzbDNL?=
 =?iso-2022-jp?B?bkhLUm1DSXpvY3NMVE5ndFZwaHNZcndOdGdIM2pMOWhUaHZxV1YzTytT?=
 =?iso-2022-jp?B?ZXh2dzBkeGRoSFpNeFpmOERqcmJlcGt3UmdkWlNMZXRTakNUcFU5RzN1?=
 =?iso-2022-jp?B?aHN6MDc0UjJOeTUvaW1QeTNnT2tmUkVoWG53TVRCcXgwY3NZdlp0NWNF?=
 =?iso-2022-jp?B?QTBNMXEwOVVwaitZaFlCanMvajRoVE0zVnZaT3Q2UmhkSVVYSnpHdm10?=
 =?iso-2022-jp?B?SDd6d2VxSHlXS1ZPNmdqK25mRGIvcFVqK3p0NFlzWmZUdTdmVXd4aEpi?=
 =?iso-2022-jp?B?aTRZRWI4WjZUb0NJbldWZWxmNE5DQ0FwQUZEQWJSMUM2MDlReHgyYU9i?=
 =?iso-2022-jp?B?MWZyWUx2SVNwODNydnAvcWpsTGpDTWI1TTVYN0M5SEsxUklMaEFabmFY?=
 =?iso-2022-jp?B?bUtmYnJSYk45Wkl0RDZ6Z1lha2t6SVdpU3dlSDhGLzVsekFucUlYZG9p?=
 =?iso-2022-jp?B?bjVHdGN5WlVJUjJNeEptOFE2czN5aUt0a3VuTnlIT3FDYmg1aXdNb1ND?=
 =?iso-2022-jp?B?aXJWaWZNRmpWQm04cDJZZTZxV2F2NGp6cUdjeHM2M3F4SnV1ZlVoTGFT?=
 =?iso-2022-jp?B?MyttM2h1SWR0TVlkNXlvN3dBV3l2alVOOG5SeUNmcWtCRzNiQXNEV2h2?=
 =?iso-2022-jp?B?bktLTkRzVm5meXRhVmZOYkdMRlAvV0JRTCtUVVdSMytWYVJWZ0xONzVu?=
 =?iso-2022-jp?B?TktDeWgxSHpmSjNuMTdFS3JXQlo1K2NTSldkSHVseFlDc2t0eVhRYkp5?=
 =?iso-2022-jp?B?aG4vUU1pZWdkaUFzdHhxVlY2MHFUT29wODkrNytWNWNaekdoWW8zbzly?=
 =?iso-2022-jp?B?Sm9tNm5lM3YxU0xTNDJyMjFLNnhHWlFmY0dnK2V1Y2hqUHcyNXNUVmZl?=
 =?iso-2022-jp?B?NXdvTVZiR2xiV2F6U2dDZ3RmcnBNY3Yzc2R6ZU9NNkd5NW16SURJdEh1?=
 =?iso-2022-jp?B?dkJMUFlpYVdkVFJoaS8rdDVlMkdqN0VWSEFyZGlVa1dxM3liMjBubExn?=
 =?iso-2022-jp?B?ZGt5R1Z1bDhka3ViTGMrOGF3ZDNMb2t1Z0Zha1NyenE0M3FkU25HUGs4?=
 =?iso-2022-jp?B?S3NNNjJQUXU5WlMwOXlrQktBN2s1T3NuQzc0SGJOMlU4OHRqemlRNjR5?=
 =?iso-2022-jp?B?ZXE5ZWoxMXByek02UnBSV2pSMVZwaXdLZGp6SFYzUmhoNDNRUTlnUVMy?=
 =?iso-2022-jp?B?VUUrd0R2bTdnb2xMOENTMGVURis3bkVCd1hHUGNVZGhhd1p4OThoa01N?=
 =?iso-2022-jp?B?N1NBSDRxVUhZcUR6Z2VlcG1PR1VpUXU0aWFxRndsbEZ3VXZzYmVmZ25C?=
 =?iso-2022-jp?B?VG1Bb2w2R3hxZksyVC91T3FNVkdGTlFkNFU5cGVPRzQzYWx4N1BCaFIw?=
 =?iso-2022-jp?B?U25QZ1dlakd0Q2lMbzlZUXhoZkZPNE9kc1k2QWNqaitESE90Z1RMcDdx?=
 =?iso-2022-jp?B?Y2lzRVFTRU5FUG14WGp4V0ZnbitZeUwwd2F2ak5TL2lqUll1cDlJMDQ5?=
 =?iso-2022-jp?B?UC9qaFMwN3BKRDFDUWpKb2xZWGYxdElmRWdZbUpqYmhxMFloVkRsbUdZ?=
 =?iso-2022-jp?B?MWFkOW9ZdHRPY0FwSEplMUxVWlIvdnI4ZEtFOW0yUUhNVUFkTGhHbnVR?=
 =?iso-2022-jp?B?TDVWWmROeDl5RmpBenltUkkrZHhySE5FVW1rV3JhR0ozbkE4Y0R1c0Ro?=
 =?iso-2022-jp?B?b3pnQ21sQW9JRUkzdmZXQW0wM3VBTXY2ckJKWkg1b3NTMVIwVUNkRVJl?=
 =?iso-2022-jp?B?RVUzZit6c0lPVlVWVTg2SzNCQVk3TVN5bVN3a0g5ajJYMElPcVpDTkVj?=
 =?iso-2022-jp?B?QWFWK3oxNlVxZGhsQWVYbDNoSng5N3pDWEpOWjJNMXFkUWhqdVF4K3pQ?=
 =?iso-2022-jp?B?bXFyTGw2bXZlVU1CcTVFUHhELzFTVERBTTFsUEZYUXdxR2ZzblBhdU1O?=
 =?iso-2022-jp?B?bVQ4cEE5eGNYUGFuS083dGIxeWVTSVFhL3RxQ0k0aEVIR1VVWk5SRFA5?=
 =?iso-2022-jp?B?dFhVeQ==?=
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: 1fe45844-06a3-421c-ac06-08db6bb47b4f
X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Jun 2023 02:18:30.9864
 (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: TmmfH9cju7UxglYaprU5C5UzfXfryTt66PWI2PmoaAFyGYMFrkYvKMYl9NQncKx4BTaBcN3I1q/3TJC/oiwkVA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYWPR01MB8268
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/OS3PR01MB666038A12CE1F9D81785D27E9555A%40OS3PR01MB6660.jpnprd01.prod.outlook.com>
Precedence: bulk

Hi Mr.Momjian.

Thank you for advises.

> From: Bruce Momjian <bruce@momjian.us>
> Sent: Monday, June 12, 2023 10:38 PM
> > I understand what the problem is. I will put a mechanism maintaining co=
mpatibility into the patch.
> > I believe there are three approaches.
> > Approach 1-1 is preferable because it does not require additional optio=
ns for postgres_fdw.
> > I will revise the patch according to Approach 1-1, unless otherwise com=
mented.
> >
> > 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=
 than 17.
> > There are two approaches to obtaining remote server versions.
> > Approach1-1: postgres_fdw connects a remote server and use PQserverVers=
ion().
> > Approach1-2: Adding a postgres_fdw option about a remote server version=
 (like "server_version").
> >
> > Approach2:
> > Adding a postgres_fdw option for partial aggregate pushdown is enable
> > or not (like enable_partial_aggregate_pushdown).
>=20
> These are good questions.  Adding a postgres_fdw option called enable_par=
tial_aggregate_pushdown helps make the
> purpose of the option clear, but remote_version can be used for future br=
eakage as well.
>=20
> I think remote_version is the best idea, and in the documention for the o=
ption, let's explcitly say it is useful to disable
> partial aggreates pushdown on pre-PG 17 servers.  If we need to use the o=
ption for other cases, we can just update the
> documentation.  When the option is blank, the default, everything is push=
ed down.
>=20
> I see remote_version a logical addition to match our "extensions" option =
that controls what extension functions can be
> pushed down.

Thank you for your perspective.
So, of the approaches I have presented, you think that approach 1-2 is
preferable and that the option name remote_server is preferable?
Indeed, the option of a remote version may have other uses.
However, this information can be obtained by connecting to a remote server,=
=20
I'm concerned that some people may find this option redundant.

Is the problem with approach 1-1 because the user cannot decide whether to =
include the compatibility check in the decision to do partial aggregate pus=
hdown or not?
# If Approach 1-1 is taken, the problem is that this feature cannot be used=
 for all buit-in aggregate functions
# when the remote server is older than PG17.
If so, Approache1-3 below seem more desirable.
Would it be possible for us to hear your thoughts?

Approache1-3=1B$B!'=1B(BWe add a postgres_fdw option about a compatibility =
check for partial aggregate pushdown
(like "enable_aggpartialfunc_compatibility_check"). This option is false, t=
he default.
When this option is true, postgres_fdw obtains the remote server version by=
 connecting the remote server and using PQserverVersion().=20
And if the remote server version is older than PG17, then the partial aggre=
gate pushdown feature is enable for all buit-in aggregate functions.
Otherwise the partial aggregate pushdown feature is disable for all buit-in=
 aggregate functions.

Sincerely yours,
Yuuki Fujii

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