sql

All things SQL and JDBC...
p-himik 2020-08-20T13:54:18.000500Z

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.

p-himik 2020-08-20T14:13:44.001300Z

Nah, that's just me being dumb, situation normal.

😂 1
kulminaator 2020-08-20T15:12:51.001700Z

that's often how it works

kulminaator 2020-08-20T15:13:09.001900Z

you ask for someone to look at something and then you discover your own bug

p-himik 2020-08-20T15:18:15.002100Z

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.

1
jsyrjala 2020-08-20T18:36:39.004Z

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

jsyrjala 2020-08-21T09:59:28.011Z

I tested jdbc-next develop branch. Now (jdbc/execute-one! db [sql ..] {:return-keys true}) returns nil, when it previously threw an exception

jsyrjala 2020-08-21T10:00:04.011200Z

And I’ll send a bug report to Snowflake about this

1
jsyrjala 2020-08-20T18:38:47.004200Z

Specifically net.snowflake.client.jdbc.SnowflakeResultSetV1$EmptyResultSet is implemented like this:

@Override
    public ResultSetMetaData getMetaData() throws SQLException {
      raiseSQLExceptionIfResultSetIsClosed();
      return null;
    }

jsyrjala 2020-08-20T18:40:35.004400Z

next.jdbc.result-set function get-column-names breaks on null rsmeta

seancorfield 2020-08-20T18:52:33.004600Z

Does it only return null if the result set is empty?

seancorfield 2020-08-20T18:52:52.004800Z

(it's certainly not a behavior I've ever seen with any other JDBC driver)

jsyrjala 2020-08-20T18:56:43.005Z

I have not yet tested other cases

jsyrjala 2020-08-20T18:57:53.005200Z

I was doing a normal insert

(jdbc/execute-one! db [sql some parameters]
                   {:return-keys ["ID"]})

jsyrjala 2020-08-20T18:59:09.005400Z

with autoincrement id field

seancorfield 2020-08-20T19:01:39.005600Z

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.

jsyrjala 2020-08-20T19:01:53.005800Z

If i remove the return-keys, I get #:next.jdbc{:update-count 1}

jsyrjala 2020-08-20T19:02:23.006Z

But I have to run now. I’ll look more in to this tomorrow.

seancorfield 2020-08-20T19:02:26.006200Z

Maybe Snowflake doesn't support .getGeneratedKeys properly?

jsyrjala 2020-08-20T19:02:34.006400Z

that is possible

seancorfield 2020-08-20T19:02:45.006600Z

Sounds like it's definitely doing something very unexpected...

seancorfield 2020-08-20T19:03:14.006800Z

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

seancorfield 2020-08-20T19:05:44.007Z

(although that breaks the test suite so I need to double check that)

seancorfield 2020-08-20T19:11:46.007200Z

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?).

jsyrjala 2020-08-20T20:19:22.007400Z

@Override
public ResultSet getGeneratedKeys() throws SQLException {
  logger.debug("getGeneratedKeys()");
  raiseSQLExceptionIfStatementIsClosed();
  return new SnowflakeResultSetV1.EmptyResultSet();
}

jsyrjala 2020-08-20T20:20:49.007600Z

in SnowflakeStatementV1

jsyrjala 2020-08-20T20:22:17.007800Z

so getGeneratedKeys() returns always EmptyResultSet which has null getMetaData()

seancorfield 2020-08-20T20:23:12.008Z

Ouch!

seancorfield 2020-08-20T20:25:11.008200Z

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.

seancorfield 2020-08-20T20:25:50.008400Z

You will get no columns back tho' (and no rows). So :return-keys just won't work with that driver I suspect.

seancorfield 2020-08-20T20:26:47.008600Z

There's probably some other weird, Snowflake-specific ways to get auto-incremented IDs back...?

jsyrjala 2020-08-20T20:28:43.008800Z

I actually I don’t even need the auto-increment field. It was just the first thing I tried.

jsyrjala 2020-08-20T20:29:23.009Z

What would be a reasonable/common way for jdbc driver to not to implement getGeneratedKeys()?

jsyrjala 2020-08-20T20:32:57.009200Z

It should throw SQLFeatureNotSupportedException

jsyrjala 2020-08-20T20:33:41.009400Z

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

jsyrjala 2020-08-20T20:33:51.009700Z

and getGeneratedKeys() is 1.4 version stuff

seancorfield 2020-08-20T20:39:29.009900Z

Sounds about right. So they haven't paid attention to detail when implementation some stuff.

seancorfield 2020-08-20T20:41:45.010100Z

> Snowflake does not guarantee that these methods are supported ...and they may return unexpected values, rather than throwing SQLFeatureNotSupportedException :rolling_on_the_floor_laughing: