From: "kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com>
To: 'Kyotaro Horiguchi' <horikyota.ntt@gmail.com>
CC: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>,
	"Shinya11.Kato@oss.nttdata.com" <Shinya11.Kato@oss.nttdata.com>,
	"zyu@yugabyte.com" <zyu@yugabyte.com>, "masao.fujii@oss.nttdata.com"
	<masao.fujii@oss.nttdata.com>
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
Thread-Topic: [Proposal] Add foreign-server health checks infrastructure
Thread-Index:
 AdfNNjec7xk22WznTt+52qg+YpAmvANdE8aAAG+tXeAALaEGgADyvFSgAOXdLoAAHbFoIAAJMQQAAVzrX4ABVSpkgAcm+qGAAFKrs2AALpHaMABlDPQAAZXPwNAAQjmEAALZMWcAADGlIkA=
Date: Thu, 17 Feb 2022 04:11:09 +0000
Message-ID:
 <TYAPR01MB58661B088B6066282824ADD9F5369@TYAPR01MB5866.jpnprd01.prod.outlook.com>
References: <0e9ea094-67df-487c-96f5-f9eabb5357c7@oss.nttdata.com>
	<TYAPR01MB5866D5297140DA5908ECFD27F5269@TYAPR01MB5866.jpnprd01.prod.outlook.com>
	<be053d3d-b195-0852-4c29-0c67bb640b48@oss.nttdata.com>
 <20220216.115058.1615761004338301624.horikyota.ntt@gmail.com>
In-Reply-To: <20220216.115058.1615761004338301624.horikyota.ntt@gmail.com>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
msip_labels: MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Enabled=true;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SetDate=2022-02-17T02:33:07Z;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Method=Standard;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Name=FUJITSU-RESTRICTED?;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SiteId=a19f121d-81e1-4858-a9d8-736e267fd4c7;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ActionId=95b0c8aa-0156-4220-bc05-d0bb355804ce;
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ContentBits=0
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=fujitsu.com;
x-ms-publictraffictype: Email
x-ms-office365-filtering-correlation-id: 36f89089-52d7-4638-2023-08d9f1cb86e2
x-ms-traffictypediagnostic: OS3PR01MB5591:EE_
x-microsoft-antispam-prvs:
 <OS3PR01MB559103501CD60A631D93D0A2F5369@OS3PR01MB5591.jpnprd01.prod.outlook.com>
