Re: BUG #12465: Materialized view dump restoration issue

On 2015-01-09 22:38, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> On 2015-01-09 21:42, Tom Lane wrote:
>>> This is not a pg_dump bug, this is a broken definition of function a().
>>> That function will fail in any context where the caller changes
>>> search_path, not only pg_dump. You can perhaps get away without that
>>> in a single-schema database, but not with multiple schemas.
>
>> AFAIK there isn't a way to write inlineable SQL functions in relocatable
>> extensions in that way, since you don't know which schema they end up
>> installed in. The original test case comes from PostGIS.
>
> You can do it for relocatable-at-install-time extensions, as suggested in
> the manual:
>
> CREATE FUNCTION ... SET search_path = @extschema@ ...

Yup, and now it's not inlineable anymore.

>> But I think the bigger problem is that naively thinking it shouldn't be
>> this easy to create unrestorable databases. But perhaps I'm being
>> overly naive.
>
> Well, if you know how to inform pg_dump what random assumptions about
> search_path exist in the functions invoked by a matview (or expression
> index, or some other cases), let me know.

Hmm.. So I guess this is pretty much impossible as long as we insist on
the materialized views being initialized at restore time. (Though I
notice that even WITH NO DATA does not work.)

.marko