Re: Problem with jdbc connection behavior

I'm working at the moment for transactions and drop tables, I shut down
the server and enabled the logging you suggested and restarted it. I
think this is now the correct behavior I should expect from the server.
When I do a commit after an exception with no savepoint it rolls the
entire transaction back or I can roll back to a savepoint before the
commit, and in either case my jdbc connection continues to work after
that commit. I do not know what caused the jdbc connection to stop
working entirely when I caught that exception before. Also my drop table
is now working the only thing I did between was shutdown the server
enable some more logging and restart it. I am going to have to do some
more testing and hope it was a one time glitch that does not start
happening again. Can you think of anything I could have screwed up in
the server that would reset when cycled?

I know what you mean with ddl in transactions, I already had to tell
some coders they could not manage their own transactions for create and
drop tables. They have to be single item transactions in the dao for
consistency. Sybase at least let me set 'ddl in tran' on, though I am
not sure on the point in that setting, because I can just do a commit
every time rather than turn it off. Oracle does auto commit and does not
even give a warning that the transaction you think you have is now shot.
I prefer the PostgreSQL method.

Jerel

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka(at)gmail(dot)com] On Behalf Of Heikki
Linnakangas
Sent: Wednesday, February 14, 2007 2:08 PM
To: Blakely, Jerel (Mission Systems)
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Problem with jdbc connection behavior

Blakely, Jerel (Mission Systems) wrote:
> The testcase code currently looks like this, I have tried rollback
> already. Since commits happen after each item it didn't change
anything.
> ...

Hmm. I can't see any apparent reason why that wouldn't work. I'd suggest
setting these parameters in the server's postgresql.conf file to log all
the connections and statements and commits:

log_connections = on
log_disconnections = on
log_statement = 'all'

I'm thinking there's something in your environment inhibiting commits or
or something like that.

> Since my other post I now have noticed a problem with the drop table
> never returning.
> It is a simple: stmt.executeUpdate("DROP TABLE TEMP_TABLE");

Can you elaborate? Are you executing the drop table while the test case
is running, or is it just generally not returning?

> I have verified these test cases against Firebird, Sybase, and Oracle
> with no problems, I am beginning to think my Postgres install is
cursed.

Yeah, I hate it when that happens ;).

Something you should be aware of is that unlike in most other DBMSs, DDL
statements like CREATE/DROP TABLE are transactional in PostgreSQL. If
you CREATE TABLE in one connection, it's not visible to others until
commit.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com