Re: DDL in transactions?

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?

Thank you for your response,
Giuseppe Sacco

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

Kris Jurka wrote:
[...]
>>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?

Thanks Kris, I am going to check it.

Hi, Giuseppe,

Kris Jurka schrieb:
>>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?

You can simply try this out via psql:

lwgeom=# begin;
BEGIN
lwgeom=# create table tester (a int);
CREATE TABLE
lwgeom=# insert into tester values (1);
INSERT 281855490 1
lwgeom=# rollback;
ROLLBACK
lwgeom=# select * from tester;
ERROR: relation "tester" does not exist
lwgeom=#

HTH,
Markus

Il giorno gio, 31-03-2005 alle 11:09 -0500, Kris Jurka ha scritto:
[...]
> > 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?

Hi Kris,
you was right: I used two different connections.

BTW, is it possible to disable this behaviour and have DDL statement
immediately executed?

Thanks,
Giuseppe

On Fri, 1 Apr 2005, Giuseppe Sacco wrote:

> BTW, is it possible to disable this behaviour and have DDL statement
> immediately executed?
>

No. If you want it to commit you must issue a commit.

Kris Jurka