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? - Mailing list pgsql-hackers

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