Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add nullability mark field to ForeignKey class and method for generating sql request for creating corresponding index #129

Closed
mfvanek opened this issue May 11, 2022 · 3 comments · Fixed by #131
Assignees
Labels
enhancement New feature or request

Comments

@mfvanek
Copy link
Owner

mfvanek commented May 11, 2022

If foreign key is not covered with corresponding index we have to create it manually.
This is boring and error prone operation due to several circumstances: possible table downtime when creating index and nullability of fields from which index consists.
We can generate sql request for index creation automatically.
For example:

ForeignKey{tableName='leads.lead_clob', constraintName='lead_clob_fkey_lead_info_id', columnsInConstraint=[lead_info_id]}

might lead to a few possible solutions:

  1. concurrently and field is nullable
create index concurrently if not exists lead_clob_lead_info_id_without_nulls_idx
    on leads.lead_clob (lead_info_id) where lead_info_id is not null;
  1. concurrently and field is not null
create index concurrently if not exists lead_clob_lead_info_id_idx
    on leads.lead_clob (lead_info_id);
  1. with table lock
create index if not exists lead_clob_lead_info_id_without_nulls_idx
    on leads.lead_clob (lead_info_id) where lead_info_id is not null;

and etc.

We need to implement a kind of Formatter with fluent API based on GOF builder pattern which will generate text for sql requests.
Options:

  1. concurrently or not (default true)
  2. exclude nulls or not (default true)
  3. add "without_null" part to the name of index or not (default true)
  4. add "idx" as prefix or suffix or not at all (default add as suffix)
  5. generated index name length cannot exceed 63. see PostgreSQL Limits
@mfvanek mfvanek added enhancement New feature or request help wanted Extra attention is needed good first issue Good for newcomers labels May 11, 2022
@mfvanek mfvanek self-assigned this May 14, 2022
@mfvanek mfvanek added work in progress Work on this issue has already begun and removed help wanted Extra attention is needed good first issue Good for newcomers labels May 14, 2022
mfvanek pushed a commit to mfvanek/pg-index-health-sql that referenced this issue May 14, 2022
@mfvanek
Copy link
Owner Author

mfvanek commented May 14, 2022

Sql query modified mfvanek/pg-index-health-sql@7565b16

@mfvanek
Copy link
Owner Author

mfvanek commented May 15, 2022

Nullability mark has been added in #130

@mfvanek
Copy link
Owner Author

mfvanek commented May 21, 2022

@mfvanek mfvanek removed the work in progress Work on this issue has already begun label May 23, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant