MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 16 Nov 2021 14:26:37 +0900
From: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
To: kuroda.hayato@fujitsu.com
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: [Proposal] Add foreign-server health checks infrastructure
In-Reply-To: 
 <TYAPR01MB58662809E678253B90E82CE5F5889@TYAPR01MB5866.jpnprd01.prod.outlook.com>
References: 
 <TYAPR01MB58662809E678253B90E82CE5F5889@TYAPR01MB5866.jpnprd01.prod.outlook.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <54449d7bea79263e937266ad6ebc65b3@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/54449d7bea79263e937266ad6ebc65b3%40oss.nttdata.com>
Precedence: bulk

Hi,
Thank you for the patch!

On 2021-10-30 12:50, kuroda.hayato@fujitsu.com wrote:

> ## Background
> 
> Currently there is no way to check the status of an foreign server in
> PostgreSQL.
> If an foreign server's postmaster goes down, or if the network between
> servers is lost,
> the backend process will only detect these when it uses the connection
> corresponding to that foreign server.
> 
> Consider a workload that updates data on an foreign server only at the
> beginning of a transaction,
> and runs a lot of local SQLs. Even if the network is disconnected
> immediately after accessing the foreign server,
> the backend process will continue to execute local SQLs without 
> realizing it.
> 
> The process will eventually finish to execute SQLs and try to commit.
> Only then will it realize that the foreign server cannot be connect
> and will abort the transaction.
> This situation should be detected as soon as possible
> because it is impossible to commit a transaction when the foreign
> server goes down.
> This can be more of a problem if you have system-wide downtime 
> requirements.
> That's why I want to implement the health-check feature to postgres.

It's a good idea. I also think such a situation should be avoided.

> ## Further work
> 
> As the next step I have a plan to implement the callback function to
> postgres_fdw.
> I already made a PoC, but it deeply depends on the following thread:
> https://commitfest.postgresql.org/35/3098/
> 
> I also want you to review the postgres_fdw part,
> but I think it should not be attached because cfbot cannot understand
> such a dependency
> and will throw build error. Do you know how to deal with them in this 
> case?

I don't know how to deal with them, but I hope you will attach the PoC, 
as it may be easier to review.

-- 
Regards,

--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION