Contrib.social

Prefixed index creation (functional index on substring) has no test coverage

Open on Drupal.org →
Created on 8 December 2023, 9 months ago
Updated 29 December 2023, 8 months ago

Problem/Motivation

While working on 📌 Adding GIN and GIST indexes to PostgreSQL databases RTBC I discovered rarely-used syntax for creating an index on a table that allows for indexing on a substring'ed prefix. Support by drivers is optional and it appears the only core DB driver which does so is Postgres. While this works, there is no test coverage.

Steps to reproduce

Not a bug, just no test coverage. The new default test coverage in DriverSpecificSchemaTestBase::assertPrefixedColumnIndex() passes on drivers which do not support this syntax (e.g., MySQL) and fails on pgsql. A Postgres-specific test method override is included in the MR.

Proposed resolution

Interestingly, at least in the case of Postgres this also reveals that pgsql module's implementation of Schema::introspectIndexSchema() won't capture these types of indexes. I messed around with variations on the existing SQL in that method and it seems that functional indexes are just a different beast. As that method's docblock says it will return a list of columns that are indexed, I think it's OK to keep things as they are in that method and not get too cute about trying to also figure out which columns contribute to a functional index.

Remaining tasks

User interface changes

API changes

None.

Data model changes

Release notes snippet

📌 Task
Status

Needs work

Version

11.0 🔥

Component
Database  →

Last updated 1 minute ago

  • Maintained by
  • 🇳🇱Netherlands @daffie
Created by

🇺🇸United States bradjones1 Digital Nomad Life

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

  • Issue created by @bradjones1
  • Merge request !5742Add test coverage for prefixed indexes → (Open) created by bradjones1
  • Status changed to Needs review 9 months ago
  • Comment 9 months ago →
    🇺🇸United States bradjones1 Digital Nomad Life

    The test-only CI run skips because it's configured for MySQL...but the only "actual" test class changed here is pgsql. Raised a question in Slack. Don't think that should necessarily hold this up, however.

  • Comment 9 months ago →
    🇺🇸United States bradjones1 Digital Nomad Life

    Test-only changes here aren't going to be helpful because we're adding test coverage, not demonstrating a bug... 🙃

    I think this should be ready for review. Some initial test failures but they look to be unrelated/related to new performance testing.

  • Status changed to RTBC 9 months ago
  • Comment 9 months ago →
    🇺🇸United States smustgrave

    Additional test coverage is always a plus.

    See you ran tests for all the different database types and no failures.

    The comments read fine to me.

  • Status changed to Needs work 8 months ago
  • Comment 8 months ago →
    🇳🇿New Zealand quietone New Zealand

    I'm triaging RTBC issues → .

    I agree with @smustgrave that additional test coverage is always welcome.

    I read the IS, the comments and the MR. I didn't see any unanswered questions. I then tried to apply the diff but it no longer applies.

    While reading the MR I was having trouble with the different terms applied to this type of index being tested. It seems that functional, alternative and prefix are being used for the same thing. While I could be wrong it does suggest that consisting in the wording and the method names would help. I also think adding a link to this 'rarely used syntax' would be beneficial.

    Setting back to NW for the above.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024