Re: DDL in transactions?

From: Kris Jurka <books(at)ejurka(dot)com>
To: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: DDL in transactions?
Date: 2005-03-31 16:09:34
Message-ID: Pine.BSO.4.56.0503311102060.12053@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Giuseppe Sacco 2005-03-31 17:24:03 Re: DDL in transactions?
Previous Message Giuseppe Sacco 2005-03-31 07:32:14 DDL in transactions?