We are hitting a situation where REINDEX is resulting in postgresql to go
to dead lock state* for ever*. On debugging the issue we found that
3 connections are going in to some dead lock state.
1. *idle in transaction *
2. *REINDEX waiting *
3. *SELECT waiting*
All these
On 2/6/2013 1:28 AM, Anoop K wrote:
3 connections are going in to some dead lock state.
1. *idle in transaction *
2. *REINDEX waiting *
3. *SELECT waiting *
you need to track down what resources are being locked by those
processes, by joining pg_stat_activity against pg_locks and
On Wed, Feb 6, 2013 at 1:28 AM, Anoop K anoo...@gmail.com wrote:
We are hitting a situation where REINDEX is resulting in postgresql to go to
dead lock state for ever. On debugging the issue we found that
3 connections are going in to some dead lock state.
idle in transaction
REINDEX waiting
We analyzed the application side. It doesn't seem to be create a
transaction and keep it open. StackTraces indicate that it is BLOCKED in
JDBC openConnection.
Any JDBC driver issue or other scenarios which can result in *idle in
transaction* ?
Anoop
On Mon, Feb 11, 2013 at 11:16 AM, Sergey
On 2/10/2013 9:55 PM, Anoop K wrote:
We analyzed the application side. It doesn't seem to be create a
transaction and keep it open. StackTraces indicate that it is BLOCKED
in JDBC openConnection.
Any JDBC driver issue or other scenarios which can result in *idle in
transaction* ?
JDBC has
Yes, we do that.
On Mon, Feb 11, 2013 at 11:53 AM, John R Pierce pie...@hogranch.com wrote:
On 2/10/2013 9:55 PM, Anoop K wrote:
We analyzed the application side. It doesn't seem to be create a
transaction and keep it open. StackTraces indicate that it is BLOCKED in
JDBC openConnection.
On Sun, Feb 10, 2013 at 9:55 PM, Anoop K anoo...@gmail.com wrote:
We analyzed the application side. It doesn't seem to be create a transaction
and keep it open. StackTraces indicate that it is BLOCKED in JDBC
openConnection.
Any JDBC driver issue or other scenarios which can result in idle in
On 2/10/2013 10:25 PM, Anoop K wrote:
Yes, we do that.
well, you need to figure out which connection isn't doing that, as one
of them is leaving a long running transaction pending.
as I said, join pg_stat_activity.pid with pg_locks and whatever to find
out what tables its locking on.
You might want to consider adding a pooler like pgbouncer to the
equation so that the pooler is what runs out of connections and not
the database. Then you could at least get into it to fix things.
On Thu, Feb 7, 2013 at 9:04 PM, Anoop K anoo...@gmail.com wrote:
REINDEX was for the whole
I have the setup in problem state. But I am not able to make psql
connections to view the lock details.
psql connections are hanging. Is there any other info which can be
collected in this state ?
Also we don't know the steps to reproduce the issue.
On Thu, Feb 7, 2013 at 1:23 PM, Albe Laurenz
On Thu, Feb 7, 2013 at 2:08 PM, Anoop K anoo...@gmail.com wrote:
I have the setup in problem state. But I am not able to make psql
connections to view the lock details.
psql connections are hanging. Is there any other info which can be collected
in this state ?
Try attaching each process
It sounds like you're running out of connections. Have you tried
connecting as postgres? It has 2 or 3 superuser connections reserved
by default.
On Thu, Feb 7, 2013 at 1:38 AM, Anoop K anoo...@gmail.com wrote:
I have the setup in problem state. But I am not able to make psql
connections to
We did run out of conns as our processes which tried to connect (over few
days) got hung in '*startup waiting state'. *Even superuser conns are also
over.
Thanks
Anoop
On Thu, Feb 7, 2013 at 3:37 PM, Scott Marlowe scott.marl...@gmail.comwrote:
It sounds like you're running out of connections.
I dont have C triggers. I can attach gdb and get stacktrace. Wondering if
it will take the processes out of problem state.
Thanks
Anoop
On Thu, Feb 7, 2013 at 3:33 PM, Pavan Deolasee pavan.deola...@gmail.comwrote:
On Thu, Feb 7, 2013 at 2:08 PM, Anoop K anoo...@gmail.com wrote:
I have the
So have you tried connecting as a superuser?
On Thu, Feb 7, 2013 at 3:19 AM, Anoop K anoo...@gmail.com wrote:
We did run out of conns as our processes which tried to connect (over few
days) got hung in 'startup waiting state'. Even superuser conns are also
over.
Thanks
Anoop
On Thu, Feb
Actually some of our processes connect as superuser. So even that is over
and is in hung state.
On Thu, Feb 7, 2013 at 4:29 PM, Scott Marlowe scott.marl...@gmail.comwrote:
So have you tried connecting as a superuser?
On Thu, Feb 7, 2013 at 3:19 AM, Anoop K anoo...@gmail.com wrote:
We did
Processes should always connect by some other role with suspendable
superuser connections for situations like this. Do your processes
really need superuser access all the time? If you could turn it off
for a bit you could get into your database and troubleshoot from there
first. Not being able
Note that if those processes use persistent connections you'll need to
restart them to free up the connections. In the meantime you can use
the step of renaming your superuser account. Then cutting all
superuser conns and turning off superuser of postgres user
temporarily. I wouldn't do this
I will try. Here are the gdb stacktraces of hung processes.
idle in transaction
#0 0x7fbdfaceb3e2 in recv () from /lib64/libc.so.6
#1 0x0058bde6 in secure_read ()
#2 0x0059697b in ?? ()
#3 0x00596d7b in pq_getbyte ()
#4 0x006334af in PostgresMain ()
#5
On Wed, Feb 6, 2013 at 11:55 PM, Anoop K anoo...@gmail.com wrote:
We are hitting a situation where REINDEX is resulting in postgresql to go to
dead lock state for ever. On debugging the issue we found that
3 connections are going in to some dead lock state.
idle in transaction
REINDEX
Anoop K anoo...@gmail.com wrote:
I will try. Here are the gdb stacktraces of hung processes.
idle in transaction
Have you tried `kill -SIGTERM` on the idle in transaction pid?
-Kevin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
In an attempt to get access, I ended up killing a postgres process and the
whole thing *recovered from hang* state. Now don't have more data points to
debug.
I feel the trigger is the connection in *idle in transaction* state. On
examining the application side(Java) stacktrace, I found that other
On Thu, Feb 7, 2013 at 8:19 PM, Anoop K anoo...@gmail.com wrote:
In an attempt to get access, I ended up killing a postgres process and the
whole thing recovered from hang state. Now don't have more data points to
debug.
Sorry, I was going to ask what REINDEX was really indexing ? System
Pavan Deolasee pavan.deola...@gmail.com writes:
Sorry, I was going to ask what REINDEX was really indexing ? System
tables ?
The stack trace for the REINDEX process includes ReindexDatabase(), so
if it was running as a superuser it would be trying to reindex system
catalogs too. We don't
REINDEX was for the whole database. It seems REINDEX was blocked by the *idle
in transaction* process.
What we are not able to explain is how that connection went in to *idle in
transaction* state. The app stacktrace confirms that app (JDBC) is trying
to open a connection. We do close connection
We are hitting a situation where REINDEX is resulting in postgresql to go
to dead lock state* for ever*. On debugging the issue we found that
3 connections are going in to some dead lock state.
1. *idle in transaction *
2. *REINDEX waiting *
3. *SELECT waiting*
All these
Anoop K wrote:
We are hitting a situation where REINDEX is resulting in postgresql to go to
dead lock state for ever.
On debugging the issue we found that
3 connections are going in to some dead lock state.
1.idle in transaction
2.REINDEX waiting
3.SELECT waiting
All these
27 matches
Mail list logo
