Horton

Re: Support logical replication of DDLs

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Ajin Cherian <itsajin@gmail.com>, Peter Smith <smithpb2250@gmail.com>, vignesh C <vignesh21@gmail.com>, Zheng Li <zhengli10@gmail.com>, li jie <ggysxcq@gmail.com>, Dilip Kumar <dilipbalaut@gmail.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-17T09:15:07Z
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.

On Fri, Feb 17, 2023 at 1:13 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> On 2/16/23 2:38 PM, Alvaro Herrera wrote:
> > On 2023-Feb-16, Jonathan S. Katz wrote:
> >
> >> On 2/16/23 12:53 PM, Alvaro Herrera wrote:
> >
> >>> I don't think this is the fault of logical replication.  Consider that
> >>> for the backend server, the function source code is just an opaque
> >>> string that is given to the plpgsql engine to interpret.  So there's no
> >>> way for the logical DDL replication engine to turn this into runnable
> >>> code if the table name is not qualified.
> >>
> >> Sure, that's fair. That said, the example above would fall under a "typical
> >> use case", i.e. I'm replicating functions that call tables without schema
> >> qualification. This is pretty common, and as logical replication becomes
> >> used for more types of workloads (e.g. high availability), we'll definitely
> >> see this.
> >
> > Hmm, I think you're saying that replay should turn check_function_bodies
> > off, and I think I agree with that.
>
> Yes, exactly. +1
>

But will that be sufficient? I guess such functions can give errors at
a later stage when invoked at DML or another DDL time. Consider the
following example:

Pub:
CREATE PUBLICATION pub FOR ALL TABLES with (ddl = 'all');

Sub:
(Set check_function_bodies = off in postgresql.conf)
CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres' PUBLICATION pub;

Pub:
CREATE FUNCTION t1(a int) RETURNS int AS $$
select a+1;
$$ LANGUAGE sql;
CREATE FUNCTION t(a int) RETURNS int AS $$
select t1(a);
$$ LANGUAGE sql;
CREATE TABLE tbl1 (a int primary key, b text);
create index idx on tbl1(t(a));

insert into tbl1 values (1,1); -- This insert on publisher causes an
error on the subscriber. Check subscriber Logs (ERROR:  function
t1(integer) does not exist at character 9.)

This happens because of the function used in the index expression.
Now, this is not the only thing, the replication can even fail during
DDL replication when the function like above is IMMUTABLE and used as
follows: ALTER TABLE tbl ADD COLUMN d int DEFAULT t(1);

Normally, it is recommended that users can fix such errors by
schema-qualifying affected names. See commits 11da97024a and
582edc369c.

-- 
With Regards,
Amit Kapila.