search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
29.89k stars 3.77k forks source link

sql: store column IDs in index predicates instead of column names #49766

Open mgartner opened 4 years ago

mgartner commented 4 years ago

Storing column names in partial index predicates (like CREATE INDEX ... WHERE s = 'foo') has disadvantages, such as maintaining code to handle column renames.

Storing column IDs instead (like `CREATE INDEX ... WHERE @11 = 'foo') would eliminate this issue.

Note that we'd need to convert these column IDs to column names whenever the predicate is shown to users.

More context: https://github.com/cockroachdb/cockroach/pull/49672#issuecomment-635677045

Jira issue: CRDB-4199

jordanlewis commented 4 years ago

Some things that I've been talking over with @RichardJCai in this regard:

  1. Not relevant here, but for views, we can't just store a column ordinal - we have to also store a table id. So whatever we do, we should make sure to make the syntax for this usable in both situations or risk having extra serialization formats.
  2. Truncating a table bumps its table id. Maybe also not relevant here.
  3. Column IDs can be modified by alter column type, which replaces a column with another. It's important to use the logical column ID, which should be stable in the face of this kind of operation.

There may be other gotchas. Anything else you noticed, Richard?

rohany commented 4 years ago

@mgartner, also index predicates will need to be using this sort of stable representation when there are user defined types in the predicate -- see #49565. I'll probably be doing the change, but just to keep it on your radar.

RichardJCai commented 4 years ago

Some things that I've been talking over with @RichardJCai in this regard:

  1. Not relevant here, but for views, we can't just store a column ordinal - we have to also store a table id. So whatever we do, we should make sure to make the syntax for this usable in both situations or risk having extra serialization formats.
  2. Truncating a table bumps its table id. Maybe also not relevant here.
  3. Column IDs can be modified by alter column type, which replaces a column with another. It's important to use the logical column ID, which should be stable in the face of this kind of operation.

There may be other gotchas. Anything else you noticed, Richard?

No I think you've covered everything. I don't think table id will be too difficult to update right?

github-actions[bot] commented 1 year ago

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 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!