Re: Something is rotten in publication drop

On 6/15/17 12:23, Tom Lane wrote:
> It strikes me that you could rewrite psql's query to just do its own
> catalog search and not bother with the function at all. It would have
> to know a bit more about the catalog structure than it does now, but not
> that much:
>
> select pub.pubname from pg_catalog.pg_publication pub
> where puballtables or
> exists(select 1 from pg_catalog.pg_publication_rel r
> where r.prpubid = pub.oid and r.prrelid = '%s');

We used to do something like that, but then people complained that that
was not absolutely accurate, because it did not exclude catalog tables
and related things properly. See commit
2d460179baa8744e9e2a183a5121306596c53fba. To do this properly, you need
to filter pg_class using is_publishable_class() (hitherto internal C
function).

The way this was originally written was for use by subscriptioncmds.c
fetch_table_list(), which generally only deals with a small number of
publications as the search key and wants to find all the relations. The
psql use case is exactly the opposite: We start with a relation and want
to find all the publications. The third use case is that we document
the view pg_publication_tables for general use, so depending on which
search key you start with, you might get terrible performance if you
have a lot of tables.

An academically nice way to write a general query for this would be:

CREATE VIEW pg_publication_tables AS
SELECT
p.pubname AS pubname,
n.nspname AS schemaname,
c.relname AS tablename,
c.oid AS relid
FROM pg_publication p
JOIN pg_publication_rel pr ON p.oid = pr.prpubid
JOIN pg_class c ON pr.prrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
UNION
SELECT
p.pubname AS pubname,
n.nspname AS schemaname,
c.relname AS tablename,
c.oid AS relid
FROM pg_publication p
JOIN pg_class c ON p.puballtables AND
pg_is_relation_publishable(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid;

But looking at the plans this generates, it will do a sequential scan of
pg_class even if you look for a publication that is not puballtables,
which would suck for the subscriptioncmds.c use case.

We could use the above definition for the documented view and the psql
use case.

We could then create second view that uses the existing definition

CREATE VIEW pg_publication_tables AS
SELECT
P.pubname AS pubname,
N.nspname AS schemaname,
C.relname AS tablename
FROM pg_publication P, pg_class C
JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname));

for use in subscriptioncmds.c. Or don't use a view for that. But the
view is useful because we should preserve this interface across versions.

Or we throw away all the views and use custom code everywhere.

Patch for experimentation attached.

Any ideas?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services