Re: Support logical replication of DDLs

On Mon, Mar 27, 2023 at 5:37 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Mar 27, 2023 at 12:07 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Mon, Mar 27, 2023 at 2:52 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> >
> > > I suggest taking a couple of steps back from the minutiae of the
> > > patch, and spending some hard effort thinking about how the thing
> > > would be controlled in a useful fashion (that is, a real design for
> > > the filtering that was mentioned at the very outset), and about the
> > > security issues, and about how we could get to a committable patch.
> > >
> >
> > Agreed. I'll try to summarize the discussion we have till now on this
> > and share my thoughts on the same in a separate email.
> >
>
> The idea to control what could be replicated is to introduce a new
> publication option 'ddl' along with current options 'publish' and
> 'publish_via_partition_root'. The values of this new option could be
> 'table', 'function', 'all', etc. Here 'all' enables the replication of
> all supported DDL commands. Example usage for this would be:
> Example:
> Create a new publication with all ddl replication enabled:
> CREATE PUBLICATION pub1 FOR ALL TABLES with (ddl = 'all');
>
> Enable table ddl replication for an existing Publication:
> ALTER PUBLICATION pub2 SET (ddl = 'table');
>
> This is what seems to have been discussed but I think we can even
> extend it to support based on operations/commands, say one would like
> to publish only 'create' and 'drop' of tables. Then we can extend the
> existing publish option to have values like 'create', 'alter', and
> 'drop'.
>

The other idea could be to that for the new option ddl, we input
command tags such that the replication will happen for those commands.
For example, ALTER PUBLICATION pub2 SET (ddl = 'Create Table, Alter
Table, ..'); This will obviate the need to have additional values like
'create', 'alter', and 'drop' for publish option.

The other thought related to filtering is that one might want to
filter DDLs and or DMLs performed by specific roles in the future. So,
we then need to introduce another option ddl_role, or something like
that.

Can we think of some other kind of filter for DDL replication?

Thoughts?

--
With Regards,
Amit Kapila.