Re: Something is rotten in publication drop

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> On 6/9/17 11:45, Tom Lane wrote:
>> What we've done in many comparable situations is to allow a
>> catalog-probing function to return NULL instead of failing
>> when handed an OID or other identifier that it can't locate.
>> Here it seems like pg_get_publication_tables() needs to use
>> missing_ok = TRUE and then return zero rows for a null result.

> Why is it that dropping a publication in another session makes our local
> view of things change in middle of a single SQL statement? Is there
> something we can change to address that?

Not easily. The syscaches run on CatalogSnapshot time and may therefore
see changes not yet visible to the surrounding query's snapshot. It's
possible that you could reimplement pg_get_publication_tables() to do
its own catalog access with the outer query's snapshot, but it'd be
pretty tedious and duplicative.

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');

regards, tom lane