Horton

RE: [Proposal] Add foreign-server health checks infrastructure

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread View on postgr.es
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>
Date: 2022-02-22T02:53:26Z
Lists: pgsql-hackers

Attachments

Dear Horiguchi-san, Fujii-san,

> > I understood here as removing following mechanism from core:
> >
> > * disable timeout at end of tx.
> > * skip if held off or read commands
> 
> I think we're on the same page. Anyway query cancel interrupt is
> ignored while rading input.
> 
> > > - 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.
> 
> However, I would like to hear others' opnions about the direction, of
> course.
>

Based on the idea, I re-implemented the feature. Almost all feature is
moved to postgres_fdw. The abstract of my patch is as follows:

# core

* Exposes QueryCancelMessage for error reporting
* Uses above if it was not NULL

# postgres_fdw

* Defines new GUC postgres_fdw.health_check_interval.
  It is renamed simpler.
* Registers a timeout when initializing connection hash.
* Raises SIGINT and sets QueryCancelMessage to message.
  if detects a connection lost.

I also attached a test as zipped file for keep cfbot quiet.
When connection lost is detected, the following message will show:

```
ERROR:  Foreign Server (servername) might be down.
```

How do you think?

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

Commits

  1. postgres_fdw: Extend postgres_fdw_get_connections to return user name.

  2. postgres_fdw: Fix bug in connection status check.

  3. postgres_fdw: Add connection status check to postgres_fdw_get_connections().

  4. postgres_fdw: Add "used_in_xact" column to postgres_fdw_get_connections().

  5. doc: Enhance documentation for postgres_fdw_get_connections() output columns.

  6. Avoid reference to nonexistent array element in ExecInitAgg().

  7. Add WL_SOCKET_CLOSED for socket shutdown events.

  8. postgres_fdw: reestablish new connection if cached one is detected as broken.