RE: Support logical replication of DDLs - Mailing list pgsql-hackers

On Tuesday, March 28, 2023 1:41 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Mon, Mar 27, 2023 at 5:37 PM Amit Kapila <amit.kapila16@gmail.com>
> wrote:
> >
> > On Mon, Mar 27, 2023 at 12:07 PM Amit Kapila <amit.kapila16@gmail.com>
> wrote:
> > >
> > > On Mon, Mar 27, 2023 at 2:52 AM Tom Lane <tgl@sss.pgh.pa.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?

I am thinking another generic syntax for ddl replication like:

--
CREATE PUBLICATION pubname FOR object_type object_name with (publish = 'ddl_type');
--

To replicate DDLs that happened on a table, we don't need to add new syntax or
option, we can extend the value for the 'publish' option like:

To support more non-table objects replication, we can follow the same style and write it like:
--
CRAETE PUBLICATION FOR FUNCTION f1 with (publish = 'alter'); -- function
CRAETE PUBLICATION FOR ALL OPERATORS IN SCHEMA op_schema with (publish = 'drop'); -- operators
CRAETE PUBLICATION FOR ALL OBJECTS with (publish = 'alter, create, drop'); -- everything
--

In this approach, we extend the publication grammar and users can
filter the object schema, object name, object type and ddltype. We can also add
more options to filter role or other infos in the future.

~~~~

One more alternative could be like:

One more alternative could be like:
CREATE PUBLICATION xx FOR pub_create_alter_table WITH (ddl = 'table:create,alter'); -- it will publish create table and
altertable operations.
 
CREATE PUBLICATION xx FOR pub_all_table WITH (ddl = 'table:all'); -- This means all table operations create/alter/drop
CREATE PUBLICATION xx FOR pub_all_table WITH (ddl = 'table'); -- same as above

This can be extended later to:
CREATE PUBLICATION xx FOR pub_all_func WITH (ddl = 'function:all');
CREATE PUBLICATION xx FOR pub_create_trigger (ddl = 'trigger:create');

In this approach, we don't need to add more stuff in gram.y and
will give fine-grained control as well.

Thanks for Vignesh for sharing this idea off-list.

Best Regards,
Hou zj