Re: Support logical replication of DDLs - Mailing list pgsql-hackers

Hi,

On 2/14/23 10:01 PM, houzj.fnst@fujitsu.com wrote:

> Here is the new version patch which addressed above comments.
> I also fixed a bug for the deparsing of CREATE RULE that it didn't add
> parentheses for rule action list.

I started testing this change set from this patch. I'm doing a mix of 
happy path, "making mistakes" path, and "real world" testing, and 
testing this both with unidirectional and "origin=none" replication.

I wanted to report an issue I came up with using one of my real world 
cases. I had previously built a demo scheduling app to demonstrate 
several features of PostgreSQL to help with various kinds of data 
synchronization[1]. The first example uses a series of functions and 
triggers[2] to keep a calendar table up-to-date.

I set up an experiment as such:

1. Create two different clusters. In each cluster, create a DB
2. On Cluster 1, run:

CREATE PUBLICATION ddl FOR ALL TABLES WITH (ddl='all');

3. On Cluster 2, run:

CREATE SUBSCRIPTION ddl CONNECTION '' PUBLICATION ddl;

4. On Cluster 1, run the commands in [2]. Note that I reproduced the 
error both by running the commands individually and as part of a single 
transaction.

5. The transactions (or single transaction) completes successfully on 
Cluster 1

5. Cluster 2 reports the following error:


2023-02-16 16:11:10.537 UTC [25207] LOG:  logical replication apply 
worker for subscription "ddl" has started
2023-02-16 16:11:10.570 UTC [25207] ERROR:  relation "availability" does 
not exist at character 279
2023-02-16 16:11:10.570 UTC [25207] CONTEXT:  processing remote data for 
replication origin "pg_16733" during message type "DDL" in transaction 
890, finished at 0/BF298CC0
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
        )
        SELECT
            $1.room_id,
            $1.id,
            available_date::date + $2 - 1,
            tstzrange(
                /** start of range */
                (available_date::date + $2 - 1) + $1.start_time,
                /** end of range */
                /** check if there is a time wraparound, if so, increment 
by a day */
                CASE $1.end_time <= $1.start_time
                    WHEN TRUE THEN (available_date::date + $2) + $1.end_time
                    ELSE (available_date::date + $2 - 1) + $1.end_time
                END
            )
        FROM
            generate_series(
                date_trunc('week', CURRENT_DATE),
                date_trunc('week', CURRENT_DATE) + 
($1.generate_weeks_into_future::text || ' weeks')::interval,
                '1 week'::interval
            ) available_date;
    $_$
2023-02-16 16:11:10.573 UTC [15348] LOG:  background worker "logical 
replication worker" (PID 25207) exited with exit code 1

I attempted this with both async and sync logical replication. In sync 
mode, the publisher hangs and is unable to accept any more writes.

When I went in and explicitly schema qualified the tables in the 
functions[3], the example executed successfully.

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.

Thanks,

Jonathan

[1] https://github.com/CrunchyData/postgres-realtime-demo
[2] 
https://github.com/CrunchyData/postgres-realtime-demo/blob/main/examples/demo/demo1.sql
[3] https://gist.github.com/jkatz/5655c10da1a4c8691094e951ea07b036