Horton

Re: Support logical replication of DDLs

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Amit Kapila <amit.kapila16@gmail.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-16T17:53: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.

On 2023-Feb-16, Jonathan S. Katz wrote:

[replication tries to execute this command]

> 2023-02-16 16:11:10.570 UTC [25207] STATEMENT:  CREATE OR REPLACE FUNCTION
> public.availability_rule_bulk_insert ( IN availability_rule
> public.availability_rule, IN day_of_week pg_catalog.int4 ) RETURNS
> pg_catalog.void LANGUAGE sql VOLATILE PARALLEL UNSAFE CALLED ON NULL INPUT
> SECURITY INVOKER COST 100 AS $_$
> 	    INSERT INTO availability (
> 	        room_id,
> 	        availability_rule_id,
> 	        available_date,
> 	        available_range
> 	    )

[which results in:]

> 2023-02-16 16:11:10.570 UTC [25207] ERROR:  relation "availability" does not
> exist at character 279

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.

(The fact that this is a security-invoker function prevents you from
attaching a SET search_path clause to the function, I believe?  Which
means it is extra dangerous to have an unqualified table reference
there.)

> My high level guess without looking at the code is that the apply worker is
> not aware of the search_path to use when processing functions during
> creation. Provided that the publisher/subscriber environments are similar
> (if not identical), I would expect that if the function create succeeds on
> the publisher, it should also succeed on the subscriber.

If we're going to force search_path and all other settings to be
identical, then we might as well give up the whole deparsing design and
transmit the original string for execution in the replica; it is much
simpler.  But this idea was rejected outright when this stuff was first
proposed years ago.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Crear es tan difícil como ser libre" (Elsa Triolet)