PostgreSQL JDBC - DDL hanging when different connection is left open

Author

DDL hanging when different connection is left open

Nathan McEachen

2006-01-24, 3:24 am

In order to execute a DDL statement on a table using a JDBC
connection, must all other JDBC connections that have performed a read
query on that table be closed?

This is what I am seeing:

connection1 performes query: "SELECT * FROM my_table";
connection 2 performs DDL: "ALTER TABLE my_table ADD COLUMN my_col INTEGER";

connection 2 seems to hang unless connection1 is closed (i.e.
conneciton1.close() ).

This is proving to be a problem when I pool my JDBC connections in my
application, as connections that are returned to the pool are not
closed. I tried chaning the transaction isolation levels through JDBC
but that did not change anything.

Is there a way for me to keep my JDBC connections open, yet still
perform DDL statements?

Thanks in advance,

-Nathan

--
In theory, there is no difference between theory and practice. But, in practice, there is.

--Jan L.A. van de Snepscheut

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2006-01-24, 3:24 am

Nathan McEachen wrote:

> connection1 performes query: "SELECT * FROM my_table";
> connection 2 performs DDL: "ALTER TABLE my_table ADD COLUMN my_col
> INTEGER";
>
>
> connection 2 seems to hang unless connection1 is closed (i.e.
> conneciton1.close() ).

Most likely, connection 1 has autocommit off and you have forgotten to
commit or rollback the transaction that your SELECT opened.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Nathan McEachen

2006-01-24, 3:24 am

Oliver Jowett wrote:

> Nathan McEachen wrote:
>
>
>
> Most likely, connection 1 has autocommit off and you have forgotten to
> commit or rollback the transaction that your SELECT opened.
>
Thanks, I *thought* I had checked that, being the thorough and diligent
person that I am. :) However, I am using AspectJ on this project. I
just figured out that I had a faulty pointcut definition. So this was
not a JDBC issue, rather a "Nathan better pay closer attention to how he
defines his pointcut advice" issue. Basically some aspect advice was
preventing the connection from committing, hence connection 2 was hanging.

Sorry for the faulty post.

For all of you AOP people:

public abstract pointcut dumbNathanPosts();

Object around() : dumbNathanPosts()
{
// just ignore what Nathan says. Do not call proceed();
}

-Nathan

--
In theory, there is no difference between theory and practice. But, in practice, there is.

--Jan L.A. van de Snepscheut

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links