Re: DDL in transactions?

On Thu, 31 Mar 2005, Giuseppe Sacco wrote:

> Hi,
> I have a question about 7.4 jdbc driver. I am executing this code:
>
> --------------------
> conn=datasource.getPooledConnection().getConnection();
> conn.setAutoCommit(false);
> stmt=conn.createStatement();
>
> stmt.executeUpdate("CREATE TABLE X (X VARCHAR(100))");
> //stmt.commit(); // <= Look at this line
> stmt.executeUpdate("INSERT INTO X VALUES ('string')");
> stmt.commit();
>
> stmt.close();
> conn.close();
> --------------------
>
> And I found that, using postgresql jdbc driver, I need to add the
> commit() call that is commented out, otherwise I get an error like
> 'relation X does not exists'.
>
> My question is: why? Shouldn't DDL be executed immediately?

No.  DDL in postgresql is fully transactable and may be rolled back or
committed.  That doesn't explain why the above code doesn't work.
Because the two executeUpdates are on the same Statement on the same
Connection the second execution should see the newly created table because
it is in the same transaction.  Perhaps you are not showing us the real
code you are using and you actually have two connections?

Kris Jurka