[GENERAL] Understanding ps -ef "command" column
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
Re: [GENERAL] Understanding ps -ef "command" column
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 <[EMAIL PROTECTED]> wrote: > On 2/22/08, David Jaquay <[EMAIL PROTECTED]> 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 >
Re: [GENERAL] Understanding ps -ef "command" column
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 <[EMAIL PROTECTED]> wrote: > > On Feb 22, 2008, at 10:28 AM, Douglas McNaught wrote: > > > On 2/22/08, David Jaquay <[EMAIL PROTECTED]> 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) > [EMAIL PROTECTED] > 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 > > > >
[GENERAL] LEFT JOIN issue
I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I believe to be an Ubuntu Heron server. The sql below demonstrates the issue. What I expect to see is no rows in the output, i.e. the LEFT JOIN should pair the two rows together, and the WHERE clause should decide that the joined row doesn't match, and should yield no output. What happens is that the planner appears to apply the WHERE clause early, the left table doesn't yield any rows, and the row from the right table is output by itself. This only appears to happen when both sides of the OR are present, and the idx_beta_datereceived index is present. Remove any one, and it works like I expect. Dave create table alpha ( alphaid bigint not null, betaid bigint null, itemcode char(1) not null ); ALTER TABLE ONLY alpha ADD CONSTRAINT pk_alpha PRIMARY KEY (alphaid); create table beta ( betaid bigint not null, datereceived date null ); ALTER TABLE ONLY beta ADD CONSTRAINT pk_beta PRIMARY KEY (betaid); create index idx_alpha_betaid on alpha(betaid); insert into alpha values (22044, 92359002, 'U'); insert into beta values (92359002, '2008-08-11'); CREATE INDEX idx_beta_datereceived ON beta USING btree (datereceived); analyze alpha; analyze beta; explain select alpha.alphaid as aid , alpha.betaid as alphabetaid , beta.betaid as betaid , beta.datereceived , alpha.itemcode from alpha left join beta on beta.betaid = alpha.betaid where alpha.alphaid = 22044 and (beta.datereceived IS NULL OR ( beta.betaid IS NULL AND alpha.itemcode='U' ) ); QUERY PLAN --- Nested Loop Left Join (cost=0.00..2.04 rows=1 width=30) Join Filter: (beta.betaid = alpha.betaid) Filter: ((beta.datereceived IS NULL) OR ((beta.betaid IS NULL) AND (alpha.itemcode = 'U'::bpchar))) -> Seq Scan on alpha (cost=0.00..1.01 rows=1 width=18) Filter: (alphaid = 22044) -> Seq Scan on beta (cost=0.00..1.01 rows=1 width=12) Filter: ((beta.datereceived IS NULL) OR (beta.betaid IS NULL)) (7 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LEFT JOIN issue
Just got an email saying that this has been fixed in 8.3.4, so I suppose I'll watch and wait for that. Thanks for looking, Alan (and anyone else who was watching this...) Dave On Tue, Sep 9, 2008 at 4:57 PM, Alan Hodgson <[EMAIL PROTECTED]> wrote: > On Tuesday 09 September 2008, "David Jaquay" <[EMAIL PROTECTED]> wrote: >> I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I >> believe to be an Ubuntu Heron server. The sql below demonstrates the >> issue. > > explain doesn't execute the query. Show the output of the actual select - it > doesn't return any rows on my 8.3.3 database. > > -- > Alan > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How does filter order relate to query optimization?
A really long time ago, on 2/27/07, Tom Lane <[EMAIL PROTECTED]> wrote: Usually what you want to think about for something like this is plan optimization, ie, what order are the tables joined in and with what join methods. How exactly can you control the join order? I have cases where the explain data indicates that the planner is picking a very inefficient way to get the data, (i.e. joining two very big tables in their entirety, then joining that to a 3rd with maybe 10 rows yielding maybe 30). I'd love to have some more determinacy in dealing with the planner on complex queries... Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
