Horton

Re: Support logical replication of DDLs

Aleksander Alekseev <aleksander@timescale.com>

View thread
From: Aleksander Alekseev <aleksander@timescale.com>
To: Zheng Li <zhengli10@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-02-22T10:05: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.

Hi Zheng,

> I’m working on a patch to support logical replication of data
> definition language statements (DDLs).

That's great!

> However, there are still many edge cases to sort out because not every
> DDL statement can/should be replicated.

Maybe the first implementation shouldn't be perfect as long as known
limitations are documented and the future improvements are unlikely to
break anything for the users. Committing an MVP and iterating on this is
much simpler in terms of development and code review. Also, it will deliver
value to the users and give us feedback sooner.

> 1. DDL involving multiple tables where only some tables are replicated,
e.g.
>
>     DROP TABLE replicated_foo, notreplicated_bar;
>

I would add DROP TABLE ... CASCADE to the list. Also, let's not forget that
PostgreSQL supports table inheritance and table partitioning.

> 2. Any DDL that calls a volatile function, such as NOW() or RAND(), is
> likely to generate a different value on each replica. It is possible
> to work around these issues—for example, the publisher can replace any
> volatile function calls with a fixed return value when the statement
> is logged so that the subscribers all get the same value. We will have
> to consider some other cases.

That would make sense.

> [...]
> Whether a DDL should be replicated also depends on what granularity do
> we define DDL replication. For example, we can define DDL replication
> on these levels:
>
> 1. Database level
> Allows all DDLs for a database to be replicated except for certain
> edge cases (refer to the edge cases mentioned above).
> This is likely a major use case, such as in online major version upgrade.

To my knowledge, this is not a primary use case for logical replication.
Also, I suspect that implementing it may be a bit challenging. What if we
focus on table-level replication for now?

-- 
Best regards,
Aleksander Alekseev