Thread: [HACKERS] pg_dump ignoring information_schema tables which used in CreatePublication.

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

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 <tushar.ahuja@enterprisedb.com> 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

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

tushar <tushar.ahuja@enterprisedb.com> 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

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

On Mon, May 22, 2017 at 5:22 PM, tushar <tushar.ahuja@enterprisedb.com> 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

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

On Wed, May 24, 2017 at 7:16 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> 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

On 5/24/17 21:36, Robert Haas wrote:
> On Wed, May 24, 2017 at 7:16 PM, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> 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

On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> 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

On 5/25/17 09:55, Robert Haas wrote:
> On Thu, May 25, 2017 at 8:32 AM, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> 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

On Thu, May 25, 2017 at 5:06 PM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> 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

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

On Fri, May 26, 2017 at 10:46:12PM -0300, Euler Taveira wrote:
> 2017-05-26 17:52 GMT-03:00 Peter Eisentraut <peter.eisentraut@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.

[Action required within three days.  This is a generic notification.]

The above-described topic is currently a PostgreSQL 10 open item.  Peter,
since you committed the patch believed to have created it, you own this open
item.  If some other commit is more relevant or if this does not belong as a
v10 open item, please let us know.  Otherwise, please observe the policy on
open item ownership[1] and send a status update within three calendar days of
this message.  Include a date for your subsequent status update.  Testers may
discover new open items at any time, and I want to plan to get them all fixed
well in advance of shipping v10.  Consequently, I will appreciate your efforts
toward speedy resolution.  Thanks.

[1] https://www.postgresql.org/message-id/20170404140717.GA2675809%40tornado.leadboat.com

On 5/29/17 22:14, Noah Misch wrote:
> On Fri, May 26, 2017 at 10:46:12PM -0300, Euler Taveira wrote:
>> 2017-05-26 17:52 GMT-03:00 Peter Eisentraut <peter.eisentraut@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.
> [Action required within three days.  This is a generic notification.]

Unless there are any new insights, I propose to close this item as not
new and not worth fixing, per the above.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services