Re: Referential integrity error

James,

This is how postgres works. After *any* database error the current
transaction is aborted and all subsequent statements will error until
you rollback the transaction and start a new one. So you can't just
trap a SQLException and continue processing, you need to rollback first
(and possibly redo any work that you didn't want rolled back) to get you
to a point where you can continue.

thanks,
--Barry

James Kelty wrote:

>Yes, yes, I do.
>
>Here is a test case that a developer wrote. Basically the class ran through
>a process of, doing a select on the DB, and getting the results, which were
>valid. Then, he created a ref integrity violation, and then tried the select
>again, and got an exception. Here are his results.
>
>
>
>>Running this java class produces the following output:
>>The second query (which is the same as the first) produces an error
>>message after a bad insert statement is executed...
>>
>>
>>About to execute a valid query (we expect results)
>>After executing a valid query (no exception thrown)
>>Expected this error: ERROR: fk_customeraddress_n40026faddre referential
>>integrity violation -
>>key referenced from customeraddress not found in address
>>
>>Closing error-producing statement
>>About to execute another valid query
>>After second valid query (unexpected exception): No results were returned
>>
>>
>by
>
>
>>the query.
>>
>>
>
>
>Thanks!
>
>-James
>-----Original Message-----
>From: Dave Cramer [mailto:Dave(at)micro-automation(dot)net]
>Sent: Monday, August 05, 2002 4:07 PM
>To: James Kelty
>Cc: pgsql-jdbc(at)postgresql(dot)org
>Subject: Re: [JDBC] Referential integrity error
>
>
>Can you send a test case that demonstrates your problem?
>
>Dave
>On Mon, 2002-08-05 at 18:47, James Kelty wrote:
>
>
>>Hello,
>>
>>We have noticed that with PostgreSQL 7.1.3, and the downloadable jdbc
>>driver, that, when a connection gets a referential integrity exception,
>>
>>
>that
>
>
>>the query results returned from that connection are not longer correct.
>>
>>
>This
>
>
>>is reproduceable by our QA team. Has anyone had this issue, and is there a
>>fix?
>>
>>
>>
>>-James
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>message can get through to the mailing list cleanly
>>
>>
>>
>>
>
>
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>