Thread: [postgres_fdw] add local pid to fallback_application_name

Hi Hackers,

I propose adding trackable information in postgres_fdw, in order to track remote query correctly.

## Background and motivation

Currently postgres_fdw connects remote servers by using connect_pg_server(). However the function just calls
PQconnectdbParams()with fallback_application_name = "postgres_fdw." 
Therefore, if two or more servers connect to one data server and two queries arrive at the data server, the database
administratorcannot determine which queries came from which server. 
This problem prevents some workload analysis because it cannot track the flow of queries.

## Implementation

I just added local backend's pid to fallback_application_name. This is the key for seaching and matching two logs.
In order to use the feature and track remote transactions, user must add backend-pid and application_name to
log_line_prefix,like 

```
log_line_prefix = '%m [%p] [%a] '
```

Here is the output example. Assume that remote server has a table "foo," and local server imports the schema.
When local server executes foregin scan, the following line was output in the local's logfile.

```
2021-07-29 03:18:50.630 UTC [21572] [psql] LOG:  duration: 23.366 ms  statement: select * from foo;
```

And in the remote's one, the following lines were appered.

```
2021-07-29 03:18:50.628 UTC [21573] [postgres_fdw for remote PID: 21572] LOG:  duration: 0.615 ms  parse <unnamed>:
DECLAREc1 CURSOR FOR 
        SELECT id FROM public.foo
```

Two lines have same pid, so we can track the log and analyze workloads correctly.
I will write docs later, but now I want you to review the motivation and implementation.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

"kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com> writes:
> I propose adding trackable information in postgres_fdw, in order to track remote query correctly.

I don't think this is a great idea as-is.  People who need to do this
sort of thing will all have their own ideas of what they need to track
--- most obviously, it might be appropriate to include the originating
server's name, else you don't know what machine the PID is for.
So I think most people with this sort of requirement will be overriding
the default application name anyway, so we might as well keep the
default behavior simple.

What would be better to think about is how to let users specify this
kind of behavior for themselves.  I think it's possible to set
application_name as part of a foreign server's connection options,
but at present the result would only be a constant string.  Somebody
who wished the PID to be in there would like to have some sort of
formatting escape, say "%p" for PID.  Extrapolating wildly, maybe we
could make all the %-codes known to log_line_prefix available here.

Perhaps this is overkill.  But I think the patch you have here is
not going to make very many people happy: it'll either be detail
they don't want, or too little detail.

            regards, tom lane

Dear Tom,

Thank you for replying!

> I don't think this is a great idea as-is.  People who need to do this
> sort of thing will all have their own ideas of what they need to track
> --- most obviously, it might be appropriate to include the originating
> server's name, else you don't know what machine the PID is for.

I thought this is not big problem because hostname (or IP address) can be
added to log_line_prefix. I added only local-pid because this is the only thing
that cannot be set in the parameter.

> So I think most people with this sort of requirement will be overriding
> the default application name anyway, so we might as well keep the
> default behavior simple.

Yeah, this patch assumed that application_name will be not overridden.
There is an another approach that PID adds to application_name, but it might be stupid.

> What would be better to think about is how to let users specify this
> kind of behavior for themselves.  I think it's possible to set
> application_name as part of a foreign server's connection options,
> but at present the result would only be a constant string.  Somebody
> who wished the PID to be in there would like to have some sort of
> formatting escape, say "%p" for PID.  Extrapolating wildly, maybe we
> could make all the %-codes known to log_line_prefix available here.

I think your argument is better than mine. I will try to implement this approach.
If anyone has another argument please tell me.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED