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 279I 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
OpenPGP_signature
Description: OpenPGP digital signature
