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?

From: "Regina Obe" <lr(at)pcorp(dot)us>
To: "'PostgreSQL-development'" <pgsql-hackers(at)postgresql(dot)org>
Subject: 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?
Date: 2016-03-26 04:06:46
Message-ID: 000601d18714$ea4aee90$bee0cbb0$@pcorp.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2016-03-26 04:24:52 Re: Combining Aggregates
Previous Message Amit Kapila 2016-03-26 04:05:54 Re: Speed up Clog Access by increasing CLOG buffers