google-cloud-spanner: can't easily delete all leading and trailing rows in a table

[archiecobbs]

The Mutation API does not allow deleting a range of keys where the minimum or maximum key is "infinity".

In other words, there's no way to do the equivalent of DELETE FROM Table WHERE key > @value.

This is because the KeyRange class does not have values for positive and negative infinity.

Instead, you have to iterate over the rows and delete them one by one, unless the key's type happens to have known absolute maximum and minimum values (some do not, e.g., BYTES and String do not have maximum values).

Is this omission intentional?

If not, request adding the ability to specify "infinity" for the start and/or end of a KeyRange.

[archiecobbs]

Quoting issue #1632:

Yes thats a valid point. In theory we should be able to sort order to the API. But given that similar effect can be achieved using query api, we do not have any plans to do so.

The same logic may apply here; if so, feel free to close this bug.

FWIW, here's the background motivating this request and #1632: for my application, I only need a sorted key/value store (keys and values are binary strings ordered unsigned lexicographically). So there's only one table with two columns, key (primary key) and value. I could do everything needed through the read() API, except it's missing (a) descending result option, and (b) the ability to specify an open ended maximum value for a key range. Querying via SQL is an adequate, though somehow aesthetically less satisfying, workaround.

[vkedia]

Thanks for explaining your use case. For this specific issue I don't think the situation is exactly similar as the other one. Querying all the keys via SQL and then deleting them all individually might not be as efficient. What might be more efficient is to use SQL to find the maximum key and then to a delete till that key.

[archiecobbs]

Oh right, I forgot that SQL DELETE is not even supported. So, yeah.

[tomayles]

KeyRange does support -/+ inf, but not by specifying particular values. The trick is to omit the trailing or leading key part and use a closed start/end. We talk about this is the docs here: https://cloud.google.com/spanner/docs/reference/rpc/google.spanner.v1#google.spanner.v1.KeyRange / http://googlecloudplatform.github.io/google-cloud-java/0.9.3/apidocs/com/google/cloud/spanner/KeyRange.html. Is that what you're looking for?

[archiecobbs]

I see how what you're saying might work for certain cases involving composite primary keys, but not for a simple primary key. Sorry if I'm missing something.

To make things concrete, here's my DDL:

CREATE TABLE KV (
  key BYTES(MAX) NOT NULL,
  val BYTES(MAX) NOT NULL
) PRIMARY KEY(key)

Suppose I have the key new byte[] { (byte)0x12, (byte)0x34 } and want to delete all rows in the table that are greater than or equal to this key in a single operation.

Can you show me how to construct the appropriate KeySet parameter to Mutation.delete() ?

[tomayles]

I believe it's the following:

KeyRange.closedClosed(Key.of(new byte[] { (byte)0x12, (byte)0x34 }), Key.of())

(fwiw, you can test how ranges work non-destructively with the read API: http://googlecloudplatform.github.io/google-cloud-java/0.9.3/apidocs/com/google/cloud/spanner/ReadContext.html#read-java.lang.String-com.google.cloud.spanner.KeySet-java.lang.Iterable-com.google.cloud.spanner.Options.ReadOption...-)

[archiecobbs]

This doesn't work (I tried it to make sure).

The problem is that Key.of() is negative infinity, not positive infinity. So your KeyRange has an upper limit less than its lower limit, which results in nothing being deleted.

There's no way to create a Key that means positive infinity. This is the root of the problem.

BTW, the Javadoc for Key talks about Key.all() which I thought might provide a clue, but no such method exists.

Minor note - presumably you meant:

KeyRange.closedClosed(Key.of(ByteArray.copyFrom(new byte[] { (byte)0x12, (byte)0x34 })), Key.of())

[tomayles]

Interesting - we do more or less exactly this in https://github.com/GoogleCloudPlatform/google-cloud-java/blob/master/google-cloud-spanner/src/test/java/com/google/cloud/spanner/it/ITReadTest.java#L183

[archiecobbs]

Ha, interesting.

Reviewing my test I was using KeyRange.closedOpen() instead of KeyRange.closedClosed(). With the former, it fails, but with the latter it works.

OK so now I have a way to delete to infinity, thanks.

However, now I'm mystified by this difference.

I see that the unit test you refer to is checking for this behavior. I still don't understand it though.

Shouldn't "closed range to +infinity" and "open range to +infinity" mean the same thing?

So at minimum the semantics are confusing and undocumented.

In particular, the documentation needs to specify that Key.of() should be used to represent both negative and positive infinity, as this is not intuitive.

[tomayles]

It's fair that this is subtle and deserves more documentation. At the moment, we have this short paragraph in the class doc for KeyRange:

"Start and end keys can omit trailing key components. This affects the inclusion and exclusion of rows that exactly match the provided key components: if the key is closed, then rows that exactly match the provided components are included; if the key is open, then rows that exactly match are not included."

So the way to think about it is not as keys representing +/- infinity, but what partially specifying a key means.

This is great feedback though, we'll work on improving the documentation and seeing if there are ways of making this more intuitive in the API.

[archiecobbs]

Great, thanks. A better explanation would definitely help.