Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf5Zi-0007sg-8l for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Apr 2022 19:56:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nf5Zh-0003Jy-5b for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Apr 2022 19:56:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf5Zg-0003Jo-6G for pgsql-hackers@lists.postgresql.org; Thu, 14 Apr 2022 19:56:16 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nf5Zc-0007Ba-EI for pgsql-hackers@lists.postgresql.org; Thu, 14 Apr 2022 19:56:15 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 55FFC5C0321; Thu, 14 Apr 2022 15:56:09 -0400 (EDT) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Thu, 14 Apr 2022 15:56:09 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm1; t=1649966169; x=1650052569; bh=8QWs/2B+FY E3+ZMC2P2LfUjHRC/tThY9anpBQJSens4=; b=XzpivjA06YsoNr8dPoQESY6Lkt +43/hQrbGQ/VNwf7uLlcSDfmtWdV7W9VEZ2BNZ6THnZdQTWa1KoR8MeM8wTYcYWV n5kaHKlgr5TPItWUiTL31wuxWzhNNESFyLzyHy6MV/uX8Y6P9Hr+iFvodVxqeZZS BLSy4gvJvoL1i3htRgw4aqZD8SAFAJSzYsTvLK0Z8o/iRdMyz4W9+YG/a1rx2XvR eOYnxGtXov3NzbRqo4TLsxI1vwk14Ni9/Fipfgrlsv7/3DrikwRuaGH3QlXCuuiZ Xp4lfeyiRcfh+h7HVhLWBQJLiDTPg/1w3J/34oYflEvF7z7vlE0HoSsDSFzQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1649966169; x= 1650052569; bh=8QWs/2B+FYE3+ZMC2P2LfUjHRC/tThY9anpBQJSens4=; b=H an6yniocpe4eSOs/a3KAd+48qF5AMbdBlQywS/xJGyDKfELTvwgaVWOW+UTGhGdA /gsj3vp9RgPoq3ntUthKtnz0Jz0xHM6CPiXDtHpn232tbVNwFrA/xCjxIh/CeXVA k8ZeWVaUIAS3K+ggKk/TP3p6n2QadrnXfBmzU4CVTli4UIHQJ/MBtKGIKqHynFPp r1YSblxkjfjblCN3J+VJuag6a7L7oQjG0HjGmYZycrFtWhQ0W19/rKhNBrJCzig0 pCPhYcnKULywcOLPY2BbwtaJ9aI322xu5JSS7+88H+YH4T2tKmu4tzkprn1bW7Mn le4zQ4ng0i20tvSps344Q== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvvddrudelfedgudeghecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefofgggkfgjfhffhffvufgtsegrtderreerredtnecuhfhrohhmpedfgfhu lhgvrhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecugg ftrfgrthhtvghrnhepjeethfeuvdfgffdtteevudeftdejueffkeelveekhfevkeegleef hfejhedtjeegnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlh hushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegv uhhlvghrthhordgtohhm X-ME-Proxy: Received: by mailuser.nyi.internal (Postfix, from userid 501) id F0BAE15A007A; Thu, 14 Apr 2022 15:56:08 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.7.0-alpha0-387-g7ea99c4045-fm-20220413.002-g7ea99c40 Mime-Version: 1.0 Message-Id: <00afeb42-19b3-47a6-821e-e78d366eb0b0@www.fastmail.com> In-Reply-To: References: Date: Thu, 14 Apr 2022 16:55:24 -0300 From: "Euler Taveira" To: "Peter Eisentraut" , "vignesh C" , "PostgreSQL Hackers" Subject: Re: Skipping schema changes in publication Content-Type: multipart/alternative; boundary=7d489ae1477744c88b7ad08c168c49c2 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --7d489ae1477744c88b7ad08c168c49c2 Content-Type: text/plain On Thu, Apr 14, 2022, at 10:47 AM, Peter Eisentraut wrote: > On 12.04.22 08:23, vignesh C wrote: > > I have also included the implementation for skipping a few tables from > > all tables publication, the 0002 patch has the implementation for the > > same. > > 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 > > tables. > > Ex: > > CREATE PUBLICATION pub1 FOR ALL TABLES SKIP TABLE t1,t2; > > OR > > ALTER PUBLICATION pub1 ADD SKIP TABLE t1,t2; > > We have already allocated the "skip" terminology for skipping > transactions, which is a dynamic run-time action. We are also using the > term "skip" elsewhere to skip locked rows, which is similarly a run-time > action. I think it would be confusing to use the term SKIP for DDL > construction. I didn't like the SKIP choice too. We already have EXCEPT for IMPORT FOREIGN SCHEMA and if I were to suggest a keyword, it would be EXCEPT. > I would also think about this in broader terms. For example, sometimes > people want features like "all columns except these" in certain places. > The syntax for those things should be similar. The questions are: What kind of issues does it solve? Do we have a workaround for it? > That said, I'm not sure this feature is worth the trouble. If this is > useful, what about "whole database except these schemas"? What about > "create this database from this template except these schemas". This > could get out of hand. I think we should encourage users to group their > object the way they want and not offer these complicated negative > selection mechanisms. I have the same impression too. We already provide a way to: * include individual tables; * include all tables; * include all tables in a certain schema. Doesn't it cover the majority of the use cases? We don't need to cover all possible cases in one DDL command. IMO the current grammar for CREATE PUBLICATION is already complicated after the ALL TABLES IN SCHEMA. You are proposing to add "ALL TABLES SKIP ALL TABLES" that sounds repetitive but it is not; doesn't seem well-thought-out. I'm also concerned about possible gotchas for this proposal. The first command above suggests that it skips all tables in a certain schema. What happen if I decide to include a particular table of the skipped schema (second command)? ALTER PUBLICATION pub1 ADD SKIP ALL TABLES IN SCHEMA s1,s2; ALTER PUBLICATION pub1 ADD TABLE s1.foo; Having said that I'm not wedded to this proposal. Unless someone provides compelling use cases for this additional syntax, I think we should leave the publication syntax as is. -- Euler Taveira EDB https://www.enterprisedb.com/ --7d489ae1477744c88b7ad08c168c49c2 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Thu, Apr 14,= 2022, at 10:47 AM, Peter Eisentraut wrote:
On 12.04.22 08:23, vignesh C wrote:
=
> I have also included the implementation for skipping a f= ew tables from
> all tables publication, the 0002 patch= has the implementation for the
> same.
&= gt; 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
> tables.
> Ex:
> CREATE PUBLICATION pub1 FOR ALL TABLES SKIP TABLE t1,t2;
> OR
> ALTER PUBLICATION pub1 ADD SKIP  T= ABLE t1,t2;

