Apply worker fails if a relation is missing on subscriber even if refresh publication has not been refreshed yet

From: Melih Mutlu <m(dot)melihmutlu(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Apply worker fails if a relation is missing on subscriber even if refresh publication has not been refreshed yet
Date: 2022-12-22 13:46:02
Message-ID: CAGPVpCQtF8LTM8svXdqMcMw8EmX4cNH=tGjwBW6ukJo2PEQcng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I realized a behaviour of logical replication that seems unexpected to me,
but not totally sure.

Let's say a new table is created and added into a publication and not
created on subscriber yet. Also "ALTER SUBSCRIPTION ... REFRESH
PUBLICATION" has not been called yet.
What I expect in that case would be that logical replication continues to
work as it was working before the new table was created. The new table does
not get replicated until "REFRESH PUBLICATION" as stated here [1].
This is indeed how it actually seems to work. Until we insert a row into
the new table.

After a new row into the new table, the apply worker gets this change and
tries to apply it. As expected, it fails since the table does not exist on
the subscriber yet. And the worker keeps crashing without and can't apply
any changes for any table.
The obvious way to resolve this is creating the table on subscriber as
well. After that apply worker will be back to work and skip changes for the
new table and move to other changes.
Since REFRESH PUBLICATION is not called yet, any change for the new table
will not be replicated.

If replication of the new table will not start anyway (until REFRESH
PUBLICATION), do we really need to have that table on the subscriber for
apply worker to work?
AFAIU any change on publication would not affect logical replication setup
until the publication gets refreshed on subscriber. If this understanding
is correct, then apply worker should be able to run without needing new
tables.
What do you think?

Also; if you agree, then the attached patch attempts to fix this issue.
It relies on the info from pg_subscription_rel so that apply worker only
applies changes for the relations exist in pg_subscription_rel.
Since new tables wouldn't be in there until the next REFRESH PUBLICATION,
missing those tables won't be a problem for existing subscriptions.

[1] https://www.postgresql.org/docs/current/sql-altersubscription.html

Thanks,
--
Melih Mutlu
Microsoft

Attachment Content-Type Size
0001-Do-not-apply-for-tables-which-not-exist-on-catalog.patch application/octet-stream 6.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dag Lem 2022-12-22 14:02:54 Re: daitch_mokotoff module
Previous Message Dag Lem 2022-12-22 13:27:54 Re: daitch_mokotoff module