Cannot delete row just created

CrateDB
1

I’m trying to simulate unique constraints with a table dedicated to it, and in doing so, need to also simulate transactions by “rolling back” an insert it just completed. However, when I send the DELETE, it doesn’t “see” the row inserted. I get no error. Just a count of 0 rows deleted.

To test further, I replaced it with a general delete what deletes all that are not in another table.

DELETE FROM uniquekeys where value not in (select logonId from users);

This deletes all but the one that was just inserted, verifying it’s simply not “seeing” the new row added until later when this general delete is called next time.

The environment is a single node docker of CrateDB 4.5.0. The app is a Quarkus Java app using the postgresql driver (crate’s driver is not available in Quarkus/GraalVM). Each invocation is via a REST request, with connections provided via an injected (CDI) data source.

I tried to pass the same DB connection to the operation inserting and deleting, and that did not resolve it. Because it is using connection pooling via a data source via a framework aware of the request, it’s possible it was using the same connection anyway when not explicitly passed.

I’m tempted to say this is a READ ISOLATION problem, but that’s typically a concept for transactions, and I vaguely remember something about a “delay” with Crate, though being single node, that shouldn’t be an issue.

These DELETEs work as expected via Crash.

UPDATE: I did diagnose this down to a delay issue. Can you please explain the reason as well as the best practice for dealing with it? I am currently working around it with a Future…

ExecutorService executor = Executors.newSingleThreadExecutor();
executor.submit(() -> {
  try {
    Thread.sleep(2000);
    keys.rollback(transactionId);
  } catch (Exception e1) {
      LOG.errorf("Cannot rollback %s for %s: %s", transactionId, "Unique Keys", e1.getMessage());
  }
});
2

So this is most likely due to the refresh interval. You could try adjusting the refresh_interval(Default is 1000ms) for that table or send a REFRESH TABLE <table name> before the DELETE-Statement.

—

I have seen some prolonged delays with recent versions … needs further investigation.

3

REFRESH TABLE resolves it without introducing a delay. Thank you!

1 Like