Re: Support logical replication of DDLs

On Sun, Apr 2, 2023 at 3:25 PM Phil Florent <philflorent(at)hotmail(dot)com> wrote:
>
> As an end-user, I am highly interested in the patch https://commitfest.postgresql.org/42/3595/ but I don't fully get its main goal in its first version.
> It's "for all tables" that will be implemented ?
> If one needs a complete replication of a cluster, a hot standby will always be more efficient than a publication right ? I use both for different needs in public hospitals I work for (hot standby for disaster recovery & logical replication for dss)
> The main interest of a publication is to be able to filter things on the publisher and to add stuff on the replicated cluster.
> If you compare PostgreSQL with less avanced RDBMS that don't really implement schemas (typically Oracle), the huge advantage of Postgre is that many things (e.g security) can be dynamically implemented via schemas.
> Developers just have put a table in the "good" schema and that's all. Logical DML replication now fully implements this logic since PostgreSQL 15. Only remaining problem is that a "for tables in schema" publication has to be owned by a superuser (because a normal user can have tables that don't belong to him in a schema it owns ?) If DDL replication only works with "FOR ALL TABLES " and not "FOR TABLES IN SCHEMA" it reduces its interest anyway.
>

I don't see any major issue with supporting it for both "FOR ALL
TABLES" and "FOR ALL TABLES IN SCHEMA". However, if we want to support
it with the "FOR TABLE .." variant then we will probably need to apply
some restrictions as we can only support 'alter' and 'drop'.
Additionally, there could be some additional problems to deal with
like say if the column list has been specified then we ideally
shouldn't send those columns even in DDL. For example, if one uses
Alter Table .. Rename Column and the new column name is not present in
the published column list then we shouldn't send it.

BTW, we have some proposals related to the specification of this
feature in emails [1][2][3]. See, if you have any suggestions on the
same?

Note- It seems you have copied this thread to pgsql-general. Is it
because you are not subscribed to pgsql-hackers? As this is a
development project so better to keep the discussion on pgsql-hackers.

[1] - https://www.postgresql.org/message-id/CAA4eK1%2B%2BY7a2SQq55DXT6neghZgj3j%2BpQ74%3D8zfT3k8Tkdj0ZA%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/CAA4eK1KZqvJsTt7OkS8AkxOKVvSpkQkPwsqzNmo10mFaVAKeZg%40mail.gmail.com
[3] - https://www.postgresql.org/message-id/OS0PR01MB571646874A3E165D93999A9D94889%40OS0PR01MB5716.jpnprd01.prod.outlook.com

--
With Regards,
Amit Kapila.