From: Jonathan S. Katz <jkatz@postgresql.org>
To: houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com>
To: Amit Kapila <amit.kapila16@gmail.com>
To: 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: Alvaro Herrera <alvherre@alvh.no-ip.org>
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 11:48:22 -0500
Message-ID: <263bea1c-a897-417d-3765-ba6e1e24711e@postgresql.org> (raw)
In-Reply-To: <OS0PR01MB57168C9B93D7B2DC4D72F8EF94A39@OS0PR01MB5716.jpnprd01.prod.outlook.com>
References: <CAFPTHDbZzCeYMPJn0iFuD_ggpY-0ZHfVBHgQ9VJ6v4dF59xang@mail.gmail.com>
<CAA4eK1JGLCd+do-KurYrjnSxLwFNHf4bA7bL_r5Xc=ubVeVZpw@mail.gmail.com>
<OS0PR01MB57168C9B93D7B2DC4D72F8EF94A39@OS0PR01MB5716.jpnprd01.prod.outlook.com>
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/5655c10da1a4c8691094e951ea07b036Attachments:
OpenPGP_signature (application/pgp-signature, 839B) download
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: 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, alvherre@alvh.no-ip.org, 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: <263bea1c-a897-417d-3765-ba6e1e24711e@postgresql.org>
* 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