Horton

Re: Support logical replication of DDLs

Zheng Li <zhengli10@gmail.com>

View thread
From: Zheng Li <zhengli10@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Smith <smithpb2250@gmail.com>, Ajin Cherian <itsajin@gmail.com>, vignesh C <vignesh21@gmail.com>, li jie <ggysxcq@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Japin Li <japinli@hotmail.com>, rajesh singarapu <rajesh.rs0541@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-02-15T22:16:05Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add a run_as_owner option to subscriptions.

  2. Refactor pgoutput_change().

  3. Print the correct aliases for DML target tables in ruleutils.

  4. Fix object identity string for transforms

  5. Add grantable MAINTAIN privilege and pg_maintain role.

  6. Get rid of recursion-marker values in enum AlterTableType

  7. Release cache tuple when no longer needed

  8. Empty search_path in logical replication apply worker and walsender.

  9. Refactor format_type APIs to be more modular

  10. Use wrappers of PG_DETOAST_DATUM_PACKED() more.

Attachments

Hi

We have not discussed much about the ownership of replicated objects.
Currently, replicated
objects belong to the subscription owner. However, it makes sense to
allow replicated
objects to keep the same owner from the publisher for certain use
cases otherwise users
may need to run lots of ALTER TABLE/OBJ OWNER TO manually. This issue
has been raised in [1] and [2].

I've implemented a prototype to allow replicated objects to have the
same owner from the publisher in
v69-0008-Allow-replicated-objects-to-have-the-same-owner-from.patch.
This patch needs to be applied
on top of the v69 DDL replication patch set.

Specifically, the changes include:
1. Change event trigger functions to collect the current role in
CollectedCommand.

2. Change Deparser function deparse_utility_command to encode the
owner role in the top-level
json element such as {myowner:role_name, fmt:..., identity:...} of the
deparsed jsonb output.
Also change the function deparse_ddl_json_to_string to retrieve the
myowner element from
the jsonb string.

3. Introduce a new subscription option match_ddl_owner: when turned
on, the apply worker
will apply DDL messages in the role retrieved from the "myowner" field
of the deparsed
jsonb string. The default value of match_ddl_owner is off.

Here is an example,
publisher:
CREATE PUBLICATION mypub
FOR ALL TABLES with (ddl = 'all');
CREATE ROLE user1;

subscriber:
CREATE SUBSCRIPTION mysub
CONNECTION 'dbname=source_db host=localhost user=master port=5433'
PUBLICATION mypub with (match_ddl_owner=true);
CREATE ROLE user1;

publisher:
CREATE TABLE t1 (a int, b varchar);
GRANT ALL ON schema public TO user1;
SET SESSION AUTHORIZATION user1;
CREATE TABLE t2 (a int, b varchar);

subscriber:
\d
              List of relations
 Schema | Name | Type  |        Owner
--------+------+-------+----------------------
 public | t1   | table | master
 public | t2   | table | user1

Your feedback is appreciated,
Zane

[1] https://www.postgresql.org/message-id/CAGfChW4vxVCgWs2%3Db%2BSDag0j3G-3Aqw5XvKnHVAReB-iysyj%2Bg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAAD30UKX%3DPbojrjU0webYy7Y9mz1HmDTM3dx_JJXpc%2BdXW-EQQ%40mail.gmail.com