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 check "Tables are not linked to other tables" #448

Closed
mfvanek opened this issue Oct 5, 2024 · 0 comments · Fixed by mfvanek/pg-index-health-demo#261
Closed

Add check "Tables are not linked to other tables" #448

mfvanek opened this issue Oct 5, 2024 · 0 comments · Fixed by mfvanek/pg-index-health-demo#261
Assignees
Labels
enhancement New feature or request
Milestone

Comments

@mfvanek
Copy link
Owner

mfvanek commented Oct 5, 2024

See https://habr.com/ru/articles/803841/
Part "Отношения не участвующие в FK"
This check can find potentially outdated or unused tables

SELECT
	n.nspname,  -- схема
	t.relname   -- отношение
FROM pg_catalog.pg_class AS t
    INNER JOIN pg_catalog.pg_namespace AS n 
        ON t.relnamespace = n.oid
WHERE
    relkind IN ('r', 'p')
    AND t.oid NOT IN (SELECT conrelid FROM pg_catalog.pg_constraint WHERE contype IN ('f'))
    AND t.oid NOT IN (SELECT confrelid FROM pg_catalog.pg_constraint WHERE contype IN ('f'))
    AND n.nspname NOT IN ('information_schema', 'pg_catalog') 
    AND n.nspname NOT LIKE 'pg_toast%'
@mfvanek mfvanek added duplicate This issue or pull request already exists help wanted Extra attention is needed good first issue Good for newcomers enhancement New feature or request and removed duplicate This issue or pull request already exists labels Oct 5, 2024
@mfvanek mfvanek removed help wanted Extra attention is needed good first issue Good for newcomers labels Oct 20, 2024
@mfvanek mfvanek self-assigned this Oct 20, 2024
@mfvanek mfvanek added this to the 0.13.2 milestone Oct 20, 2024
@mfvanek mfvanek closed this as completed Oct 22, 2024
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