Thread: RE: JDBC Drop/Create problem?
Dropping a non-existent table should throw an exception as well as mark any open transaction as aborted. I'd say either: * using autoCommit while checking for existing tables. * commit and begin a new transaction afterwards. * Use temporary tables, so the table doesn't survive the connection. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Greg Speegle [mailto:Greg@10happythings.com] > Sent: Thursday, December 07, 2000 4:35 PM > To: pgsql-interfaces@postgresql.org > Subject: [INTERFACES] JDBC Drop/Create problem? > > > > Scenario: > Want to create a table via JDBC > > Plan: > Drop existing table (if any) > Create new one > > Problem: > If there was no table before, the Statement to drop the table > throws an > exception, which is fine and not a problem. > However, if I then continue to try to create the table, I get > a "table > not found" exception thrown by the Create > Statement. Doing a commit between the two eliminates the problem, but > this doesn't look like how the code > should work, or am I missing something? > > Thanks for input, > > Greg Speegle > BaylorUniversity >
Ah. That explains it. Thanks. Out of curiosity, why does the transaction get marked as aborted? I only ask since others (e.g. Oracle) don't have this behavior. Greg Peter Mount wrote: > Dropping a non-existent table should throw an exception as well as mark any > open transaction as aborted. > > I'd say either: > > * using autoCommit while checking for existing tables. > * commit and begin a new transaction afterwards. > * Use temporary tables, so the table doesn't survive the connection. > > Peter >
I'm not sure if the term's "aborted" (been a horrible week, etc), but as the drop failed, any transaction its contained within must also fail - thats the point of transactions. If it didn't then you could find complex relationships breaking down all over the place. What would be nice would be nested transactions. Then the drop could be placed within its own transaction, and the outer one wouldn't be affected. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Greg Speegle [mailto:Greg@10happythings.com] > Sent: Thursday, December 07, 2000 6:19 PM > To: Peter Mount > Cc: pgsql-interfaces@postgresql.org > Subject: Re: [INTERFACES] JDBC Drop/Create problem? > > > > Ah. That explains it. Thanks. > > Out of curiosity, why does the transaction get marked as aborted? I > only ask since others > (e.g. Oracle) don't have this behavior. > > Greg > > Peter Mount wrote: > > > Dropping a non-existent table should throw an exception as > well as mark any > > open transaction as aborted. > > > > I'd say either: > > > > * using autoCommit while checking for existing tables. > > * commit and begin a new transaction afterwards. > > * Use temporary tables, so the table doesn't survive the connection. > > > > Peter > > >
Today, in a message to Greg Speegle, Peter Mount wrote: > > I'm not sure if the term's "aborted" (been a horrible week, etc), but > as the drop failed, any transaction its contained within must also > fail - thats the point of transactions. Well no, that is not necessarily the point of transactions. It is sufficient to report the error and the calling process can decide whether to continue the transaction using another approach or to abort it by calling rollback. The RDBMS should abort a transaction only when it has no reasonable way to revert to the state before the command that failed. All major RDBMS systems behave this way, hence one cannot be surprised when people expect this behaviour also in Postgresql. This was discussed here several times. > What would be nice would be nested transactions. Then the drop could > be placed within its own transaction, and the outer one wouldn't be > affected. Nested transactions are more general than statement-level abort in that they allow users to define the scope of the nested transaction. It is also the preferred way to cleanly implement statement-level abort. Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.ca (http://www.kraut.ca)
-- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Joachim Achtzehnter [mailto:joachim@kraut.ca] > Sent: Friday, December 08, 2000 6:27 PM > To: pgsql-interfaces@postgresql.org > Subject: RE: [INTERFACES] JDBC Drop/Create problem? > > > Today, in a message to Greg Speegle, Peter Mount wrote: > > What would be nice would be nested transactions. Then the drop could > > be placed within its own transaction, and the outer one wouldn't be > > affected. > > Nested transactions are more general than statement-level > abort in that > they allow users to define the scope of the nested > transaction. It is also > the preferred way to cleanly implement statement-level abort. The problem JDBC has is that it's DatabaseMetaData methods can make several queries. While AutoCommit is off, if one of those fails, the users transaction will also fail. Could be a possible area of confusion for both user and app. Peter
Today, in a message to pgsql-interfaces, Peter Mount wrote: > > The problem JDBC has is that it's DatabaseMetaData methods can make > several queries. While AutoCommit is off, if one of those fails, the > users transaction will also fail. If the JDBC driver under the hood performs several queries in response to a single 'user command' and one of these fails it can still report that command's failure without aborting the whole transaction (assuming the backend supports statement-level aborts, of course). Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.ca (http://www.kraut.ca)
-- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council > -----Original Message----- > From: Joachim Achtzehnter [mailto:joachim@kraut.ca] > Sent: Monday, December 11, 2000 5:29 PM > To: pgsql-interfaces@postgresql.org > Subject: RE: [INTERFACES] JDBC Drop/Create problem? > > > Today, in a message to pgsql-interfaces, Peter Mount wrote: > > > > The problem JDBC has is that it's DatabaseMetaData methods can make > > several queries. While AutoCommit is off, if one of those fails, the > > users transaction will also fail. > > If the JDBC driver under the hood performs several queries in > response to > a single 'user command' and one of these fails it can still > report that > command's failure without aborting the whole transaction (assuming the > backend supports statement-level aborts, of course). Unless 7.1 now supports statement level aborts (and it's possible I've missed it if it has) this is where the problem lies. The backend currently does abort the whole transaction. Peter
Today, in a message to pgsql-interfaces, Peter Mount wrote: > > Unless 7.1 now supports statement level aborts (and it's possible I've > missed it if it has) this is where the problem lies. The backend > currently does abort the whole transaction. Exactly. I was speaking about postgresql in general, not JDBC specificly. In my previous email I was merely pointing out that if postgresql, i.e. the backend, did support statement-level aborts (which it doesn't currently) then JDBC could too. There are no special JDBC issues that would require aborting the whole transaction. Joachim -- work: joachima@realtimeint.com (http://www.realtimeint.com) private: joachim@kraut.ca (http://www.kraut.ca)