Publications with no tables

Back to Catalog
Goal Find publications that do not contain any table.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Drop the publication.
Data Source system catalog only
SQL Query 
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 statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
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.
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, 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 .
Categories

This query is classified under the following categories:

NameDescription
Distributed databaseQueries of this category provide information about the foreign table mechanism.