Shouldn't duplicate addition to publication be a no-op?

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers