[Re: Password?]

Yes,
Its the currently logged on user.

--- On Wed, 7/8/09, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

From: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
Subject: [Re: [GENERAL] Password?]
To: "PG-General Mailing List" <pgsql-general@postgresql.org>
Date: Wednesday, July 8, 2009, 3:54 PM

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method 'password' in
pg_hba.conf means, that you will be asked for a password for the user you
try to create a db with. If no user is given (with createdb -U [username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no user
is specified?

correct - so this will be postgres because other users are not allowed to use these
programs ...

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL:  role "duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2009-07-08, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method 'password' in
pg_hba.conf means, that you will be asked for a password for the user you
try to create a db with. If no user is given (with createdb -U [username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no user
is specified?

correct - so this will be postgres because other users are not allowed to use these
programs ...

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL: role "duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

if it's "trust" any user can do

~postgres/8.4/bin/createdb -U postgres -p 5433 test

Jasen Betts schrieb:

On 2009-07-08, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method 'password' in
pg_hba.conf means, that you will be asked for a password for the user you
try to create a db with. If no user is given (with createdb -U [username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no user
is specified?

correct - so this will be postgres because other users are not allowed to use these
programs ...

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL: role "duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

if it's "trust" any user can do

~postgres/8.4/bin/createdb -U postgres -p 5433 test

nope! what you did is calling createdb as system user postgres (I believe because of the ~
sign at the beginning) *and* giving the option -U postgres. That works for sure and you
even don't need -U postgres since you are allready postgres. But leave -U postgres away as
a system user not equal to postgres ... see my example above.

Cheers

Andy

On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote:

Jasen Betts schrieb:

On 2009-07-08, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method
'password' in
pg_hba.conf means, that you will be asked for a password for the
user you
try to create a db with. If no user is given (with createdb -U
[username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no
user
is specified?

correct - so this will be postgres because other users are not
allowed to use these
programs ...

That's not true.

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL: role
"duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

if it's "trust" any user can do
~postgres/8.4/bin/createdb -U postgres -p 5433 test

nope! what you did is calling createdb as system user postgres (I
believe because of the ~ sign at the beginning) *and* giving the
option -U postgres. That works for sure and you even don't need -U
postgres since you are allready postgres. But leave -U postgres away
as a system user not equal to postgres ... see my example above.

Nor is that.

Most of the postgresql client tools, including createdb, can be used
by any operating system user to connect to the database as any
database user.

If they are called with "-U foo" then they will attempt to connect to
the database as database user "foo".

If they are not called with -U then they will usually attempt to
connect to the database as the current operating system user (though
that can be overridden with the PGUSER or PGSERVICE environment
variables).

So if I'm logged in as steve and I do "createdb test" then I will try
and connect to the database as database user steve and create the test
database. If I do "createdb -U postgres test" I will try to connect to
the database as database user "postgres" and create the test database.

Whether I'm prompted for a password or not depends on the settings in
pg_hba.conf. Typically the postgres operating system user is allowed
to connect to the database as the postgres database user without a
password. Other OS user / database user combinations may or may not
need a password depending on whether pg_hba.conf is set up to ask for
one or not - how that is set up as default varies, but it's fairly
common to require a password.

Cheers,
Steve

On 08/07/2009 18:14, Steve Atkins wrote:

Typically the postgres operating system user is allowed to
connect to the database as the postgres database user without a
password.

Is this really so? I don't think so - I think it depends on pg_hba.conf
settings, just like any other user.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Jul 8, 2009, at 10:25 AM, Raymond O'Donnell wrote:

On 08/07/2009 18:14, Steve Atkins wrote:

Typically the postgres operating system user is allowed to
connect to the database as the postgres database user without a
password.

Is this really so? I don't think so - I think it depends on
pg_hba.conf
settings, just like any other user.

It does, yes. But most of the distributions I've seen tend to set it
up that way (as otherwise there's not really a good way to do
automated maintenance and backups, nor any easy way to
bootstrap the database).

So it's not hardwired that way, just typically set up that way (on
non-Windows OSes anyway - Windows has issues that likely
mean it's setup differently there).

Cheers,
Steve

On 08/07/2009 18:30, Steve Atkins wrote:

On Jul 8, 2009, at 10:25 AM, Raymond O'Donnell wrote:

Is this really so? I don't think so - I think it depends on pg_hba.conf
settings, just like any other user.

It does, yes. But most of the distributions I've seen tend to set it
up that way (as otherwise there's not really a good way to do
automated maintenance and backups, nor any easy way to
bootstrap the database).

So it's not hardwired that way, just typically set up that way (on
non-Windows OSes anyway - Windows has issues that likely
mean it's setup differently there).

Ah, OK - I see what you mean.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Steve Atkins schrieb:

On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote:

Jasen Betts schrieb:

On 2009-07-08, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method
'password' in
pg_hba.conf means, that you will be asked for a password for the
user you
try to create a db with. If no user is given (with createdb -U
[username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no user
is specified?

correct - so this will be postgres because other users are not
allowed to use these
programs ...

That's not true.

you are right!Sorry for that mistake.

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL: role
"duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

if it's "trust" any user can do
~postgres/8.4/bin/createdb -U postgres -p 5433 test

nope! what you did is calling createdb as system user postgres (I
believe because of the ~ sign at the beginning) *and* giving the
option -U postgres. That works for sure and you even don't need -U
postgres since you are allready postgres. But leave -U postgres away
as a system user not equal to postgres ... see my example above.

Nor is that.

Why not? I think it is but maybe I did not write it understandable
enough. See my example.

Most of the postgresql client tools, including createdb, can be used by
any operating system user to connect to the database as any database user.

If they are called with "-U foo" then they will attempt to connect to
the database as database user "foo".

If they are not called with -U then they will usually attempt to connect
to the database as the current operating system user (though that can be
overridden with the PGUSER or PGSERVICE environment variables).

So if I'm logged in as steve and I do "createdb test" then I will try
and connect to the database as database user steve and create the test
database. If I do "createdb -U postgres test" I will try to connect to
the database as database user "postgres" and create the test database.

this is exactly my example. I am the system user duke but there is no
role duke in the db. The result is the error message. Using -U postgres
is successful.

But in the case shown by Swati she is allready the system user postgres.
So there's no need to put the option -U postgres. Correct? See my example.

Whether I'm prompted for a password or not depends on the settings in
pg_hba.conf. Typically the postgres operating system user is allowed to
connect to the database as the postgres database user without a
password. Other OS user / database user combinations may or may not need
a password depending on whether pg_hba.conf is set up to ask for one or
not - how that is set up as default varies, but it's fairly common to
require a password.

exactly. Thanks a lot for explaining that way better.

Cheers,
Steve

Actually I think we both know how it works ;-)

Cheers

Andy

I think I can understand what you both have discussed.
But I am unable to unearth my mistake.
 
Regards
Swati

--- On Wed, 7/8/09, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

From: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
Subject: Re: [GENERAL] [Re: Password?]
To: "Steve Atkins" <steve@blighty.com>
Cc: "pgsql-general List" <pgsql-general@postgresql.org>
Date: Wednesday, July 8, 2009, 11:20 PM

Steve Atkins schrieb:

On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote:

Jasen Betts schrieb:

On 2009-07-08, Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote:

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method 'password' in
pg_hba.conf means, that you will be asked for a password for the user you
try to create a db with. If no user is given (with createdb -U [username]),
this user is postgres ...

Wasn't it that it uses the currently logged on user is used if no user
is specified?

correct - so this will be postgres because other users are not allowed to use these
programs ...

That's not true.

you are right!Sorry for that mistake.

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL:  role "duke" does not exist

$ sudo su postgres
postgres@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postgres@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

if it's "trust" any user can do
~postgres/8.4/bin/createdb -U postgres -p 5433 test

nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above.

Nor is that.

Why not? I think it is but maybe I did not write it understandable enough. See my example.

Most of the postgresql client tools, including createdb, can be used by any operating system user to connect to the database as any database user.

If they are called with "-U foo" then they will attempt to connect to the database as database user "foo".

If they are not called with -U then they will usually attempt to connect to the database as the current operating system user (though that can be overridden with the PGUSER or PGSERVICE environment variables).

So if I'm logged in as steve and I do "createdb test" then I will try and connect to the database as database user steve and create the test database. If I do "createdb -U postgres test" I will try to connect to the database as database user "postgres" and create the test database.

this is exactly my example. I am the system user duke but there is no role duke in the db. The result is the error message. Using -U postgres is successful.

But in the case shown by Swati she is allready the system user postgres. So there's no need to put the option -U postgres. Correct? See my example.

Whether I'm prompted for a password or not depends on the settings in pg_hba.conf. Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Other OS user / database user combinations may or may not need a password depending on whether pg_hba.conf is set up to ask for one or not - how that is set up as default varies, but it's fairly common to require a password.

exactly. Thanks a lot for explaining that way better.

Cheers,
   Steve

Actually I think we both know how it works ;-)

Cheers

Andy

-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
 
Thank you all for your kind responses.
 
Things however aren't falling in place.
 
Will take a short break, rework, and get back.
Probably, with a new problem!
 
Thanks again,
Regards
Swati

Hi everyone,

I need some help with tracking down idle in transaction problems. We
have a custom application that is leaving queries in idle in transaction
status for unknown reasons. The developers are working on ways to track
it down, but right now the options on their end are limited and it will
be at least 1-2 months until they have something working. I am trying
to track down the problem from the PG end in the meantime. Is there any
way to tell what query is hanging in idle in transaction status? Or
what the current or previous query was/is, since idle in transaction
doesn't tell me anything? I'm kind of at a loss on what if anything I
can do from the database end to help (read push) the programmers to find
and fix this problem.

My first priority is helping them find and fix the problem if I can. My
second priority is finding an automated way to deal with the idle in
transactions as they are locking tables and rows, causing other
transactions to hang also. None of the timeouts appear to affect idle
in transactions.

I'm on PG 8.3.7. Upgrading to 8.4 won't be an option for several
months, enough time for the developers to test and develop against 8.4.

Thanks for any help you can give me.

Scot Kreienkamp

Scot Kreienkamp wrote:

Hi everyone,

I need some help with tracking down idle in transaction problems. We
have a custom application that is leaving queries in idle in
transaction status for unknown reasons. The developers are working on
ways to track it down, but right now the options on their end are
limited and it will be at least 1-2 months until they have something
working. I am trying to track down the problem from the PG end in the
meantime. Is there any way to tell what query is hanging in idle in
transaction status? Or what the current or previous query was/is,
since idle in transaction doesn’t tell me anything? I’m kind of at a
loss on what if anything I can do from the database end to help (read
push) the programmers to find and fix this problem.

there is no active query, thats why its idle. they did a "BEGIN" to
start a transaction, then left the connection idle.

is this software, by any chance, Java based? older versions of the
Postgres JDBC module had a nasty habit of doing this, as JDBC
autogenerates the BEGIN if its not in autocommit mode. the older version
would generate the begin immediately after a COMMIT or ROLLBACK to
prepare for the next transaction, and if the app simply stopped using
the connection, it was left IDLE IN TRANSACTION. The updated version
postpones the BEGIN until you issue your first query.

if you enable statement logging and set up a log prefix to show the
Process ID (and I usually prefix with a timestamp, database name and
other useful stuff), then you can grep the logs for the PID of the IDLE
IN TRANSACTION process. Note logging all statements is pretty CPU and
disk intensive, so likely will impact your system performance, so should
only be done for debug purposes.

Hi John,

It is Java. I asked our programmers to check on the JDBC version as I
had seen that on the list previously. It is using postgresql-8.2-504.
Is that one of the problem versions? I had thought it was new enough
that it would not be subject to that problem.

The unexplained part is why are there locks acquired, sometimes on the
row level, prior to the connection going to idle in transaction status?
That makes me think it's not the JDBC driver.

Thanks,

Scot Kreienkamp

-----Original Message-----
From: John R Pierce [mailto:pierce@hogranch.com]
Sent: Friday, July 10, 2009 4:21 PM
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle in transaction help

Scot Kreienkamp wrote:

Hi everyone,

I need some help with tracking down idle in transaction problems. We
have a custom application that is leaving queries in idle in
transaction status for unknown reasons. The developers are working on
ways to track it down, but right now the options on their end are
limited and it will be at least 1-2 months until they have something
working. I am trying to track down the problem from the PG end in the
meantime. Is there any way to tell what query is hanging in idle in
transaction status? Or what the current or previous query was/is,
since idle in transaction doesn't tell me anything? I'm kind of at a
loss on what if anything I can do from the database end to help (read
push) the programmers to find and fix this problem.

there is no active query, thats why its idle. they did a "BEGIN" to
start a transaction, then left the connection idle.

is this software, by any chance, Java based? older versions of the
Postgres JDBC module had a nasty habit of doing this, as JDBC
autogenerates the BEGIN if its not in autocommit mode. the older version

would generate the begin immediately after a COMMIT or ROLLBACK to
prepare for the next transaction, and if the app simply stopped using
the connection, it was left IDLE IN TRANSACTION. The updated version
postpones the BEGIN until you issue your first query.

if you enable statement logging and set up a log prefix to show the
Process ID (and I usually prefix with a timestamp, database name and
other useful stuff), then you can grep the logs for the PID of the IDLE
IN TRANSACTION process. Note logging all statements is pretty CPU and
disk intensive, so likely will impact your system performance, so should

only be done for debug purposes.

Scot Kreienkamp wrote:

It is Java. I asked our programmers to check on the JDBC version as I
had seen that on the list previously. It is using postgresql-8.2-504.
Is that one of the problem versions? I had thought it was new enough
that it would not be subject to that problem.

well, the current JDBC for 8.2 is build 510. see
http://jdbc.postgresql.org/download.html

It does appear the idle-in-transaction bug I'm remembering is pretty
old... it was fixed in Version 8.0-dev302 (2004-06-15), heh. your
version is from 2006-12-01. I would still consider upgrading, there
have been a lot of fixes and enhancements between 504 and 510... See
http://jdbc.postgresql.org/changes.html for a revision history.

Remember, if you have autocommit OFF, then even read-only (select-only)
connections need commits, or they stay in transaction.

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp<SKreien@la-z-boy.com> wrote:

Hi everyone,

I need some help with tracking down idle in transaction problems.  We have a
custom application that is leaving queries in idle in transaction status for
unknown reasons.  The developers are working on ways to track it down, but
right now the options on their end are limited and it will be at least 1-2
months until they have something working.  I am trying to track down the
problem from the PG end in the meantime.  Is there any way to tell what
query is hanging in idle in transaction status?  Or what the current or
previous query was/is, since idle in transaction doesn’t tell me anything?
I’m kind of at a loss on what if anything I can do from the database end to
help (read push) the programmers to find and fix this problem.

My first priority is helping them find and fix the problem if I can.  My
second priority is finding an automated way to deal with the idle in
transactions as they are locking tables and rows, causing other transactions
to hang also.  None of the timeouts appear to affect idle in transactions.

Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port.

For instance, I connect from my laptop with two connections. One I do
a begin; in and in the other I look it up like so:

select * from pg_stat_activity where current_query ilike
'%idle%trans%' and current_query not ilike 'select%';
datid | datname | procpid | usesysid | usename | current_query
| waiting | xact_start | query_start
| backend_start | client_addr | client_port
-------+----------+---------+----------+----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------
11511 | postgres | 24893 | 16413 | smarlowe | <IDLE> in
transaction | f | 2009-07-10 16:20:15.056385-06 | 2009-07-10
16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
48727

The client port is 48727. Now, on my laptop I can do:

sudo lsof |grep 48727 and I have this line in there:

psql 27964 smarlowe 3u IPv4 1114765
TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Note that 27964 is the pid of the psql command that's connected to the
server. Hope that helps a little.

Thanks scott, but I wrote a cgi to combine all of the process info and allow me to kill errant queries. So I know how to track down the pid. Thanks for trying to help though. :-)

----- Original Message -----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Fri Jul 10 18:34:14 2009
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp<SKreien@la-z-boy.com> wrote:

Hi everyone,

I need some help with tracking down idle in transaction problems.  We have a
custom application that is leaving queries in idle in transaction status for
unknown reasons.  The developers are working on ways to track it down, but
right now the options on their end are limited and it will be at least 1-2
months until they have something working.  I am trying to track down the
problem from the PG end in the meantime.  Is there any way to tell what
query is hanging in idle in transaction status?  Or what the current or
previous query was/is, since idle in transaction doesn’t tell me anything?
I’m kind of at a loss on what if anything I can do from the database end to
help (read push) the programmers to find and fix this problem.

My first priority is helping them find and fix the problem if I can.  My
second priority is finding an automated way to deal with the idle in
transactions as they are locking tables and rows, causing other transactions
to hang also.  None of the timeouts appear to affect idle in transactions.

Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port.

For instance, I connect from my laptop with two connections. One I do
a begin; in and in the other I look it up like so:

select * from pg_stat_activity where current_query ilike
'%idle%trans%' and current_query not ilike 'select%';
datid | datname | procpid | usesysid | usename | current_query
| waiting | xact_start | query_start
| backend_start | client_addr | client_port
-------+----------+---------+----------+----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------
11511 | postgres | 24893 | 16413 | smarlowe | <IDLE> in
transaction | f | 2009-07-10 16:20:15.056385-06 | 2009-07-10
16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
48727

The client port is 48727. Now, on my laptop I can do:

sudo lsof |grep 48727 and I have this line in there:

psql 27964 smarlowe 3u IPv4 1114765
TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Note that 27964 is the pid of the psql command that's connected to the
server. Hope that helps a little.

On Jul 10, 2009, at 3:34 PM, Scott Marlowe wrote:

Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port.

For instance, I connect from my laptop with two connections. One I do
a begin; in and in the other I look it up like so:

select * from pg_stat_activity where current_query ilike
'%idle%trans%' and current_query not ilike 'select%';
datid | datname  | procpid | usesysid | usename  |     current_query
| waiting |          xact_start           |          query_start
|         backend_start         | client_addr  | client_port
-------+----------+---------+----------+---------- 
+-----------------------+---------+------------------------------- 
+-------------------------------+------------------------------- 
+--------------+-------------
11511 | postgres |   24893 |    16413 | smarlowe | <IDLE> in
transaction | f       | 2009-07-10 16:20:15.056385-06 | 2009-07-10
16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
48727

The client port is 48727. Now, on my laptop I can do:

sudo lsof |grep 48727 and I have this line in there:

psql 27964 smarlowe 3u IPv4 1114765
TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Just a little tidbit for that: you can have lsof tell you what's got
that port open directly, no need for grep:

lsof -i tcp:48727

that way you keep the column headers in the output.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp<SKreien@la-z-boy.com> wrote:

Thanks scott, but I wrote a cgi to combine all of the process info and allow
me to kill errant queries. So I know how to track down the pid. Thanks for
trying to help though. :-)

So, what are you looking for, a stack trace dump from java to look
through maybe? (the one that kill -1 or whatever generates? It's been
a few years.) That'll usually give you the context to find out which
thread is where. P.s. no need to pat me on the head like the doggie.
:)

We have a java web page that will give us the stack trace of all the
running JDBC connections inside our system. The problem is that we
currently have no way of relating those stack traces back to a PID so
the programmers can get the stack trace of the hung database connection.
We use the JDBC connection pooling so there's no way to be sure what
stack trace goes to what PID. I gave the developers the postgres call
to get that backend PID through the JDBC connection a few days ago, but
they don't have the resources to get the additional call built into
their programs for up to 1-2 months.

I'm working on the business side to get priorities changed, but it
hasn't happened yet. Mostly because I've got Xymon watching for those
conditions so I can correct them before we get calls into the helpdesk.
Sorry, I'm rambling.

Anyway, I'm trying to attack it from the database side out since I am
not a programmer and can't help with that part. I can do simple CGIs
with bash, but I don't know Java or C or even Perl yet for that matter.
Since you guys are the experts, I'm looking for any way to attack this
problem from the database side. The tips I've gotten about the JDBC
driver and commits are helpful in that it gives our programmers things
to watch out for that we didn't realize, and I'm open to any suggestions
from the list about how I can help attack this.

Since I'm ultimately responsible for database performance and I don't
like being reduced to sitting on the sidelines I'm trying to see what if
anything else my skills can contribute.

As for patting you on the head, I was being sincere. And trying not to
come off sounding like a cocky SOB. :-)

Thanks,

Scot Kreienkamp

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, July 10, 2009 7:02 PM
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp<SKreien@la-z-boy.com>
wrote:

Thanks scott, but I wrote a cgi to combine all of the process info and

allow

me to kill errant queries. So I know how to track down the pid. Thanks

for

trying to help though. :-)

So, what are you looking for, a stack trace dump from java to look
through maybe? (the one that kill -1 or whatever generates? It's been
a few years.) That'll usually give you the context to find out which
thread is where. P.s. no need to pat me on the head like the doggie.
:)