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