Query goal: | Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Create partitions or drop the table. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH partitioned AS (SELECT n.nspname AS table_schema, c.oid AS table_oid, c.relname AS table_name, CASE WHEN pt.partstrat='h' THEN 'hash partitioned table' WHEN pt.partstrat='l' THEN 'list partitioned table' WHEN pt.partstrat='r' THEN 'range partitioned table' END AS partition_strategy, CASE WHEN pt.partdefid=0 THEN FALSE ELSE TRUE END AS has_default_partition, partattrs, relispartition FROM pg_class AS c INNER JOIN pg_partitioned_table AS pt ON pt.partrelid=c.oid INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid WHERE n.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)), partitioned_unnest AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid, partattrs, target_col_num, ordin FROM partitioned, unnest(partitioned.partattrs) WITH ORDINALITY AS f(target_col_num, ordin)), partitioned_with_names AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid, string_agg(a_target.attname, ', ' ORDER BY a_target.attname) AS partition_key_columns FROM partitioned_unnest AS pu INNER JOIN pg_attribute a_target ON pu.target_col_num = a_target.attnum AND pu.table_oid = a_target.attrelid AND a_target.attisdropped = false GROUP BY table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid) SELECT table_schema, table_name, partition_strategy, has_default_partition, partition_key_columns, relispartition FROM partitioned_with_names pt WHERE NOT EXISTS (SELECT * FROM pg_depend AS d INNER JOIN pg_class AS partitions ON d.objid=partitions.oid INNER JOIN pg_namespace AS n ON partitions.relnamespace=n.oid WHERE d.deptype='a' AND partitions.relispartition=TRUE AND pt.table_oid=d.refobjid) ORDER BY table_schema, table_name; |
SQL query | Description |
---|---|
WITH partitioned AS (SELECT n.nspname AS table_schema, c.oid AS table_oid, c.relname AS table_name, CASE WHEN pt.partstrat='h' THEN 'hash partitioned table' WHEN pt.partstrat='l' THEN 'list partitioned table' WHEN pt.partstrat='r' THEN 'range partitioned table' END AS partition_strategy, CASE WHEN pt.partdefid=0 THEN FALSE ELSE TRUE END AS has_default_partition, partattrs FROM pg_class AS c INNER JOIN pg_partitioned_table AS pt ON pt.partrelid=c.oid INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid WHERE n.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)), partitioned_unnest AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, table_oid, partattrs, target_col_num, ordin FROM partitioned, unnest(partitioned.partattrs) WITH ORDINALITY AS f(target_col_num, ordin)), partitioned_with_names AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, table_oid, string_agg(a_target.attname, ', ' ORDER BY a_target.attname) AS partition_key_columns FROM partitioned_unnest AS pu INNER JOIN pg_attribute a_target ON pu.target_col_num = a_target.attnum AND pu.table_oid = a_target.attrelid AND a_target.attisdropped = false GROUP BY table_schema, table_name, partition_strategy, has_default_partition, table_oid) SELECT format('DROP TABLE %1$I.%2$I;', table_schema, table_name) AS statements FROM partitioned_with_names pt WHERE NOT EXISTS (SELECT * FROM pg_depend AS d INNER JOIN pg_class AS partitions ON d.objid=partitions.oid INNER JOIN pg_namespace AS n ON partitions.relnamespace=n.oid WHERE d.deptype='a' AND partitions.relispartition=TRUE AND pt.table_oid=d.refobjid) ORDER BY table_schema, table_name; | Drop the table. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Comfortability of data management | Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient. |
Performance | Queries of this category provide information about indexes in a database. |
Unused implementation elements | Queries of this catergory provide information about the database objects that are not used. |
Reference |
---|
https://www.postgresql.org/docs/current/ddl-partitioning.html |