Thread: [HACKERS] Shouldn't duplicate addition to publication be a no-op?
I wonder if trying to add a relation to a publication that it is already a part should be considered a no-op, instead of causing an error (which happens in the ALTER PUBLICATION ADD TABLES case). create table bar (a int); create publication mypub for table bar; alter publication mypub add table bar; ERROR: relation "bar" is already member of publication "mypub" 2nd command should be a no-op, IMHO. Consider the following (I know we're discussing inheritance elsewhere as well): create table foo (a int); create table baz () inherits (foo); alter table bar inherit foo; alter table mypub add table foo; ERROR: relation "bar" is already member of publication "mypub" There is no way to add foo and children other than bar to mypub without doing so one-by-one. If my proposal to make that a no-op sounds desirable, attached patch implements that. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> I wonder if trying to add a relation to a publication that it is already a
> part should be considered a no-op, instead of causing an error (which
> happens in the ALTER PUBLICATION ADD TABLES case).
On what grounds?
The equivalent case for inheritance is an error:
regression=# create table foo (a int);
CREATE TABLE
regression=# create table bar () inherits (foo);
CREATE TABLE
regression=# alter table bar inherit foo;
ERROR: relation "foo" would be inherited from more than once
(Your example purporting to show the contrary contains a typo.)
If there's a reason why this case should act differently from that
precedent, you haven't shown it.
regards, tom lane
On Thu, Apr 13, 2017 at 9:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes: >> I wonder if trying to add a relation to a publication that it is already a >> part should be considered a no-op, instead of causing an error (which >> happens in the ALTER PUBLICATION ADD TABLES case). > > On what grounds? > > The equivalent case for inheritance is an error: > > regression=# create table foo (a int); > CREATE TABLE > regression=# create table bar () inherits (foo); > CREATE TABLE > regression=# alter table bar inherit foo; > ERROR: relation "foo" would be inherited from more than once Hmm, yes. Making it a no-op might be surprising to some. > (Your example purporting to show the contrary contains a typo.) Oops, I had meant: alter publication mypub add table foo; > If there's a reason why this case should act differently from that > precedent, you haven't shown it. Maybe we won't solve it by doing what I proposed, but if there is a database like this: create table foo (a int); create table bar () inherits(foo); create publication mypub for table foo; Dumping and restoring it into another database is not without errors, because of the order in which things are dumped: $ createdb test $ pg_dump -s | psql -e test <snip> CREATE PUBLICATION mypub WITH (PUBLISH INSERT, PUBLISH UPDATE, PUBLISH DELETE); ALTER PUBLICATION mypub ADD TABLE bar; ALTER PUBLICATION mypub ADD TABLE foo; ERROR: relation "bar" is already member of publication "mypub" But perhaps that's a pg_dump issue, not this. I haven't closely looked. Or perhaps something that will be resolved in the nearby "Logical replication and inheritance" thread. Thanks, Amit
On 4/13/17 06:23, Amit Langote wrote: > create table bar (a int); > create publication mypub for table bar; > alter publication mypub add table bar; > ERROR: relation "bar" is already member of publication "mypub" > > 2nd command should be a no-op, IMHO. We generally require a IF NOT EXISTS in those situations. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2017/04/15 8:53, Peter Eisentraut wrote: > On 4/13/17 06:23, Amit Langote wrote: >> create table bar (a int); >> create publication mypub for table bar; >> alter publication mypub add table bar; >> ERROR: relation "bar" is already member of publication "mypub" >> >> 2nd command should be a no-op, IMHO. > > We generally require a IF NOT EXISTS in those situations. Hmm, okay. So I guess the grammar support will be added later. By the way, Petr said in the other thread that it could be made a no-op (presumably without requiring IF NOT EXISTS) on the grounds that membership of table in publication is "soft object" or "property" rather than real object. Thanks, Amit
On Sun, Apr 16, 2017 at 11:58 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: > By the way, Petr said in the other thread that it could be made a no-op > (presumably without requiring IF NOT EXISTS) on the grounds that > membership of table in publication is "soft object" or "property" rather > than real object. I don't find that argument terribly convincing. The nearest parallel that we have for this is probably: ALTER EXTENSION name ADD member_object; ALTER EXTENSION name DROP member_object; I would guess this ought to work similarly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2017/04/17 14:46, Robert Haas wrote: > On Sun, Apr 16, 2017 at 11:58 PM, Amit Langote > <Langote_Amit_f8@lab.ntt.co.jp> wrote: >> By the way, Petr said in the other thread that it could be made a no-op >> (presumably without requiring IF NOT EXISTS) on the grounds that >> membership of table in publication is "soft object" or "property" rather >> than real object. > > I don't find that argument terribly convincing. > > The nearest parallel that we have for this is probably: > > ALTER EXTENSION name ADD member_object; > ALTER EXTENSION name DROP member_object; > > I would guess this ought to work similarly. Hmm, it does make sense to mock this behavior. create extension dummy; create table foo (); alter extension dummy add table foo; alter extension dummy add table foo; ERROR: table foo is already a member of extension "dummy" Thanks, Amit