Re: Preserving data after updates - Mailing list 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?