x-ms-oob-tlc-oobclassifiers: OLM:8273;
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;
x-microsoft-antispam-message-info:
 BXiq4UK9zu/upm5TzL6oiJGp96rUwRnpzK+eZXsWpg6BiVzh+Zqpp7uvo3Kc2YRl/dNdrH2lfBJM+M6ZDF0NVBzQHtly9Lj8peYF3hCQTh5aZfXlsH3MCcawOI6AAIPzMTwOkMBXDAoV2wprOETsQXosUPqqyHtuqN33+sSVhsN4nv2DUuuA84wrErZ0mZDjbBWFkJyjiX1oWHFTEkVCuTbiN9VFeDAuWeE2K6wgwHfNE4G+imGcKnkL5OCt4oEf1KeMst7zQ7+hLOjDvXnKE6G6CarzE7v/8CdIK8zlQkMNixrZR/N5gP4IyTDfo2cAliXtCbEjpnhWRcqI074iz/zJZWBDdnLNuwqI5282/UzPCnTbAbUi7o9PVqFdroRQapLBUMdwp4gcqjBbp5jgBhayVwu+7efNGdxqp1iNlNbN7Fq23w4RIgYn9POCRhaLodsN3kEJVIC/muvEsJcbH7KuYAA14yYBzM3ZVm8EbV8pkpBy2ENa1zkGCwwM16KipmnZl+jTHtRu6dYHcNiuWz8jJc5vdPQZ2WWlcQrZDDKGjtqSmA8pnHhZKlkExk+6oFk/4xCMdz3XZVlHzbP60k4HFQ7q2dNvPM5YrTqq1pMf5DMEWITVuPQfgl1bsw3x2oFc2/cC1IVcjO3RUy9cFweAyREtXNP29HnkS7Y00EbrY0gviN3PGlq/RqQ6KFnJa624cFfNb9musWYcOOYsKA==
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:TYAPR01MB5866.jpnprd01.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230001)(4636009)(366004)(26005)(7696005)(9686003)(508600001)(186003)(6506007)(64756008)(2906002)(66556008)(66946007)(66476007)(122000001)(76116006)(66446008)(86362001)(8676002)(4326008)(85182001)(38100700002)(54906003)(316002)(6916009)(33656002)(82960400001)(8936002)(52536014)(5660300002)(83380400001)(71200400001)(38070700005)(55016003);DIR:OUT;SFP:1101;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-2022-jp?B?dFhzVGNVN3AxZmZRcW9La09CeGFjQ1c1VXNtWUFtSnpiSFo2aEVCQ2hN?=
 =?iso-2022-jp?B?dTZvWnIydktNaXhUM1c0bUZjMkU3bm5ibVp5UzZVYitDNUVTellqdGpL?=
 =?iso-2022-jp?B?VDdtaWJhdlg4Y0dQZThmRlJnd1lzR1JBdEduY3Ard1Z5ZytsWjZqUmV0?=
 =?iso-2022-jp?B?WUx1VFk3cmJHZ3J2MERDMEpxN243Z0IrVnJRT3VlVUVGVC9Cc0s0TDcx?=
 =?iso-2022-jp?B?ZzhaWDhudUxkWk5UbDRMUHdIanZYUUo0OUE0VkdJMGFWcTdGdFZ2UzVQ?=
 =?iso-2022-jp?B?Y3hBcW9PVWxwRVNjK0NwOWJ0Q0hzQjIvZWR6ZFVNWEQ4TWNHL2lBREht?=
 =?iso-2022-jp?B?NFpIN3hKZUlrUlZqMDgxT2pxU0JiVVMyTU9MSHoxWldsLzhjSlBCRVlM?=
 =?iso-2022-jp?B?b2ErS1Jhc1BKdmZjbmlJcnlTVWNuYmZoQWd0Rmh4WkE1QTlBb3FKOVN3?=
 =?iso-2022-jp?B?L0FaS0pLbTVhSm9wVGJoaVgrUGdrcG9YMkRFZUJLZjZodExzVTFHRm1Z?=
 =?iso-2022-jp?B?cHJuRWFQUmRlR0F5Lzhyb2E5dkJ0UVNyVFFwd0ZJN2dWNzBuOFRWQ09I?=
 =?iso-2022-jp?B?Y1NDOWVjN2p4VTYwaC9lK1FFK3VZWmZUaGcxQitHSzZON0VwV3ZjY21I?=
 =?iso-2022-jp?B?VTdRZVlRYzRDSFk2cGp2NjcxWDE4KzFLRCttRHlja1JpZlFOK1dwQXc5?=
 =?iso-2022-jp?B?YUFrT2ZzQnRtWDFCRHhMd3FES08zNzd4dzhqNFVkTkRNOWlRSHZLbDB4?=
 =?iso-2022-jp?B?ZlBoQm9oRlBCeUFEV3MzMS9jd3daOUg5eTYyS20yWGdYM0VPWngzRHZE?=
 =?iso-2022-jp?B?QWp6SVNVZkdMVDVPR2w1Q3EvK1ZnRTdFTmxxdzJWM3ZhWmw0N0w3UjFE?=
 =?iso-2022-jp?B?bXBDdmtsb3hlZ0pBMUdsSnVpNUhIMG40M0VCbXBmaGt2V0dML0k1Wnpj?=
 =?iso-2022-jp?B?ZEFKSjZDK3FRa0ovVTFaRlFUOHd6eU1YV0RjYlBESUx3bEl4ZG1lL2tz?=
 =?iso-2022-jp?B?Y3RJRlhVMGd5YTdkOEtsRWxNWUhLWkE0by9rREdKQUFsMkgrU0N5OHJM?=
 =?iso-2022-jp?B?UGRqT09XQ3QzMUtKbHFJOUlHVVBGcG9pc3dzdXMyVVdNbTZBRHRrYUlM?=
 =?iso-2022-jp?B?VjFCalVtK1hhYlU4QjlTMk1td0pPU1MwMmZuWWh1ajdZNmxBUlFTK0ov?=
 =?iso-2022-jp?B?YzJaMmJEekg0RGpWcklrRUtpUXF2em1zOE5rQUlmdHhpQXBXZWVPSjg3?=
 =?iso-2022-jp?B?S3lGMGRVNmVnS2ZYS2s0KzNZOENIKzN0ejRNOEsvYm5FNDQwNlVONU1p?=
 =?iso-2022-jp?B?WHc2MHlFbFJUU2pPVERqbFJaVkFmcXZVTlBrR2k0WFpDK212TFVFWndt?=
 =?iso-2022-jp?B?SmV5UG5IM1VBai8wWHdFeTM0VGpCL3hsN0JaMkQ2UkJSR0N1SUdaYVBM?=
 =?iso-2022-jp?B?ZzRxWkdCbFJQYTN0LzV0UUZFblNBS1BBT0pueFNKS0R4K2FIQ2ZYNFhk?=
 =?iso-2022-jp?B?Z3dUaGNMWXlLWWpxT3lXV3djU3NYaDNIbHRacU5Bd0VITGhrYmQxMkQ1?=
 =?iso-2022-jp?B?dThrVnpUYVp3VmtGaExIZHRCRStCTEJhU1BiTFlnNU80enJoU3FuazB2?=
 =?iso-2022-jp?B?dVphRXdkYWdvaGN3d252SW1sTXRVMFdRMk5ReGoyN1o1Z0JkNG1CbVpE?=
 =?iso-2022-jp?B?QWlVUkl4amlzWXlOSXEzZmVyc0lRMlBZUDN3eWtWRVVKYVJLUVlHeEU3?=
 =?iso-2022-jp?B?WkFxdnRad2V5R2dCZEM4MHhXWXhqVXJEZHJ1d3FoQVpYNlM3ckR5c096?=
 =?iso-2022-jp?B?UFIzbGhWMVZjaTV4VXJkRzk1eEk1TU1GSitVMVF3WnZkVjFhK1VTdTBM?=
 =?iso-2022-jp?B?S1FhVmVmQTIyUGJWRk0zL2FnZ2VRU2pmSStpOHNtQ1pEWlRaUGlBK0V3?=
 =?iso-2022-jp?B?R3I2TjZ4bzZqOUpnQW4zcEtZdW1uOHJVYUsrVUp5WFplRFdEL0c0bEVt?=
 =?iso-2022-jp?B?VW1UQm9WUXVkQk5tM20yWWU1ZGlGRjBsZTQ3ZFVWT0tzUzlQdFNKYjFU?=
 =?iso-2022-jp?B?NkorMGMzQ2tzcFFXMG9UZ0NraWlvbWNTOXBUVW5rREdoQm51S1lGMnJG?=
 =?iso-2022-jp?B?RjBCWEJpZy9wZlVid2hRK1AvazRLSFk3cVdnQit2ZHU0MndrejdHSExj?=
 =?iso-2022-jp?B?TWxJTkhYU0djNzVzcWlUK0o0TSs3bEZqaVVkK2x3RHNDcEJLdVJ0VEww?=
 =?iso-2022-jp?B?cXpXczRDNjRGTE1ObmxmdVNpOERFNXluOGVGcTg1T0hrbEdjTlpTNHNs?=
 =?iso-2022-jp?B?MXlyUFN6THVRWk5NUnZCcGc0WHcrVkNWczNzTC9tcEtXNG9XSzFMMTY2?=
 =?iso-2022-jp?B?akZSbldGL3hIeFJRdGIrK0x0bHR2UitTZnJLOWUyeC9za1hMdTNKRFBV?=
 =?iso-2022-jp?B?cWpGOWx3PT0=?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: fujitsu.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYAPR01MB5866.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 36f89089-52d7-4638-2023-08d9f1cb86e2
