Re: Support logical replication of DDLs

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)