Re: [BUGS] BUG #1347: Bulk Import stopps after a while (

Bahadur Singh wrote:
> Finally I am able to force the driver to do the DEBUG
> level output on the console.
>
> err.zip contains Debug level output.

OK, it looks like it is the connection deadlock issue, but triggered by
a case I hadn't considered. For some reason every query in the batch is
creating a new named statement on the backend .. and the statement
cleanup process that happens before query execution is flooding
statement Close commands down the connection on the second batch
execution, which appears to deadlock the connection. The exact behaviour
will be fairly unpredicatable as it depends on when the JVM does garbage
collection.

I will try to find some time to investigate this properly tomorrow.

-O

On Wed, 15 Dec 2004, Oliver Jowett wrote:

> OK, it looks like it is the connection deadlock issue, but triggered by
> a case I hadn't considered. For some reason every query in the batch is
> creating a new named statement on the backend .. and the statement
> cleanup process that happens before query execution is flooding
> statement Close commands down the connection on the second batch
> execution, which appears to deadlock the connection. The exact behaviour
> will be fairly unpredicatable as it depends on when the JVM does garbage
> collection.
>

Any news on this? In general it seems like the whole statement/portal
cleanup is too lazy. These things can take up significant backend
resources and I would have expected calling ResultSet.close() and/or
Statement.close() to actually close something (perhaps not immediately,
but at least on the next network trip).

Kris Jurka

Kris Jurka wrote:
> On Wed, 15 Dec 2004, Oliver Jowett wrote:
>
>>OK, it looks like it is the connection deadlock issue, but triggered by
>>a case I hadn't considered. For some reason every query in the batch is
>>creating a new named statement on the backend .. and the statement
>>cleanup process that happens before query execution is flooding
>>statement Close commands down the connection on the second batch
>>execution, which appears to deadlock the connection. The exact behaviour
>>will be fairly unpredicatable as it depends on when the JVM does garbage
>>collection.
>
> Any news on this?

I didn't get a chance to look at it before going on leave. I
won't be able to look at it before early January.

> In general it seems like the whole statement/portal
> cleanup is too lazy. These things can take up significant backend
> resources and I would have expected calling ResultSet.close() and/or
> Statement.close() to actually close something (perhaps not immediately,
> but at least on the next network trip).

I don't have the code to hand, but from memory an explicit
close of the Query does exactly this -- it clears the
reference which causes it to be immeditately enqueued, and
the next query will do cleanup when it polls the reference
queue.

The GC-driven enqueue only kicks in if the object holding
the statement info becomes garbage without being closed.

The reference queue stuff seems to be working as designed.
The problem is that we should be taking the Close messages
we send into account when trying to avoid protocol
deadlocks, but currently we don't, so a big batch of Close
messages at once break things.

I was more worried about why a batch update was generating
so many individual named statements.. it should either use
zero (Statement.addBatch) or one
(PreparedStatement.addBatch) named statement, not N statements.

-O

On Thu, 23 Dec 2004, Oliver Jowett wrote:

> I don't have the code to hand, but from memory an explicit
> close of the Query does exactly this -- it clears the
> reference which causes it to be immeditately enqueued, and
> the next query will do cleanup when it polls the reference
> queue.

This is true for the Query/Portal objects, the problem is that the
Statement/ResultSets aren't closing the underlying objects. Will fix.

> I was more worried about why a batch update was generating
> so many individual named statements.. it should either use
> zero (Statement.addBatch) or one
> (PreparedStatement.addBatch) named statement, not N statements.
>

Ahhh. The protocol is a problem, but this named statement misuse is
triggering it. I can take a look at that.

Kris Jurka

On Wed, 22 Dec 2004, Kris Jurka wrote:

> Ahhh. The protocol is a problem, but this named statement misuse is
> triggering it. I can take a look at that.
>

Ok, armed with this insight from Oliver, the problem was an easy find and
fix.

Bahadur, I've put up a new set of development jar files at:

http://www.ejurka.com/pgsql/jars/bahadur/

if you'd like to test it out.

Kris Jurka

Kris,

The new updated jar from you works perfectly for my
test environment and I did some tests with different
batch sizes of (20000, 30000, 400000 etc), This works
fine, even the time of import in various situations is
consistent. If you need any feedback from me about
logs /debug outputs. Please write me, I can send you.

Many thanks for your support.

Best regards
Bahadur

--- Kris Jurka <books(at)ejurka(dot)com> wrote:

>
>
> On Wed, 22 Dec 2004, Kris Jurka wrote:
>
> > Ahhh. The protocol is a problem, but this named
> statement misuse is
> > triggering it. I can take a look at that.
> >
>
> Ok, armed with this insight from Oliver, the problem
> was an easy find and
> fix.
>
> Bahadur, I've put up a new set of development jar
> files at:
>
> http://www.ejurka.com/pgsql/jars/bahadur/
>
> if you'd like to test it out.
>
> Kris Jurka
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com