Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Euler Taveira]

2017-05-26 17:52 GMT-03:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

>
> You cannot publish a system catalog.  But a user-created table in
> information_schema is not a system catalog.


Replication of information_schema tables works. However, pg_dump doesn't
include information_schema tables into CREATE PUBLICATION command
(user-defined information_schema tables aren't included in pg_dump even
*before* logical replication). IMO allow publish/subscribe of tables into
information_schema is harmless (they aren't special tables like catalogs).
Also, how many people would create real tables into information_schema?
Almost zero. Let's leave it alone. Since pg_dump doesn't document that
information_schema isn't dumped, I think we shouldn't document this for
logical replication.


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Peter Eisentraut]

On 5/25/17 22:45, Robert Haas wrote:
> I guess I'm not convinced that it's really the same.  I think we want
> to allow users to create views over system objects; our life might be
> easier if we hadn't permitted that, but views over e.g. pg_locks are
> common, and prohibiting them doesn't seem like a reasonable choice.
> I'm less clear that we want to let them publish system objects.  Aside
> from the pg_dump issues, does it work?

The confusion in this discussion is exactly that there are multiple
definitions of what a "system object" might be.

You cannot publish a system catalog.  But a user-created table in
information_schema is not a system catalog.

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Robert Haas]

On Thu, May 25, 2017 at 5:06 PM, Peter Eisentraut
 wrote:
> They are the same cases.
>
> a) Create object in information_schema.
>
> b) Create another object elsewhere that depends on it.
>
> c) pg_dump will dump (b) but not (a).
>
> So the fix, if any, would be to prevent (a), or prevent (b), or fix (c).

I guess I'm not convinced that it's really the same.  I think we want
to allow users to create views over system objects; our life might be
easier if we hadn't permitted that, but views over e.g. pg_locks are
common, and prohibiting them doesn't seem like a reasonable choice.
I'm less clear that we want to let them publish system objects.  Aside
from the pg_dump issues, does it work?

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Peter Eisentraut]

On 5/25/17 09:55, Robert Haas wrote:
> On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut
>  wrote:
>>> Well, I think if it's not going to work, it should be prohibited,
>>> rather than seeming to work but then not actually working.
>>
>> Here is a similar case that pg_dump fails on:
>>
>> create table information_schema.test1 (a int);
>> create view public.test2 as select * from information_schema.test1;
>>
>> It's not clear how to address that, or whether it's worth it.
> 
> Sure, that case is hard to address.  But why is *this* case hard to address?

They are the same cases.

a) Create object in information_schema.

b) Create another object elsewhere that depends on it.

c) pg_dump will dump (b) but not (a).

So the fix, if any, would be to prevent (a), or prevent (b), or fix (c).

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Robert Haas]

On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut
 wrote:
>> Well, I think if it's not going to work, it should be prohibited,
>> rather than seeming to work but then not actually working.
>
> Here is a similar case that pg_dump fails on:
>
> create table information_schema.test1 (a int);
> create view public.test2 as select * from information_schema.test1;
>
> It's not clear how to address that, or whether it's worth it.

Sure, that case is hard to address.  But why is *this* case hard to address?

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Peter Eisentraut]

On 5/24/17 21:36, Robert Haas wrote:
> On Wed, May 24, 2017 at 7:16 PM, Peter Eisentraut
>  wrote:
>> On 5/22/17 07:42, Kuntal Ghosh wrote:
>>> pg_dump ignores anything created under object name "pg_*" or
>>> "information_schema".
>>
>> Publications have a slightly different definition of what tables to
>> ignore/prohibit than pg_dump, partly because they have more built-in
>> knowledge.  I'm not sure whether it's worth fixing this.
> 
> Well, I think if it's not going to work, it should be prohibited,
> rather than seeming to work but then not actually working.

Here is a similar case that pg_dump fails on:

create table information_schema.test1 (a int);
create view public.test2 as select * from information_schema.test1;

It's not clear how to address that, or whether it's worth it.

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Robert Haas]

On Wed, May 24, 2017 at 7:16 PM, Peter Eisentraut
 wrote:
> On 5/22/17 07:42, Kuntal Ghosh wrote:
>> pg_dump ignores anything created under object name "pg_*" or
>> "information_schema".
>
> Publications have a slightly different definition of what tables to
> ignore/prohibit than pg_dump, partly because they have more built-in
> knowledge.  I'm not sure whether it's worth fixing this.

Well, I think if it's not going to work, it should be prohibited,
rather than seeming to work but then not actually working.

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Peter Eisentraut]

On 5/22/17 07:42, Kuntal Ghosh wrote:
> pg_dump ignores anything created under object name "pg_*" or
> "information_schema".

Publications have a slightly different definition of what tables to
ignore/prohibit than pg_dump, partly because they have more built-in
knowledge.  I'm not sure whether it's worth fixing this.

-- 
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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Kuntal Ghosh]

