On Sat, Apr 23, 2022 at 2:09 AM Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.com> wrote: > > On Tue, Mar 22, 2022 at 12:39 PM vignesh C <vignes...@gmail.com> wrote: > > > > Hi, > > > > This feature adds an option to skip changes of all tables in specified > > schema while creating publication. > > This feature is helpful for use cases where the user wants to > > subscribe to all the changes except for the changes present in a few > > schemas. > > Ex: > > CREATE PUBLICATION pub1 FOR ALL TABLES SKIP ALL TABLES IN SCHEMA s1,s2; > > OR > > ALTER PUBLICATION pub1 ADD SKIP ALL TABLES IN SCHEMA s1,s2; > > > > A new column pnskip is added to table "pg_publication_namespace", to > > maintain the schemas that the user wants to skip publishing through > > the publication. Modified the output plugin (pgoutput) to skip > > publishing the changes if the relation is part of skip schema > > publication. > > As a continuation to this, I will work on implementing skipping tables > > from all tables in schema and skipping tables from all tables > > publication. > > > > Attached patch has the implementation for this. > > This feature is for the pg16 version. > > Thoughts? > > The feature seems to be useful especially when there are lots of > schemas in a database. However, I don't quite like the syntax. Do we > have 'SKIP' identifier in any of the SQL statements in SQL standard? > Can we think of adding skip_schema_list as an option, something like > below? > > CREATE PUBLICATION foo FOR ALL TABLES (skip_schema_list = 's1, s2'); > ALTER PUBLICATION foo SET (skip_schema_list = 's1, s2'); - to set > ALTER PUBLICATION foo SET (skip_schema_list = ''); - to reset >
I had been wondering for some time if there was any way to introduce a more flexible pattern matching into PUBLICATION but without bloating the syntax. Maybe your idea to use an option for the "skip" gives a way to do it... For example, if we could use regex (for <schemaname>.<tablename> patterns) for the option value then.... ~~ e.g.1. Exclude certain tables: // do NOT publish any tables of schemas s1,s2 CREATE PUBLICATION foo FOR ALL TABLES (exclude_match = '(s1\..*)|(s2\..*)'); // do NOT publish my secret tables (those called "mysecretXXX") CREATE PUBLICATION foo FOR ALL TABLES (exclude_match = '(.*\.mysecret.*)'); ~~ e.g.2. Only allow certain tables. // ONLY publish my tables (those called "mytableXXX") CREATE PUBLICATION foo FOR ALL TABLES (subset_match = '(.*\.mytable.*)'); // So following is equivalent to FOR ALL TABLES IN SCHEMA s1 CREATE PUBLICATION foo FOR ALL TABLES (subset_match = '(s1\..*)'); ------ Kind Regards, Peter Smith. Fujitsu Australia
