October 4, 2017


I was struggling mightily this past evening trying to solve a problem with Cassandra. The first thing to know about this problem is that Cassandra only supports upserts. The second thing to know is that Cassandra has this pretty handy feature where it remembers the timestamp of each row. This allows you to process things out of order but still guarantee that they end up in your data store in order. So you can do this:

INSERT INTO event (key, value) VALUES ('foo', 'bar') USING TIMESTAMP 5;
INSERT INTO event (key, value) VALUES ('baz', 'bat') USING TIMESTAMP 4;

And when you're done your table will contain "foo" and "bar" because that came later than "baz" and "bat". It even works with deletes, like this:

DELETE FROM event USING TIMESTAMP 7 WHERE key = 'foo';
INSERT INTO event (key, value) VALUES ('fizz', 'buzz') USING TIMESTAMP 6;

And when you've run those two queries your table will still be empty even after the delete because the delete came after the insert, chronologically. It's very slick, particularly for my purposes.

Now on to the problem.

I have a system that receives events. The events might fit into a table like this:

CREATE TABLE event (
    key text primary key,
    message text,
    updated timestamp,
    created timestamp
);

The events come in with their own timestamps and I use those timestamps to ensure that the events are processed in the correct order regardless of in which order they arrive. Sometimes the events come in telling me to delete a key. Sometimes they come in telling me to upsert a key. I only care about the most recent message and I want to know when the most recent message arrived but I also want to know when the first message arrived, too. If an event is deleted then I stop caring about when the first one arrived until the next "first" one arrives.

Another way to describe this is like this:

  1. An event comes with the key "foo" so record the key, the message, and the created and updated timestamp using the provided timestamp.
  2. Another event comes with the key "foo" so update the message and the updated timestamp.
  3. A message telling me to delete the key "foo" comes in so delete the whole row.
  4. An event comes with the key "foo" so record the key, the message, and the created and updated timestamp using the provided timestamp.

In a normal database such as PostgreSQL version 9.5 or greater you might do this as a simple INSERT ... ON CONFLICT like this:

INSERT INTO event (key, message, created, updated)
VALUES ('foo', 'bar', '2017-10-04 00:00:00', '2017-10-04 00:00:00')
ON CONFLICT (key) DO UPDATE
SET message = 'bar', updated = '2017-10-04 00:00:00'
WHERE updated < '2017-10-04 00:00:00';

Then you can run that on the receipt of every message and you have guaranteed idempotent, ACID compliant event updates. (I think. I haven't tested that query but you know it looks correct. It doesn't matter anyway because you get the point.)

So for Cassandra you might think that I can do this with a query like this:

INSERT INTO event (key, created) VALUES ('foo', '2017-10-04 00:00:00') USING TIMESTAMP 1507075200000000 IF NOT EXISTS;
INSERT INTO event (key, message, updated) VALUES ('foo', 'bar', '2017-10-04 00:00:00') USING TIMESTAMP 1507075200000000;

Wrong. That fails because you can't have USING TIMESTAMP and IF NOT EXISTS at the same time.

So take off the USING TIMESTAMP portion. Except now the timestamp on the the created column is the current timestamp. This very quickly becomes a problem when you do this:

  1. At time 10 we process event insertion that happened at time 1. The created column is now at time 10 but the rest of the data is at time 1.
  2. At time 11 we process event deletion that happened at time 2. All data is deleted except the created column because 10 is later than 2.
  3. Process event insertion that happened at time 3. Because we still have data in the created column we still have a row and because we still have a row we won't insert a new value in the created column.
  4. Now our data is all wrong. Sad!

After hours of banging my head I figured out how to do it and it's still relatively fast. Here are the steps:

  1. At time 10 we receive an insertion that happened at time 1. We INSERT INTO event (key) VALUES ('foo') IF NOT EXISTS;. This returns true or false depending on whether or not the row was inserted. Primary key columns do not have timestamps so it doesn't matter that this happened at time 10.
  2. If the row was indeed inserted in the last step then we run INSERT INTO event (key, created) VALUES ('foo', '2017-10-04 00:00:00') USING TIMESTAMP 1507075200000000;. Now we have a valid value for the created column and it has the correct timestamp associated with it.
  3. Then we insert the message using the same process.
  4. At time 11 we receive a delete that happened at time 2. So we delete the whole row and because all fields are set to time 1 everything is deleted and the row disappears. Remember, primary key columns do not have timestamps so deleting the whole row deletes everything. (I agree that this last point about primary key columns is surprising and confusing but it helps me solve my problem so I'm not complaining.)

And problem solved.

Comments

No comments ... yet.

Post Comment

About

Location Seattle, WA
First post September 6, 2004
Total posts 5,943

Tags

  1. 1,794
  2. 693
  3. 631
  4. 585
  5. 552
  6. 494
  7. 268
  8. 192
  9. 191
  10. 139
  11. 131
  12. 105
  13. 104
  14. 86
  15. 78
  16. 70
  17. 58
  18. 56
  19. 41
  20. 26
Load the RSS feed for entries or for comments.