No exceptions on violating queries via JDBC

Max_1

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?

[Deleted User]

Hi 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

Max_1

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.

[Deleted User]

Hi 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 :-)

Max_1

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.

[Deleted User]

Interesting, 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.

Max_1

I've shown reproducible scenario with code and logs on screenshots in my 1st reply, are they visible? To repeat myself, here's DDL of the schema: CREATE TABLE bar ( pk INT PRIMARY KEY ); CREATE TABLE foo ( pk int primary key, fk INT NOT NULL, CONSTRAINT fkc FOREIGN KEY (fk) REFERENCES bar(pk) ); CREATE PROJECTION foobar AS SELECT foo.*, bar.* FROM foo, bar WHERE foo.fk = bar.pk; and the code: DataSource dataSource = /* configure datasource */ Connection con = dataSource.getConnection(); con.setAutoCommit(false); PreparedStatement stmnt = con.prepareStatement("INSERT INTO foo VALUES (?, ?)"); stmnt.setInt(1, 1); stmnt.setInt(2, 100500); stmnt.executeUpdate(); stmnt.close(); // exception ought to be thrown here, but was suppressed inside the driver con.commit(); con.close(); After the code is run, the table remains empty, nothing has been inserted. The caller code has not been notified about any errors neither.

[Deleted User]

Ah, 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.

Max_1

It seems like a bug in the driver to me. Do you have an issue tracker?