On Mon, Jul 19, 2021 at 9:32 AM tanghy.f...@fujitsu.com < tanghy.f...@fujitsu.com> wrote: > > On Friday, July 16, 2021 6:10 PM vignesh C <vignes...@gmail.com> > > On Wed, Jul 14, 2021 at 6:25 PM houzj.f...@fujitsu.com > > <houzj.f...@fujitsu.com> wrote: > > > > > > Wednesday, July 14, 2021 6:17 PM vignesh C <vignes...@gmail.com> wrote: > > > > On Tue, Jul 13, 2021 at 12:06 PM tanghy.f...@fujitsu.com > > > > <tanghy.f...@fujitsu.com> wrote: > > > > > > > > > > On Monday, July 12, 2021 5:36 PM vignesh C <vignes...@gmail.com> > > wrote: > > > > > > > > > > > > Thanks for reporting this issue, this issue is fixed in the v10 > > > > > > patch attached at [1]. > > > > > > [1] - https://www.postgresql.org/message-id/CALDaNm2%2BtR%2B8R- > > > > > > sD1CSyMbZcZbkintZE-avefjsp7LCkm6HMmw%40mail.gmail.com > > > > > > > > > > Thanks for fixing it. > > > > > > > > > > By applying your V10 patch, I saw three problems, please have a look. > > > > > > > > > > 1. An issue about pg_dump. > > > > > When public schema was published, the publication was created in the > > > > > output file, but public schema was not added to it. (Other schemas > > > > > could be added as expected.) > > > > > > > > > > I looked into it and found that selectDumpableNamespace function marks > > > > DUMP_COMPONENT_DEFINITION as needless when the schema is public, > > > > leading to schema public is ignored in getPublicationSchemas. So we'd better > > > > check whether schemas should be dumped in another way. > > > > > > > > > > I tried to fix it with the following change, please have a look. > > > > > (Maybe we also need to add some comments for it.) > > > > > > > > > > diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c > > > > > index f6b4f12648..a327d2568b 100644 > > > > > --- a/src/bin/pg_dump/pg_dump.c > > > > > +++ b/src/bin/pg_dump/pg_dump.c > > > > > @@ -4206,7 +4206,8 @@ getPublicationSchemas(Archive *fout, > > > > NamespaceInfo nspinfo[], int numSchemas) > > > > > * Ignore publication membership of schemas whose > > > > definitions are not > > > > > * to be dumped. > > > > > */ > > > > > - if (!(nsinfo->dobj.dump & > > > > DUMP_COMPONENT_DEFINITION)) > > > > > + if (!((nsinfo->dobj.dump & > > > > DUMP_COMPONENT_DEFINITION) > > > > > + || (strcmp(nsinfo->dobj.name, "public") == 0 > > > > > + && nsinfo->dobj.dump != DUMP_COMPONENT_NONE))) > > > > > continue; > > > > > > > > > > pg_log_info("reading publication membership for schema > > > > > \"%s\"", > > > > > > > > I felt it is intentionally done like that as the pubic schema is created by default, > > > > hence it is not required to dump else we will get errors while restoring. > > > > Thougths? > > > > > > Thanks for the new patches and I also looked at this issue. > > > > > > For user defined schema and publication: > > > -------------------------- > > > create schema s1; > > > create publication pub2 for SCHEMA s1; > > > -------------------------- > > > > > > pg_dump will only generate the following SQLs: > > > > > > ------pg_dump result------ > > > CREATE PUBLICATION pub2 WITH (publish = 'insert, update, delete, truncate'); > > > ALTER PUBLICATION pub2 ADD SCHEMA s1; > > > -------------------------- > > > > > > But for the public schema: > > > -------------------------- > > > create publication pub for SCHEMA public; > > > -------------------------- > > > > > > pg_dump will only generate the following SQL: > > > > > > ------pg_dump result------ > > > CREATE PUBLICATION pub WITH (publish = 'insert, update, delete, truncate'); > > > -------------------------- > > > > > > It didn't generate SQL like "ALTER PUBLICATION pub ADD SCHEMA public;" which > > > means the public schema won't be published after restoring. So, I think we'd > > > better let the pg_dump generate the ADD SCHEMA public SQL. Thoughts ? > > > > Thanks for reporting this issue, this issue is fixed in the v12 patch attached. > > > > I tested your v12 patch and found a problem in the following case. > > Step 1: > postgres=# create schema s1; > CREATE SCHEMA > postgres=# create table s1.t1 (a int); > CREATE TABLE > postgres=# create publication pub_t for table s1.t1; > CREATE PUBLICATION > postgres=# create publication pub_s for schema s1; > CREATE PUBLICATION > > Step 2: > pg_dump -N s1 > > I dumped and excluded schema s1, pg_dump generated the following SQL: > ------------------------------- > ALTER PUBLICATION pub_s ADD SCHEMA s1; > > I think it was not expected because SQL like "ALTER PUBLICATION pub_t ADD TABLE s1.t1" was not generated in my case. Thoughts?
Thanks for reporting this issue, this issue is fixed in the v13 patch posted at [1] [1] - https://www.postgresql.org/message-id/CALDaNm0%3DMaXyAok5iq_-DeWUd81vpdF47-MZbbrsd%2BzB2P6WwA%40mail.gmail.com Regards, Vignesh