On Mon, May 22, 2017 at 5:22 PM, tushar  wrote:
> On 05/22/2017 05:12 PM, Kuntal Ghosh wrote:
>>
>> pg_dump ignores anything created under object name "pg_*" or
>> "information_schema".
>
> In this below scenario  , I am able to see - pg_dump catch the information
> of table which is created under information_schema
>
> --
> -- Name: e1; Type: VIEW; Schema: public; Owner: centos
> --
>
> CREATE VIEW e1 AS
>  SELECT abc.n
>FROM information_schema.abc;
> 
>
The view is created in public schema. Hence, you're able to take a
dump for the same. However, you'll probably get an error saying
"relation information_schema.abc doesn't exist" while restoring the
dump.

For publications, the create definition(CREATE PUBLICATION) and
addition of tables(ALTER publication ADD TABLE) are separated. Hence,
it can skip all the relations created under information_schema or
anything under "pg_*" schema.

-- 
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com


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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[tushar]

On 05/22/2017 05:31 PM, Tom Lane wrote:

Do we have a prohibition against publishing/subscribing anything
in pg_catalog?

Yes.

postgres=# create publication pub for table pg_catalog.pg_AM;
ERROR:  "pg_am" is a system table
DETAIL:  System tables cannot be added to publications.
postgres=#

--
regards,tushar
EnterpriseDB  https://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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Tom Lane]

tushar  writes:
> On 05/22/2017 05:12 PM, Kuntal Ghosh wrote:
>> pg_dump ignores anything created under object name "pg_*" or
>> "information_schema".

> In this below scenario  , I am able to see - pg_dump catch the 
> information of table which is created under information_schema

Creating your own tables inside information_schema is not considered
a supported operation anyway.  In general, there should be only
system views in there, so there's no point in publishing them.

Do we have a prohibition against publishing/subscribing anything
in pg_catalog?  (Please tell me the answer is yes, because if it's
no, I'll bet good money that attempting to do so reveals all sorts
of bugs.)  I would suggest that information_schema, and probably
pg_toast, should have the same restriction.

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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[tushar]

On 05/22/2017 05:12 PM, Kuntal Ghosh wrote:

pg_dump ignores anything created under object name "pg_*" or
"information_schema".
In this below scenario  , I am able to see - pg_dump catch the 
information of table which is created under information_schema


postgres=# create database  ntest;
\CREATE DATABASE
postgres=# \c ntest
You are now connected to database "ntest" as user "centos".
ntest=# create table information_schema.abc(n int);
CREATE TABLE
ntest=# create   view e1  as select * from information_schema.abc;
CREATE VIEW

[centos@centos-cpula regress]$ pg_dump -Fp  ntest > /tmp/a.a

cat /tmp/a.a

SET search_path = public, pg_catalog;

--
-- Name: e1; Type: VIEW; Schema: public; Owner: centos
--

CREATE VIEW e1 AS
 SELECT abc.n
   FROM information_schema.abc;


--
regards,tushar
EnterpriseDB  https://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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Kuntal Ghosh]

Hello,

pg_dump ignores anything created under object name "pg_*" or
"information_schema". I guess you will not have any "CREATE TABLE"
definition  as well for information_schema.abc. Related code:

else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 ||
 strcmp(nsinfo->dobj.name, "information_schema") == 0)
{
/* Other system schemas don't get dumped */
nsinfo->dobj.dump_contains = nsinfo->dobj.dump = DUMP_COMPONENT_NONE;
}

Hence, there is no point of creating publication for it in the dump.

On Mon, May 22, 2017 at 4:22 PM, tushar  wrote:
> Hi,
>
> pg_dump is ignoring tables which created under information_schema schema
> for  CREATE PUBLICATION .
>
> postgres=# create database  test;
> CREATE DATABASE
> postgres=# \c test
> You are now connected to database "test" as user "centos".
> test=# create table information_schema.abc(n int);
> CREATE TABLE
> test=# create publication test for table information_schema.abc;
> CREATE PUBLICATION
> test=# select * from pg_publication_tables;
>  pubname | schemaname | tablename
> -++---
>  test| information_schema | abc
> (1 row)
>
> test=# \q
> [centos@centos-cpula regress]$ pg_dump -Fp  test > /tmp/a.a
> [centos@centos-cpula regress]$ cat /tmp/a.a|grep publication -i
> -- Name: test; Type: PUBLICATION; Schema: -; Owner: centos
> CREATE PUBLICATION test WITH (publish = 'insert, update, delete');
> ALTER PUBLICATION test OWNER TO centos;
> [centos@centos-cpula regress]$
>
> --
> regards,tushar
> EnterpriseDB  https://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



-- 
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com


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

[HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[tushar]

Hi,

pg_dump is ignoring tables which created under information_schema 
schema  for  CREATE PUBLICATION .


postgres=# create database  test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "centos".
test=# create table information_schema.abc(n int);
CREATE TABLE
test=# create publication test for table information_schema.abc;
CREATE PUBLICATION
test=# select * from pg_publication_tables;
 pubname | schemaname | tablename
-++---
 test| information_schema | abc
(1 row)

test=# \q
[centos@centos-cpula regress]$ pg_dump -Fp  test > /tmp/a.a
[centos@centos-cpula regress]$ cat /tmp/a.a|grep publication -i
-- Name: test; Type: PUBLICATION; Schema: -; Owner: centos
CREATE PUBLICATION test WITH (publish = 'insert, update, delete');
ALTER PUBLICATION test OWNER TO centos;
[centos@centos-cpula regress]$

--
regards,tushar
EnterpriseDB  https://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