Re: Inherited constraints and search paths (was Re: Preserving

Tom Lane wrote:

> Berend Tober  writes:
>  
>
>> Now what, oh most wise one?
>>   
>
>
> OK, now I finally get the point: you are creating child tables in
> different schemas than their parents live in.  This creates a problem
> because reverse-listing of the constraints varies depending on what
> the search path is.
>  
>
Close but not exactly. In my case the child tables are in the same 
schema as the parent, but it is the function call referenced in the 
check constraint that lives in a different schema than the tables. 
However, as an alternative in developing this idea, I did consider the 
possibility of defining a separate schema where all the child tables 
would live so that the child tables could have the same name as the 
parent tables, since this particular implementation is such that the 
child tables represent change histories of the parent tables.

> An example in CVS tip is:...
>
> It's the same constraint, but the different reverse-listing fools
> pg_dump into assuming that it's different.
>
> At the moment I'm not seeing any really nice way to fix this.
>  
>
If the pg_dump output produced "SET search_path" statement with the 
complete actual path required to find all objects in subsequent DDL 
statements, my world would be at peace. (But I have no idea how 
complicated it would be to implement that.)

> It can be argued that we should actually prohibit dropping inherited
> constraints, which'd eliminate that problem.  I seem to recall that this
> has come up before and we explicitly decided against making such a
> restriction ... but given that a dump/restore will cause the inherited
> constraint to come back anyway, it can hardly be claimed that we really
> support dropping them.
>
> Comments anyone?
>  
>
I like that arguement to prohibit dropping inherited constraints.