Thread: Referential integrity error
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
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@postgresql.org so that your > message can get through to the mailing list cleanly > >
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@micro-automation.net] Sent: Monday, August 05, 2002 4:07 PM To: James Kelty Cc: pgsql-jdbc@postgresql.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@postgresql.org so that your > message can get through to the mailing list cleanly > >
Attachment
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@micro-automation.net] >Sent: Monday, August 05, 2002 4:07 PM >To: James Kelty >Cc: pgsql-jdbc@postgresql.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@postgresql.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@postgresql.org) > >