From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jonathan S. Katz <jkatz@postgresql.org>
Cc: houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Ajin Cherian <itsajin@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>
Cc: Zheng Li <zhengli10@gmail.com>
Cc: li jie <ggysxcq@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Japin Li <japinli@hotmail.com>
Cc: rajesh singarapu <rajesh.rs0541@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Support logical replication of DDLs
Date: Thu, 16 Feb 2023 18:53:05 +0100
Message-ID: <20230216175305.2ayyrxrm3lz6oy5l@alvherre.pgsql> (raw)
In-Reply-To: <263bea1c-a897-417d-3765-ba6e1e24711e@postgresql.org>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: alvherre@alvh.no-ip.org, jkatz@postgresql.org, houzj.fnst@fujitsu.com, amit.kapila16@gmail.com, itsajin@gmail.com, smithpb2250@gmail.com, vignesh21@gmail.com, zhengli10@gmail.com, ggysxcq@gmail.com, dilipbalaut@gmail.com, sawada.mshk@gmail.com, japinli@hotmail.com, rajesh.rs0541@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Support logical replication of DDLs
In-Reply-To: <20230216175305.2ayyrxrm3lz6oy5l@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox