Re: Orphaned records in pg_replication_origin_status after subscription drop

On Fri, Dec 19, 2025 at 01:56:38PM -0800, Masahiko Sawada wrote:
> It would work too. Or I think we can do a similar thing in
> replorigin_reset() for tablesync workers who are in
> SUBREL_STATE_DATASYNC state. Both ways require exposing
> replorigin_state_clear(), though.

Exposing replorigin_state_clear() feels a bit weird here for something
that's only reserved for origin.c, and it would be OK, still..

> I am slightly leaning towards the idea of using a short transaction
> because the tablesync worker would do things closer to the apply
> workers and it would also fix the odd behavior that
> pg_replication_origin_status shows NULL in the external_id column for
> the origins while the COPY is being executed. But we need to verify if
> it's really okay to reuse the existing origin instead of raising an
> error in case where the tablesync worker retries to the data copy.

I also lean towards more consistency between the worker and tablesync
code, not less.  Hence I'd prefer a short transaction at the beginning
of the tablesync startup so as we can just rely on the DROP
SUBSCRIPTION code path to ensure that the origin is removed from the
catalogs and cleaned up in memory.

The case of pg_replication_origin_status showing NULL while COPY is
executing is an interesting point, didn't think about it yesterday,
but yes that seems like a good thing to have anyway.
--
Michael