Re: Support logical replication of DDLs

On 2/16/23 12:53 PM, Alvaro Herrera wrote:
> 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.

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.

> (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.)

Yes, but the level of danger would depend on how the schema is actually 
used. And while the above pattern is not great, it is still widely common.

>> 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.

Hm, maybe we go the other way in terms of execution of function bodies, 
i.e. we don't try to run/parse it on the subscriber? If the function 
body is just based in as a string, can we just insert it without doing 
any evaluation on the source code? I'd have to think a little bit more 
about the SQL standard bodies (BEGIN ATOMIC)...though AIUI it would 
possibly be a similar flow (execute on publisher, just copy w/o 
execution into subscriber)?

If I'm using DDL replication, I'm trying to keep my 
publisher/subscribers synchronized to a reasonable level of consistency, 
so it is highly likely the function should work when it's called. I know 
things can go wrong and break, particularly if I've made independent 
changes to the schema on the subscriber, but that can happen anyway 
today with functions on a single instance.

Thanks,

Jonathan