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?

Поиск
Список
Период
Сортировка
От Regina Obe
Тема 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?
Дата
Msg-id 000601d18714$ea4aee90$bee0cbb0$@pcorp.us
обсуждение исходный текст
Ответы Re: 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?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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





В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: Speed up Clog Access by increasing CLOG buffers
Следующее
От: David Rowley
Дата:
Сообщение: Re: Combining Aggregates