X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Feb 2022 04:11:09.2319
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: a19f121d-81e1-4858-a9d8-736e267fd4c7
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: cO5OCjUQUx62GIoypLPsViZQ1JvUx24UAFFl569pr1HGlcnJYfh7514nvs1l6noRRjAT6G9IfHrNObm7UfpVRm1w44mCXOXroNXjdq1EQ2c=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS3PR01MB5591
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/TYAPR01MB58661B088B6066282824ADD9F5369%40TYAPR01MB5866.jpnprd01.prod.outlook.com>
Precedence: bulk

Dear Horiguchi-san,

Thank you for giving your suggestions. I want to confirm your saying.

> FWIW, I'm not sure this feature necessarily requires core support
> dedicated to FDWs.  The core have USER_TIMEOUT feature already and
> FDWs are not necessarily connection based.  It seems better if FDWs
> can implement health check feature without core support and it seems
> possible.  Or at least the core feature should be more generic and
> simpler. Why don't we just expose InTransactionHealthCheckCallbacks or
> something and operating functions on it?

I understood that core is too complicated and FDW side is too stupid, right=
?

> Mmm. AFAICS the running command will stop with "canceling statement
> due to user request", which is a hoax.  We need a more decent message
> there.

+1 about better messages.

> I understand that the motive of this patch is "to avoid wasted long
> local work when fdw-connection dies".

Yeah your understanding is right.

> In regard to the workload in
> your first mail, it is easily avoided by ending the transaction as soon
> as remote access ends. This feature doesn't work for the case "begin;
> <long local query>; <fdw access>". But the same measure also works in
> that case.  So the only case where this feature is useful is "begin;
> <fdw-access>; <some long work>; <fdw-access>; end;".  But in the first
> place how frequently do you expecting remote-connection close happens?
> If that happens so frequently, you might need to recheck the system
> health before implementing this feature.  Since it is correctly
> detected when something really went wrong, I feel that it is a bit too
> complex for the usefulness especially for the core part.

Thanks for analyzing motivation.
Indeed, some cases may be resolved by separating tx and this event rarely h=
appens.

> In conclusion, as my humble opinion I would like to propose to reduce
> this feature to:
>=20
> - Just periodically check health (in any aspect) of all live
>   connections regardless of the session state.

I understood here as removing following mechanism from core:

* disable timeout at end of tx.
* skip if held off or read commands

> - If an existing connection is found to be dead, just try canceling
>   the query (or sending query cancel).
> One issue with it is how to show the decent message for the query
> cancel, but maybe we can have a global variable that suggests the
> reason for the cancel.

Currently I have no good idea for that but I'll try.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED