Postgres Pro
Downloads
Home   >   mailing lists

Thread: DDL in transactions?

DDL in transactions?

From
Giuseppe Sacco
Date:
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

Re: DDL in transactions?

From
Kris Jurka
Date:

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

Re: DDL in transactions?

From
Giuseppe Sacco
Date:
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.

Re: DDL in transactions?

From
Markus Schaber
Date:
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

Re: DDL in transactions?

From
Giuseppe Sacco
Date:
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

Re: DDL in transactions?

From
Kris Jurka
Date:

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