pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC Drop/Create problem?
9+ messages / 3 participants
[nested] [flat]
* JDBC Drop/Create problem?
@ 2000-12-07 16:34 Greg Speegle <Greg@10happythings.com>
0 siblings, 0 replies; 9+ messages in thread
From: Greg Speegle @ 2000-12-07 16:34 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-07 16:51 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Mount @ 2000-12-07 16:51 UTC (permalink / raw)
To: 'Greg Speegle' <Greg@10happythings.com>; pgsql-interfaces
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JDBC Drop/Create problem?
@ 2000-12-07 18:19 Greg Speegle <Greg@10happythings.com>
parent: Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Greg Speegle @ 2000-12-07 18:19 UTC (permalink / raw)
To: Peter Mount <petermount@maidstone.gov.uk>; +Cc: pgsql-interfaces
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-08 08:23 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Mount @ 2000-12-08 08:23 UTC (permalink / raw)
To: 'Greg Speegle' <Greg@10happythings.com>; +Cc: pgsql-interfaces
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
> >
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-08 18:27 Joachim Achtzehnter <joachim@kraut.ca>
parent: Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Joachim Achtzehnter @ 2000-12-08 18:27 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-11 09:05 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Mount @ 2000-12-11 09:05 UTC (permalink / raw)
To: 'Joachim Achtzehnter' <joachim@kraut.ca>; pgsql-interfaces
--
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-11 17:29 Joachim Achtzehnter <joachim@kraut.ca>
parent: Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Joachim Achtzehnter @ 2000-12-11 17:29 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-12 09:13 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Mount @ 2000-12-12 09:13 UTC (permalink / raw)
To: pgsql-interfaces
--
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: JDBC Drop/Create problem?
@ 2000-12-12 17:38 Joachim Achtzehnter <joachim@kraut.ca>
parent: Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Joachim Achtzehnter @ 2000-12-12 17:38 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2000-12-12 17:38 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-07 16:34 JDBC Drop/Create problem? Greg Speegle <Greg@10happythings.com>
2000-12-07 16:51 RE: JDBC Drop/Create problem? Peter Mount <petermount@maidstone.gov.uk>
2000-12-07 18:19 ` Greg Speegle <Greg@10happythings.com>
2000-12-08 08:23 RE: JDBC Drop/Create problem? Peter Mount <petermount@maidstone.gov.uk>
2000-12-08 18:27 ` Joachim Achtzehnter <joachim@kraut.ca>
2000-12-11 09:05 RE: JDBC Drop/Create problem? Peter Mount <petermount@maidstone.gov.uk>
2000-12-11 17:29 ` Joachim Achtzehnter <joachim@kraut.ca>
2000-12-12 09:13 RE: JDBC Drop/Create problem? Peter Mount <petermount@maidstone.gov.uk>
2000-12-12 17:38 ` Joachim Achtzehnter <joachim@kraut.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox