sql: system.localities uses a single level of locality

[couchand]

The initial version of the system.locality table (#19652) stores lat/long information according to a single key/value pair of localities, on the assumption that these are globally unique. If two values on the same key have the same value, but are nested under different parent localities, they will be treated as distinct for most purposes, but they can't both be assigned lat/long data. For instance, if one node in a cluster is started with --locality=region=west,datacenter=1 and another with --locality=region=east,datacenter=1, the system table doesn't support assigning lat/long at the datacenter level.

There seem to be two potential solutions to this issue: validate the locality flags assigned to nodes in the cluster and fail if there's a collision, or expand the system table to track all ancestor key/value pairs, not just a single level.

In the meantime, we should probably document the limitation.

[couchand]

The way I see it, it's better to allow this and make sure we support it fully than to validate against it and error out. From a usability perspective we should only add limits like this if they're needed.

[bdarnell]

Does the allocator make the same assumption that these names are globally unique, or does it look at the entire hierarchy?

[a-robinson]

The allocator makes no assumptions about uniqueness.

[vilterp]

maybe we can put this in the docs cc @Amruta-Ranade

An example of what you can't do

1. Set up locality flags like this:

region=us-east-1
  az=a
region=eu-west-1
  az=a

2. Try to add locations to both az=a's:
   Primary key uniqueness violation on insert to system.locations, since the primary key is (localityKey, localityValue).

What you would do instead:

1. Set up your locality flags the same way:

region=us-east-1
  az=a
region=eu-west-1
  az=a

2. Add locations at the region level, since az's within a region are effectively in the same place anyway.

[couchand]

I would edit the "what you would do instead" to suggest renaming the az values to be unique, rather than moving the locations up a level, so the solution handles the case where there are actually several child localities.

[vilterp]

True, could also do

region=us-east-1
  az=us-east-1a
region=eu-west-1
  az=eu-west-1a

[vilterp]

@Amruta-Ranade if you've sufficiently documented this limitation, maybe we should close this out or move it to 2.1, since we don't have a plan to address this before 2.0.

[Amruta-Ranade]

Moving it to 2.1 (documented as discussed for 2.0)

[jseldess]

Added to 2.0 known limitations in cockroachdb/docs#2823.

[sploiselle]

@vilterp @couchand Is this still a known limitation?

[couchand]

@sploiselle yep, no change.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!