Clojurians Log v2

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

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: