Re: multiple threads inserting into the same table

Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] wrote:
> pg_stat_activity says those five threads are doing nothing except
> sitting around with open transactions. You sure you don't have a bug on
> the application side?
>
> regards, tom lane

This is a java app. A thread dump reveals that these 5 threads are all
asleep on a socket read to postgres (see below). DbUtils.java:2265 is:

session.connection().createStatement()
.executeUpdate(((DatabaseInsert) insertObject).getInsertStmt(session));

This generates and executes a single SQL insert. Since, as you point
out, postgres seems to think that this transaction isn't doing anything,
it's hard to figure out what the read is doing.

Brian

"DatabasePool.Thread1" prio=10 tid=0x27f04c00 nid=0x3b38 runnable
[0x29e27000..0x29e281b0]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
at
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1182)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
- locked <0x8975c878> (a org.postgresql.core.v3.QueryExecutorImpl)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282)
at
com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
at
com.timestock.tess.util.DbUtils$DatabaseInsertTask.insertObject(DbUtils.java:2265)
at
com.timestock.tess.util.DbUtils$DatabaseInsertTask.call(DbUtils.java:2200)
at
com.timestock.tess.util.DbUtils$DatabaseInsertTask.call(DbUtils.java:2157)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)

On Mon, Mar 23, 2009 at 2:25 PM, Brian Cox <brian(dot)cox(at)ca(dot)com> wrote:
> Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] wrote:
>>
>> pg_stat_activity says those five threads are doing nothing except
>> sitting around with open transactions.  You sure you don't have a bug on
>> the application side?
>>
>>                        regards, tom lane
>
> This is a java app. A thread dump reveals that these 5 threads are all
> asleep on a socket read to postgres (see below). DbUtils.java:2265 is:
>
> session.connection().createStatement() .executeUpdate(((DatabaseInsert)
> insertObject).getInsertStmt(session));
>
> This generates and executes a single SQL insert. Since, as you point out,
> postgres seems to think that this transaction isn't doing anything,
> it's hard to figure out what the read is doing.

Might you have a firewall that's killing the connections? What does
netstat -an on the client side say about these connections?

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Mon, Mar 23, 2009 at 2:25 PM, Brian Cox <brian(dot)cox(at)ca(dot)com> wrote:
>> This generates and executes a single SQL insert. Since, as you point out,
>> postgres seems to think that this transaction isn't doing anything,
>> it's hard to figure out what the read is doing.

> Might you have a firewall that's killing the connections? What does
> netstat -an on the client side say about these connections?

netstat will probably say the connection is open on both sides ---
otherwise the sockets would have closed. It looks like both sides
still think the connection is open. A firewall timeout is still
a possibility, but you'd have had to have a fairly long idle time
for that to happen. Are any of the threads issuing commands that might
have run for very long intervals (tens of minutes)?

regards, tom lane

Brian Cox <brian(dot)cox(at)ca(dot)com> writes:
> This is a java app. A thread dump reveals that these 5 threads are all
> asleep on a socket read to postgres (see below).

It seems clear that what you've got isn't a performance problem.
May I suggest taking it to pgsql-jdbc? The folk there are more likely
to be able to help you figure out what's wrong than most of us.

regards, tom lane