Re: Preserving data after updates

[Berend Tober <btober@xxxxxxxxxxxxxxxx>]

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?



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq