Thread: Finding out on exactly what I am stuck
Hi,
I've got a JDBC client which got hung in reading response to a DML statement:
[junit] "Thread-11201" prio=1 tid=0x0000002ac9df56e0 nid=0x3995
runnable [0x0000000041a70000..0x0000000041a70db0]
[junit] at java.net.SocketInputStream.socketRead0(Native Method)
[junit] at java.net.SocketInputStream.read(SocketInputStream.java:129)
[junit] at
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
[junit] at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
[junit] at
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
[junit] at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
[junit] at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1166)
[junit] at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
[junit] - locked <0x0000002ab85b1918> (a
org.postgresql.core.v3.QueryExecutorImpl)
[junit] at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
[junit] at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
[junit] at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:304)
I can also identify a couple of back-end processes presumably related
to the client:
postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12496) idle in transaction
postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12497) idle in transaction
postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12498) UPDATE waiting
postgres 14738 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12499) idle
postgres 14739 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12500) idle
postgres 14740 9868 0 22:34 ? 00:00:00 postgres: pkovacs
chemaxon 127.0.0.1(12501) idle
Please, could you suggest me a couple of things which I can/should
check and how I can do it? (Locks/contention would be an obvious
candidate, but I am fairly sure that the database has only one client:
mine.)
Thanks
Peter
Péter Kovács <maxottovonstirlitz@gmail.com> wrote: > postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs > chemaxon 127.0.0.1(12496) idle in transaction > postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs > chemaxon 127.0.0.1(12497) idle in transaction > postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs > chemaxon 127.0.0.1(12498) UPDATE waiting I would start by looking at pg_locks to see if your update is blocked by one of the connections which is idle in a transaction. Any locks won't be released until the idle transaction commits or rolls back. Then look at pg_stat_activity to check timestamps. Be sure your software doesn't have any code path which leaves things dangling. (Since you seem to be using Java, I recommend proper use of try/finally blocks to ensure resources are released.) -Kevi
Thank you, Kevin, for the tip.
It appears nobody is waiting for a lock (all current locks are granted):
chemaxon=# select
pg_class.relname,pg_locks.pid,pg_locks.mode,pg_locks.granted from
pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation order
by pg_locks.pid;
relname | pid | mode | granted
----------------------------------------+-------+------------------+---------
pg_class | 14735 | AccessShareLock | t
pg_class_oid_index | 14735 | AccessShareLock | t
prop_table_speedtest | 14735 | AccessShareLock | t
struc_table_speedtest_pkey | 14735 | AccessShareLock | t
struc_table_speedtest_pkey | 14735 | RowExclusiveLock | t
struc_table_speedtest_cd_id_seq | 14735 | AccessShareLock | t
struc_table_speedtest_fx | 14735 | AccessShareLock | t
pg_namespace_oid_index | 14735 | AccessShareLock | t
struc_table_speedtest_ul | 14735 | RowExclusiveLock | t
pg_namespace_nspname_index | 14735 | AccessShareLock | t
pg_class_relname_nsp_index | 14735 | AccessShareLock | t
struc_table_speedtest | 14735 | AccessShareLock | t
struc_table_speedtest | 14735 | RowExclusiveLock | t
struc_table_speedtest_fx | 14735 | RowExclusiveLock | t
struc_table_speedtest_hx | 14735 | AccessShareLock | t
struc_table_speedtest_hx | 14735 | RowExclusiveLock | t
prop_table_speedtest_pkey | 14735 | AccessShareLock | t
struc_table_speedtest_ul_update_id_seq | 14735 | AccessShareLock | t
pg_namespace | 14735 | AccessShareLock | t
struc_table_speedtest | 14737 | RowExclusiveLock | t
struc_table_speedtest | 14737 | ExclusiveLock | t
struc_table_speedtest_fx | 14737 | RowExclusiveLock | t
struc_table_speedtest_pkey | 14737 | RowExclusiveLock | t
struc_table_speedtest_hx | 14737 | RowExclusiveLock | t
pg_class_relname_nsp_index | 15488 | AccessShareLock | t
pg_class | 15488 | AccessShareLock | t
pg_class_oid_index | 15488 | AccessShareLock | t
pg_locks | 15488 | AccessShareLock | t
(28 rows)
Any more ideas?
Thanks
Peter
2009/4/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>> postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12496) idle in transaction
>> postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12497) idle in transaction
>> postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs
>> chemaxon 127.0.0.1(12498) UPDATE waiting
>
> I would start by looking at pg_locks to see if your update is blocked
> by one of the connections which is idle in a transaction. Any locks
> won't be released until the idle transaction commits or rolls back.
> Then look at pg_stat_activity to check timestamps. Be sure your
> software doesn't have any code path which leaves things dangling.
> (Since you seem to be using Java, I recommend proper use of
> try/finally blocks to ensure resources are released.)
>
> -Kevi
>
Overlooked your mentioning to timestamps in pg_stat_activity. What patterns do I need to look for there. Thanks Peter 2009/4/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>: > Péter Kovács <maxottovonstirlitz@gmail.com> wrote: >> postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs >> chemaxon 127.0.0.1(12496) idle in transaction >> postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs >> chemaxon 127.0.0.1(12497) idle in transaction >> postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs >> chemaxon 127.0.0.1(12498) UPDATE waiting > > I would start by looking at pg_locks to see if your update is blocked > by one of the connections which is idle in a transaction. Any locks > won't be released until the idle transaction commits or rolls back. > Then look at pg_stat_activity to check timestamps. Be sure your > software doesn't have any code path which leaves things dangling. > (Since you seem to be using Java, I recommend proper use of > try/finally blocks to ensure resources are released.) > > -Kevi >
The pg_stat_activity table basically shows the same picture as the process list: - two sessions idle and marked "in transaction" - one session waiting with an UPDATE as the "current_query" - a couple of more user sessions idle (and not marked "in transaction") Time stamps are recent and consistent with what I'd expect. (The client hung right after it had been started and all timestamps are around that time.) Returning to the the pg_locks view, I've started wondering about this sentence of the documentation (http://www.postgresql.org/docs/8.3/interactive/view-pg-locks.html): "If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock." Which column is meant here? Also, what is the difference between permanent and virtual transaction ID, if any? Thanks Peter 2009/4/10 Péter Kovács <maxottovonstirlitz@gmail.com>: > Overlooked your mentioning to timestamps in pg_stat_activity. What > patterns do I need to look for there. > > Thanks > Peter > > 2009/4/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>: >> Péter Kovács <maxottovonstirlitz@gmail.com> wrote: >>> postgres 14727 9868 0 22:34 ? 00:00:00 postgres: pkovacs >>> chemaxon 127.0.0.1(12496) idle in transaction >>> postgres 14735 9868 0 22:34 ? 00:00:00 postgres: pkovacs >>> chemaxon 127.0.0.1(12497) idle in transaction >>> postgres 14737 9868 0 22:34 ? 00:00:00 postgres: pkovacs >>> chemaxon 127.0.0.1(12498) UPDATE waiting >> >> I would start by looking at pg_locks to see if your update is blocked >> by one of the connections which is idle in a transaction. Any locks >> won't be released until the idle transaction commits or rolls back. >> Then look at pg_stat_activity to check timestamps. Be sure your >> software doesn't have any code path which leaves things dangling. >> (Since you seem to be using Java, I recommend proper use of >> try/finally blocks to ensure resources are released.) >> >> -Kevi >> >
Péter Kovács <maxottovonstirlitz@gmail.com> wrote: > It appears nobody is waiting for a lock (all current locks are > granted): Then I would run the UPDATE on another connection with EXPLAIN in front of it. That would show you the plan it will use, which might give a clue why it is running so long. Also, a connection which is "idle in transaction" will prevent normal maintenance and may lead to table bloat which can kill performance. It could be that your problem is there. You might want to check how big your database is (using du or something similar), and make sure that the size seems reasonable. I hope this helps. -Kevin
Running the UPDATE on another connection is an interesting suggestion.
I hope I will remember it next time I run into a similar problem (or
remember reviewing my mail archive :-) ).
In the mean time, I found the source of the problem: the client
application made SQL calls in invalid sequences. (I knew all along
that something similar was at work here, but I wanted to give
PostgreSQL's diagnostic facilities a chance to help me locate the
problem -- and learn about those facilities along the way. A bit off
topic: I ran the same test case against Oracle as well and Oracle
appeared to handle more robustly the client application error: it gave
a more informative error message ("protocol violation"); and, also,
the error message was emitted much closer to the place in the
execution path where the actual programming error occurred.)
Thank you so much for all your suggestions.
Peter
2009/4/10 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>> It appears nobody is waiting for a lock (all current locks are
>> granted):
>
> Then I would run the UPDATE on another connection with EXPLAIN in
> front of it. That would show you the plan it will use, which might
> give a clue why it is running so long.
>
> Also, a connection which is "idle in transaction" will prevent normal
> maintenance and may lead to table bloat which can kill performance.
> It could be that your problem is there. You might want to check how
> big your database is (using du or something similar), and make sure
> that the size seems reasonable.
>
> I hope this helps.
>
> -Kevin
>
Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
> I found the source of the problem: the client
> application made SQL calls in invalid sequences.
> I ran the same test case against Oracle as well
> it gave a more informative error message ("protocol violation");
> and, also, the error message was emitted much closer to the place in
> the execution path where the actual programming error occurred.
Could you share information about what you did, what you would like or
expect as an error message, and what you got instead? It might help
us improve PostgreSQL.
-Kevin
Reproducing the exact sequence of events would be very difficult.
Essentially, the same JDBC connection was used simultaneously (in
multiple threads) for various selects and updates, which is a bad
enough thing to do, I presume, as the connection instance is not
thread-safe. I don't think that much should be invested in helping
detect programming errors like this. Let the onus of dealing with such
situations rest on the application programmers -- it is their fault
anyway. (Oracle makes it somewhat [not significantly much] more easier
to do this, but I find it normal that customers get at least some
extra features for their bucks.)
Thanks
Peter
2009/4/13 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Péter Kovács <maxottovonstirlitz@gmail.com> wrote:
>> I found the source of the problem: the client
>> application made SQL calls in invalid sequences.
>
>> I ran the same test case against Oracle as well
>
>> it gave a more informative error message ("protocol violation");
>> and, also, the error message was emitted much closer to the place in
>> the execution path where the actual programming error occurred.
>
> Could you share information about what you did, what you would like or
> expect as an error message, and what you got instead? It might help
> us improve PostgreSQL.
>
> -Kevin
>
Péter Kovács <maxottovonstirlitz@gmail.com> wrote: > 2009/4/13 Kevin Grittner <Kevin.Grittner@wicourts.gov>: >> Péter Kovács <maxottovonstirlitz@gmail.com> wrote: >>> I found the source of the problem: the client >>> application made SQL calls in invalid sequences. >> Could you share information about what you did, what you would like >> or expect as an error message, and what you got instead? It might >> help us improve PostgreSQL. > the same JDBC connection was used simultaneously (in > multiple threads) for various selects and updates That would do it, all right -- the JDBC spec says that applications should not assume support for concurrent access by multiple threads. The only exception I can recall offhand is that Statement.cancel may be invoked by a separate thread. Based on your report, who knows -- maybe we could guard against this in the JDBC driver.... Thanks for the information. -Kevin
Is there a way to make Postgres do case insensitive like searches? I know you can use ilike instead, but I don't have control of the software. Is there a plan to enable this in the future? Can I recompile Postgres to make like searches do the same as ilike, or is it easier to create a new character encoding that will be case insensitive? Thanks, Mark
In 8.4 there is a citext contrib module that should do the trick. Cheers, Ken On Mon, Apr 13, 2009 at 09:59:11AM -0600, Mark Dueck wrote: > Is there a way to make Postgres do case insensitive like searches? I > know you can use ilike instead, but I don't have control of the > software. Is there a plan to enable this in the future? > > Can I recompile Postgres to make like searches do the same as ilike, or > is it easier to create a new character encoding that will be case > insensitive? > > Thanks, > Mark > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >