I seem to be hitting some bug in my app that revolves around incorrect application of PostgreSQL transactions. Namely, a before insert trigger does not see rows inserted in a different table in the same transaction prior to firing the trigger.
Are there any debugging techniques or tools that could help me? I already log everything, and right before the error it seems that everything just must work. But now, the new rows are invisible.
Nah, that's just me being dumb, situation normal.
that's often how it works
you ask for someone to look at something and then you discover your own bug
The yellow duck phenomenon, yeah. Frustrating how just thinking about something can lead you into all sorts of blind spots while coming up with the right words to explain the problem leads you back into the light.
Is it allowed for JDBC ResultSet method getMetaData() return null? I have a JDBC driver (Snowflake) that does it. next-jdbc seems to assume that getMetaData() never returns a null
I tested jdbc-next develop branch. Now (jdbc/execute-one! db [sql ..] {:return-keys true})
returns nil, when it previously threw an exception
And I’ll send a bug report to Snowflake about this
Specifically net.snowflake.client.jdbc.SnowflakeResultSetV1$EmptyResultSet
is implemented like this:
@Override
public ResultSetMetaData getMetaData() throws SQLException {
raiseSQLExceptionIfResultSetIsClosed();
return null;
}
next.jdbc.result-set
function get-column-names
breaks on null rsmeta
Does it only return null
if the result set is empty?
(it's certainly not a behavior I've ever seen with any other JDBC driver)
I have not yet tested other cases
I was doing a normal insert
(jdbc/execute-one! db [sql some parameters]
{:return-keys ["ID"]})
with autoincrement id field
I would have expected that to not return an actual ResultSet
at all, and for next.jdbc
to then call .getGeneratedKeys
which would probably a normal, valid ResultSet
for the returned keys.
If i remove the return-keys, I get #:next.jdbc{:update-count 1}
But I have to run now. I’ll look more in to this tomorrow.
Maybe Snowflake doesn't support .getGeneratedKeys
properly?
that is possible
Sounds like it's definitely doing something very unexpected...
I updated develop to at least not blow up on the column name function, but I don't know what it will do in your case now...
(although that breaks the test suite so I need to double check that)
OK, updated to pass the test suite and treat null
metadata as "empty" -- let me know how that behaves for you tomorrow (and maybe just try :return-keys true
and see what you get?).
@Override
public ResultSet getGeneratedKeys() throws SQLException {
logger.debug("getGeneratedKeys()");
raiseSQLExceptionIfStatementIsClosed();
return new SnowflakeResultSetV1.EmptyResultSet();
}
in SnowflakeStatementV1
so getGeneratedKeys() returns always EmptyResultSet which has null getMetaData()
Ouch!
If you're using deps.edn
, you can try {:git/url "<https://github.com/seancorfield/next-jdbc>" :sha "82a62424198b8748d4342c65bcd6881598cc6bc1"}
and it should at least not blow up.
You will get no columns back tho' (and no rows). So :return-keys
just won't work with that driver I suspect.
There's probably some other weird, Snowflake-specific ways to get auto-incremented IDs back...?
I actually I don’t even need the auto-increment field. It was just the first thing I tried.
What would be a reasonable/common way for jdbc driver to not to implement getGeneratedKeys()?
It should throw SQLFeatureNotSupportedException
> The Snowflake JDBC driver is a JDBC type 4 driver that supports the core JDBC functionality in version 1.0 of the JDBC API. For the complete API reference, see the http://www.oracle.com/technetwork/java/javase/jdbc/index.html. You are welcome to try methods from later versions of the API, but Snowflake does not guarantee that these methods are supported.
and getGeneratedKeys() is 1.4 version stuff
Sounds about right. So they haven't paid attention to detail when implementation some stuff.
> Snowflake does not guarantee that these methods are supported
...and they may return unexpected values, rather than throwing SQLFeatureNotSupportedException
:rolling_on_the_floor_laughing: