Query goal: | Find publications that do not contain any table. |
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: | Drop the publication. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
SELECT pubname AS publication_name, puballtables AS is_all_tables, pubinsert AS is_insert, pubupdate AS is_update, pubdelete AS is_delete, pubtruncate AS is_truncate FROM pg_publication AS p WHERE puballtables=FALSE AND NOT EXISTS (SELECT * FROM pg_publication_rel AS pr WHERE p.oid=pr.prpubid) ORDER BY pubname; |
SQL query | Description |
---|---|
SELECT format('DROP PUBLICATION %1$I;', pubname) AS statements FROM pg_publication AS p WHERE puballtables=FALSE AND NOT EXISTS (SELECT * FROM pg_publication_rel AS pr WHERE p.oid=pr.prpubid) ORDER BY pubname; | Drop the publication. |
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 |
---|---|
Distributed database | Queries of this category provide information about the foreign table mechanism. |