No exceptions on violating queries via JDBC
The problem arises when I issue some violating query (e.g. FK constraint) with JDBC statements. Here’s an exmample: Connection con = dataSource.getConnection(); con.setAutoCommit(false); PreparedStatement stmnt = con.prepareStatement("/*Query with wrong FK*/"); stmnt.executeUpdate(); stmnt.close(); // exception is generated internally, but swallowed con.commit(); //no effect con.close(); As can be seen from the log messages, exception is not propagated outside of the driver library. Moreover, next calls to methods like commit have no effect at all. So, you cannot know if the statement executed successfully. Other drivers (like MySQL) throw exception on Statement.executeStatement() call even if autocommit is disabled. Due to this fact tools like Hibernate cannot work correctly. Can anyone comment on this?
+2Hi Max, It sounds like you're not getting an error on this query. Could you confirm that the query actually failed? I would expect the above example to succeed. Please see Vertica's documentation regarding our PK/FK-enforcement model: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#1483.htm If you're having further difficulties, could you post the log messages that you're seeing? Thanks, Adam
Adam, I just checked and seems that you are right, if I have just a simple FK-PK relationship on two tables, there is no error and the data appears in the DB even with non-existent FK value. But if there is an additional projection on these two tables a totally different behaviour kicks in: no error, no data in the DB. To show a reproducible scenario I have attached a screenshot. You can see there a DB schema, program code snippet and an output with exception logging enabled in debugger. ServerException contains the following message: [Vertica][VJDBC](4165) ERROR: Nonexistent foreign key value detected in FK-PK join [(bsp.foo x bsp.bar) using subquery and subquery (PATH ID: 1)]; value [100500] 
If I commit the transaction before closing the statement, I'll get an SQLException, which is an expected behaviour. 
If I commit the transaction before closing the statement, I'll get an SQLException, which is an expected behaviour. +2Hi Max, Hm... Does the behavior change at all if you explicitly close() the prepared statement when you're done with it, rather than relying on commit() to do so for you? The projection that you've created here is a "prejoin projection" -- it stores the two tables, well, pre-joined together. As described in further detail at the bottom of the link above, prejoin projections have a different behavior here; they will detect PK/FK constraint violation up front and error out. I'm less familiar with Vertica's JDBC driver (I use ODBC a little more myself), so perhaps others can comment. But I know the driver is quite aggressive about leaving INSERT statements open for re-use, for performance reasons. As has been discussed elsewhere, the JDBC driver internally rewrites INSERT statements as COPY statements; and the above stack trace discusses COMMIT closing a COPY, which tells me that the INSERT hasn't been properly closed yet. I should note, incidentally, that SQL does allow errors to be deferred to COMMIT. Other databases do this regularly, too; the most common example is deadlock resolution between queries. So frameworks (and code) should be able to handle this case in some fashion. Just because we can do it doesn't mean it's a nice thing to do, though :-)
I expect the driver to throw an exception when a database raises an error. This is what happens in my second (reference) example when I commit the transaction. Contrary, if I call Statement.close() before the Connection.commit(), the database raises an error after close(), driver internally generates ServerException on that event (which is visible in log messages on first screenshot), BUT the exception does NOT get propagated outside of the driver library, so the caller cannot know about the error. This is the problem, the driver should propagate the exception.
+2Interesting, that's what I'm trying to get at -- Could you please post code that reproduces this case, and the log message that you see and what data ends up in the database (ie., whether the transaction was actually successful or not)? You mentioned this case previously. But then the reproducer that you posted didn't reproduce this behavior. This isn't something that I'd immediately expect; I'm trying to understand it.
+2Ah, I see. Apologies; I misunderstood your previous post. Let me take a closer look at what's going on here. I'll get back to you.
It seems like a bug in the driver to me. Do you have an issue tracker?
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
