Re: Extend postgres_fdw_get_connections to return remote backend pid

On 2025/02/25 1:55, Sagar Shedge wrote:
> Hi Team,
>
> Created patch as per recommendation in postgres wiki.

Thanks for updating the patch!

+ Process ID of remote backend handling connection. If <literal>valid</literal>
+ is set to <literal>false</literal> (i.e., marked as invalid), this will be
+ process ID of remote backend which handled this connection. <literal>NULL</literal>
+ is returned if no backend assigned to this connection or connection status is bad.

I have one question; is there actually a case where remote_backend_pid
returns NULL in postgres_fdw_get_connections()? Based on the current patch,
if the connection object (PGconn) is NULL or its state isn’t CONNECTION_OK,
PQbackendPID() returns 0, so remote_backend_pid becomes NULL.

However, if PGconn is NULL, it seems like postgres_fdw_get_connections()
wouldn't include that connection in the result. If the connection status
is not CONNECTION_OK, it looks like the connection would be closed by
pgfdw_reset_xact_state() before the local backend processes the query
calling postgres_fdw_get_connections(). So, can remote_backend_pid really
be NULL?

If remote_backend_pid will never be NULL, wouldn't a simpler description
like the following be clearer and less confusing for users?
Otherwise, users might wonder why remote_backend_pid isn't NULL
even if the remote server-side connection is closed.

Process ID of the remote backend, on the foreign server, handling the connection.

Also, rather than setting NULL when PQbackendPID() returns 0, would it
make sense for postgres_fdw_get_connections() to just return the result
of PQbackendPID() as-is?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION