Обсуждение: If a schema is created as part of an extension, should all user created tables created in that schema be considered part of the extension?

I just discovered something which was a little alarming to me.

In the postgis_tiger_geocoder extension, I had switched to having the schema
where user data download is stored created as part of create extension
script so I wouldn't need to check during load.

So I have a statement like this in the extension script:

DO 
$$
BEGIN IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name
= 'tiger_data') THEN      CREATE SCHEMA tiger_data;      END IF;   
END 
$$ LANGUAGE 'plpgsql';


I usually exclude backing the tiger_data schema up in my regular backups
because it changes only yearly and takes up a good chunk of space, so this
is why I probably never noticed this issue.

When I recently did a full database backup (not excluding tiger_data), I
discovered that none of the tables were in the backup file.
What's interesting is I can explicitly choose to backup a single user table,
but I can never backup the whole schema.

To allow backup I had to do

ALTER EXTENSION postgis_tiger_geocoder DROP SCHEMA tiger_data;

I'm going to fix this on my end, by just dropping tiger_data schema from the
extension as part of the install script if it is part of the extension.  The
behavior seems a little odd to me though.

This was testing on - PostgreSQL 9.5.1, compiled by Visual C++ build 1800,
64-bit .

I should add, the user tables in tiger_data do inherit from skeletons in
tiger schema.  Though given that dropping the schema fixes the issue, I
didn't think that was related, but I didn't verify.


Thanks,
Regina

"Regina Obe" <lr@pcorp.us> writes:
> I just discovered something which was a little alarming to me.
> In the postgis_tiger_geocoder extension, I had switched to having the schema
> where user data download is stored created as part of create extension
> script so I wouldn't need to check during load.
> ...
> When I recently did a full database backup (not excluding tiger_data), I
> discovered that none of the tables were in the backup file.

I think the chain of events is that the tiger_data schema is marked as
not to be backed up (because it belongs to an extension) and then all
of its tables are marked as not to be backed up because they're in a
schema that's not to be backed up.  The latter behavior is meant to
implement --exclude-schema but it's firing on this case too.  I think
it might've behaved differently before c42df2d46.

The whole idea of non-extension objects in a schema owned by an extension
seems pretty grotty to me though; that would mean that dropping the
extension forces dropping those objects, which I wouldn't think you want.
So I'm not sure it's worth complicating matters to make this case behave
differently in pg_dump.
        regards, tom lane

> I think the chain of events is that the tiger_data schema is marked as not
to be backed up (because it belongs to an extension) and then all of its
tables are marked as not to be backed up because they're in a schema that's
not to be backed up.  The latter 
> behavior is meant to implement --exclude-schema but it's firing on this
case too.  I think it might've behaved differently before c42df2d46.

> The whole idea of non-extension objects in a schema owned by an extension
seems pretty grotty to me though; that would mean that dropping the
extension forces dropping those objects, which I wouldn't think you want.
> So I'm not sure it's worth complicating matters to make this case behave
differently in pg_dump.

>            regards, tom lane

Ah indeed if I try my pg_dump 9.5beta1 executable it does backup the tables
so hasn't always been this way.

Are we on the same page, pg_dump should have the same respect for user
created data as the extension model does even if ischema is created by the
extension? I read your last statement two different ways. Sorry.

I was also wrong I can't choose to backup a table from that schema.  Not
sure what I was doing before, so that's not as inconsistent as I thought.


Some more foods for thought:

I guess what I find a little confusing is the schema created by the
extension to install extension functions/tables etc  is not considered part
of the extension.

For example this postgis_tiger_geocoder  is not relocatable.  So it installs
things in schema called tiger which is automatically created by create
extension via extension mechanism.  So when I drop the extension the schema
is still there.

I also can as a user then create tables, functions whatever in tiger schema
and those get backed up.

So as a user, why would I expect schema tiger_data to be any different from
schema tiger that actually holds all tiger installed stuff?

I can understand multiple extensions may install things in same schema even
if they are not schema relocateable so there is a reason for that, but still
it feels a bit odd that we are treating tiger_data differently.

I should also add, that if I do put things in tiger_data and then try to
drop the extension, the extension refuses to drop without cascade as it
should because those objects aren't part of the extension. So why should
pg_dump not have the same respect for user created data?


Thanks,
Regina