Re: Problem with accesing Oracle from plperlu functionwhen using remote pg client.

Jonah H. Harris wrote:

> Finally, my low-level Oracle knowledge does benefit Postgres :)

:-)

>
> It's a TNS parsing error due to a combination of Oracle's use of a
> Lispish s-expression-like name-value pair format and Postgres' process
> listing format for remote connections.
>
> On connection, the Oracle client sends the current application name to
> the Oracle server (which is listed in the V$SESSION view); in the case
> of Postgres, the program name is the current backend process name text.
> Because Oracle picks up Postgres' backend text, "postgres: www postgres
> 192.168.1.1(13243)", the (13243) screws up Oracle's TNS parser which
> prevents it from resolving the connection. This doesn't happen when
> you're connected to PG locally, because the backend text is, "postgres:
> www postgres [local]".
>
> The solution to this is to change the following line in
> src/backend/postmaster/postmaster.c:
>
> remote_port[0] == '\0' ? "%s" : "%s(%s)"
>
> TO
>
> remote_port[0] == '\0' ? "%s" : "%s[%s]"
> OR
> remote_port[0] == '\0' ? "%s" : "%s:%s"
>
> Which I would prefer as a nice change to make overall.

Any way to override it in the Oracle client? With an enviroment variable
or something?

Because making that change would probably break a bunch of pg admin
scripts on random systems around the world.. (yes, they should be using
pg_stat_activity, but I'll bet you there are a lot of stuff out there
based on the ps output)

(if we do change it, the colon notification seems to be the reasonable
one given that's how we usually write ip/port pairs)

//Magnus