When I do a ps -ef, in the command column, I see:

postgres: postgres dbname 10.170.1.60(57413) idle

I get all of this, except the "57413". What does this mean, and more
importantly, how can I tie that number back to a connection that I've
acquired via JDBC?

In my case, I've got a connection that's hanging around after my code should
have closed it, which means almost certainly that I've got problems in my
code, but I'd love to be able to get that "57413" number from my jdbc object
and write it to my logs to troubleshoot this. Any ideas?

Thanks,
Dave

On 2/22/08, David Jaquay <djaquay@gmail.com> wrote:
> When I do a ps -ef, in the command column, I see:
>
> postgres: postgres dbname 10.170.1.60(57413) idle
>
> I get all of this, except the "57413". What does this mean, and more
> importantly, how can I tie that number back to a connection that I've
> acquired via JDBC?

At a guess, it's the ephemeral port number used by the client
connection. It might be hard to track back in Java because I don't
think the JDBC driver gives you access to the underlying Socket object
(which you could query to find out its local port).

-Doug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Yeah, kinda guessed that.

So there's no way (that you know of) to, say, cast my JDBC connection object
to something Postgresql'y and peer into its internals?

Thanks,
Dave


On Fri, Feb 22, 2008 at 11:28 AM, Douglas McNaught <doug@mcnaught.org>
wrote:

> On 2/22/08, David Jaquay <djaquay@gmail.com> wrote:
> > When I do a ps -ef, in the command column, I see:
> >
> > postgres: postgres dbname 10.170.1.60(57413) idle
> >
> > I get all of this, except the "57413". What does this mean, and more
> > importantly, how can I tie that number back to a connection that I've
> > acquired via JDBC?
>
> At a guess, it's the ephemeral port number used by the client
> connection. It might be hard to track back in Java because I don't
> think the JDBC driver gives you access to the underlying Socket object
> (which you could query to find out its local port).
>
> -Doug
>

On the one hand, that's pretty cool. I keep forgetting that's out there.

On the other hand, I know what process is holding the connection; it's the
only one on the box connecting to that server. So lsof doesn't let me
connect a process on the server to a connection object (one of many) on the
client.

Thanks just the same, tho,

Dave


On Fri, Feb 22, 2008 at 11:55 AM, Erik Jones <erik@myemma.com> wrote:

>
> On Feb 22, 2008, at 10:28 AM, Douglas McNaught wrote:
>
> > On 2/22/08, David Jaquay <djaquay@gmail.com> wrote:
> >> When I do a ps -ef, in the command column, I see:
> >>
> >> postgres: postgres dbname 10.170.1.60(57413) idle
> >>
> >> I get all of this, except the "57413". What does this mean, and more
> >> importantly, how can I tie that number back to a connection that I've
> >> acquired via JDBC?
> >
> > At a guess, it's the ephemeral port number used by the client
> > connection. It might be hard to track back in Java because I don't
> > think the JDBC driver gives you access to the underlying Socket object
> > (which you could query to find out its local port).
>
> See the lsof unix tool for a good way to track which processes are
> communicating via that port number.
>
> Erik Jones
>
> DBA | Emma(R)
> erik@myemma.com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>
>

On Feb 22, 2008, at 10:28 AM, Douglas McNaught wrote:

> On 2/22/08, David Jaquay <djaquay@gmail.com> wrote:
>> When I do a ps -ef, in the command column, I see:
>>
>> postgres: postgres dbname 10.170.1.60(57413) idle
>>
>> I get all of this, except the "57413". What does this mean, and more
>> importantly, how can I tie that number back to a connection that I've
>> acquired via JDBC?
>
> At a guess, it's the ephemeral port number used by the client
> connection. It might be hard to track back in Java because I don't
> think the JDBC driver gives you access to the underlying Socket object
> (which you could query to find out its local port).

See the lsof unix tool for a good way to track which processes are
communicating via that port number.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 23/02/2008, David Jaquay <djaquay@gmail.com> wrote:
> When I do a ps -ef, in the command column, I see:
>
> postgres: postgres dbname 10.170.1.60(57413) idle
This doesn't resemble any "ps -ef" output I've ever seen.
What OS is this on, what's the version of ps?


Cheers,
Andrej

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On 2/22/08, David Jaquay <djaquay@gmail.com> wrote:
> Yeah, kinda guessed that.
>
> So there's no way (that you know of) to, say, cast my JDBC connection object
> to something Postgresql'y and peer into its internals?

The docs and the source code for the PG JDBC driver are freely
available. Worst case you could add a method for fetching the Socket
object and recompile the driver.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, 22 Feb 2008, David Jaquay wrote:

> In my case, I've got a connection that's hanging around after my code should
> have closed it, which means almost certainly that I've got problems in my
> code, but I'd love to be able to get that "57413" number from my jdbc object
> and write it to my logs to troubleshoot this. Any ideas?
>

The JDBC driver has an option logUnclosedConnections[1] that can be used
to find where you've neglected to close things. Any connection that gets
cleaned up by the garbage collector logs the stacktrace of its creation,
so you can see where it got built from.

Kris Jurka

[1] http://jdbc.postgresql.org/documenta...ion-parameters

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Andrej Ricnik-Bay wrote:
> On 23/02/2008, David Jaquay <djaquay@gmail.com> wrote:
> > When I do a ps -ef, in the command column, I see:
> >
> > postgres: postgres dbname 10.170.1.60(57413) idle
> This doesn't resemble any "ps -ef" output I've ever seen.
> What OS is this on, what's the version of ps?

I had forgotten we showed the remote port number for TCP connections,
but I see it here:

postgres 13651 8991 0 7:26AM ?? 0:00.01 postgres test 127.0.0.1(57352) idle (postmaster)

and it seems we have been doing it for years.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq