MIME-Version: 1.0
References: 
 <CAD21AoCTNGiddikkUcDKj5QLnsg-51bpr-o6L-GTHWZL4ZFYtQ@mail.gmail.com>
 <CALj2ACXLkU4y+mmk2gHzmvSV4KTishSe=HhZ_yn=NHFBDGrwFQ@mail.gmail.com>
 <CAA4eK1JC9+R4S7L7y3DazOOMpnANVyCpsx84RSYjX6vfxLdK6w@mail.gmail.com>
 <CAD21AoATy+CzYmNYp_fKi4zsGLEGOmZen3OR+hxsCqn2GsQBoQ@mail.gmail.com>
In-Reply-To: 
 <CAD21AoATy+CzYmNYp_fKi4zsGLEGOmZen3OR+hxsCqn2GsQBoQ@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Tue, 23 Jun 2026 17:21:51 -0700
Message-ID: 
 <CAD21AoAGf1iAyMzWRGdc19vRLQka=-Kr0VeNp3R1dmRG6NC+ag@mail.gmail.com>
Subject: Re: Add a hook for handling logical decoding messages on subscribers.
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoAGf1iAyMzWRGdc19vRLQka%3D-Kr0VeNp3R1dmRG6NC%2Bag%40mail.gmail.com>
Precedence: bulk

On Tue, Jun 23, 2026 at 11:01=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
>
> On Tue, Jun 23, 2026 at 1:52=E2=80=AFAM Amit Kapila <amit.kapila16@gmail.=
com> wrote:
> >
> > On Tue, Jun 23, 2026 at 4:09=E2=80=AFAM Bharath Rupireddy
> > <bharath.rupireddyforpostgres@gmail.com> wrote:
> > >
> > > On Fri, Jun 19, 2026 at 3:34=E2=80=AFPM Masahiko Sawada <sawada.mshk@=
gmail.com> wrote:
> > > >
> > > > Hi all,
> > > >
> > > > Commit ac4645c015 allows pgoutput to send logical decoding messages=
,
> > > > but it's limited to applications that use the pgoutput plugin -- th=
e
> > > > built-in logical replication doesn't use it. I'd like to propose
> > > > introducing a hook to the logical replication message handling so t=
hat
> > > > extensions can plug in their own handling routine. This feature can=
 be
> > > > used for extensions to implement DDL replication, function
> > > > replication, or trigger user-specific routines on the subscriber si=
de.
> > >
> > > Thanks for working on this!
> > >
> > > > I've attached the PoC patch; it adds a hook function, and adds a ne=
w
> > > > 'message' subscription option that allows the user to request the
> > > > publisher to send logical decoding messages. Therefore, users need =
to
> > > > enable the 'message' option and set up the hook function at server
> > > > startup in order to receive the messages and trigger the hook
> > > > function.
> > >
> > > I understand the intent of the proposal, but I'd like to get the
> > > bigger picture first.
> > >
> > > Do we have any external modules that actually implement DDL
> > > replication (or any of the listed use-cases) with a similar hook? Or
> > > any existing discussion? I could be missing something because I
> > > haven't looked at all the DDL replication related threads.
> > >
> > > Another thing I'm curious about - why a hook? Is the plan to implemen=
t
> > > DDL replication as an external module rather than in core? If DDL
> > > replication eventually gets into core, I'd expect it to be apply-side
> > > logic executing the decoded DDL messages directly, not something goin=
g
> > > through a hook.
> > >
> >
> > I think it is important to have some example extension implementation
> > to see how the hook could be utilized. One more use of such a hook
> > could be to use for audit of DDLs replayed on subscribers.
>
> Right. I'm implementing a basic DDL replication solution using this
> hook as a sample implementation.

FYI, I've implemented a test extension, pg_logical_ddl[1], which can
be used with the proposed patch.

The extension uses both hooks ProcessUtility_hook to capture DDL
events and LogicalRepMessageHandle_hook to apply the DDL message.  It
sends a JSON blob containing the DDL command, command tag, user name,
and search_path. The implementation is  straightforward and I find
that the proposed hook is useful to implement DDL replication.

One thing that would simplify the implementation, and that I'd like to
have, is an easy way to capture the OID of the affected relation (or
its object address) without event triggers. That information is useful
for figuring out which object a DDL affects: for instance, to filter
DDLs by table, or to map the command to the right object on the
subscriber.

If we use an event trigger to capture DDL events, it would be easy to
get  the object address, but it's not easy for extensions like
pg_logical_ddl that don't rely on event triggers. The OID of the
affected table can be resolved by the name and search_path but it's
not a reliable solution as its schema can be renamed without taking a
heavy lock on the table. If they have to use event triggers, it could
be cumbersome to manage additional objects (e.g., users can drop them
etc.).  It would be good to have a mechanism to easily capture the
information that is collected by event triggers today without event
triggers. FYI I proposed a similar idea before in the DDL replication
thread[2] since the same is true for the in-core DDL replication and I
have a PoC patch for that.

Also, since this is an extension, it cannot necessarily work well with
PUBLICATION; it doesn't respect the table filtering setting and some
options like publish_via_partition_root. In that sense, working with
the existing publication feature is the advantage of  the in-core DDL
replication.

Regards,

[1] https://github.com/MasahikoSawada/pg_logical_ddl
[2] https://www.postgresql.org/message-id/CAD21AoCEVO%2BzpLmQqKwZbJ5%2Brvqs=
J1e0wnTNBH437p8tDw7B%3Dg%40mail.gmail.com

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com