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 (opens in new tab)
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.

Fix Action #1
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.

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:

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 .

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:

Distributed database

Queries of this category provide information about the foreign table mechanism.

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