PostgreSQL JDBC - DDL in transactions?

Author

DDL in transactions?

Giuseppe Sacco

2005-03-31, 7:02 am

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Kris Jurka

2005-03-31, 8:04 pm

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

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

http://archives.postgresql.org

Giuseppe Sacco

2005-03-31, 8:04 pm

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Markus Schaber

2005-03-31, 8:04 pm

Hi, Giuseppe,

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Giuseppe Sacco

2005-04-01, 7:02 am

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

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

Kris Jurka

2005-04-01, 7:02 am

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

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

Sponsored Links