We have already allocated the "= skip" terminology for skipping 
transactions, which i= s a dynamic run-time action.  We are also using the 
=
term "skip" elsewhere to skip locked rows, which is similarly a run= -time 
action.  I think it would be confusing to= use the term SKIP for DDL 
construction.
I didn't like the SKIP choice too. We already have EXCEP= T for IMPORT FOREIGN
SCHEMA and if I were to suggest a key= word, it would be EXCEPT.

I would also think about this in broader t= erms.  For example, sometimes 
people want featu= res like "all columns except these" in certain places. 
The syntax for those things should be similar.
=
The questions are:
What kind of issues does it solve?=
Do we have a workaround for it?

<= blockquote type=3D"cite" id=3D"qt" style=3D"">
That said, I'm not su= re this feature is worth the trouble.  If this is 
useful, what about "whole database except these schemas"?  What = about 
"create this database from this template excep= t these schemas".  This 
could get out of hand.&= nbsp; I think we should encourage users to group their 
object the way they want and not offer these complicated negative&nbs= p;
selection mechanisms.
I have= the same impression too. We already provide a way to:
* include individual tables;
* include all tab= les;
* include all tables in a certain schema.

Doesn't it cover the majority of the use cases? We don= 't need to cover all
possible cases in one DDL command. IM= O the current grammar for CREATE
PUBLICATION is already co= mplicated after the ALL TABLES IN SCHEMA. You are
proposin= g to add "ALL TABLES SKIP ALL TABLES" that sounds repetitive but it is
not; doesn't seem well-thought-out. I'm also concerned abou= t possible gotchas
for this proposal. The first command ab= ove suggests that it skips all tables in a
certain schema.= What happen if I decide to include a particular table of the
<= div>skipped schema (second command)?

ALTER = PUBLICATION pub1 ADD SKIP ALL TABLES IN SCHEMA s1,s2;
ALTE= R PUBLICATION pub1 ADD TABLE s1.foo;

Having= said that I'm not wedded to this proposal. Unless someone provides
<= /div>
compelling use cases for this additional syntax, I think we sh= ould leave the
publication syntax as is.

= --
Euler Taveira
EDB   ht= tps://www.enterprisedb.com/

--7d489ae1477744c88b7ad08c168c49c2--