Re: Preserving data after updates
От | Berend Tober |
---|---|
Тема | Re: Preserving data after updates |
Дата | |
Msg-id | 428D4A0F.8010105@seaworthysys.com обсуждение исходный текст |
Ответ на | Re: Preserving data after updates (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Inherited constraints and search paths (was Re: Preserving data after updates)
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
Tom Lane wrote: > What do you get from > > select conname, consrc from pg_catalog.pg_constraint > where contype = 'c' and conrelid = 'person'::regclass; > > > conname | consrc ---------------------------+--------------------------------------------------------------------------------------------------------- person_e_mail_address | public.check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_social_security_no | public.check_pattern(social_security_no, 'Social Security Number'::character varying) (3 rows) > select conname, consrc from pg_catalog.pg_constraint > where contype = 'c' and conrelid = 'person_change_history'::regclass; > > conname | consrc ---------------------------+-------------------------------------------------------------------------------------------------- person_social_security_no | check_pattern(social_security_no, 'Social Security Number'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_e_mail_address | check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) (3 rows) > AFAICS from looking at the 7.3 pg_dump source, it should suppress any > constraint on person_change_history that looks identical to one of the > parent table's constraints in this query. > > Interesting. The consrc column values differ in that the explicit schema qualification on the function calls is missing for the descendent table. So, you think maybe if I remove the explicit schema qualification from the function calls in the constraint declarations on the person table that that might fix it? Yup! That does it! Thanks for your help! But now, however, when restoring from the pg_dump output the script gets hung up over the fact that when the CREATE TABLE statements are executed the raw script can't find the check_pattern function, since it is declared in the public schema and these application-specific tables are (being tried to be) declared in a different schema. That is, the pg_dump output has lots of SET search_path = public, pg_catalog; and SET search_path = paid, pg_catalog; statements sprinkled throughout, and when a table is declared having the check_pattern function call constraint after the latter statement, then the function can't be found. I had to manually edit the pg_dump output script search path statements to read SET search_path = paid, public, pg_catalog; in order to make this all work right. Again, too much manual editing to tolerate for disaster recovery and for my frequent refresh of DEV and QAT from PRD for development and testing purposes. Now what, oh most wise one?
В списке pgsql-general по дате отправления: