Re: BUG #13870: couldn't restore dump with mat view - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #13870: couldn't restore dump with mat view
Date
Msg-id 28815.1452958022@sss.pgh.pa.us
Whole thread Raw
In response to Re: BUG #13870: couldn't restore dump with mat view  (Michael Paquier <michael.paquier@gmail.com>)
Responses Re: BUG #13870: couldn't restore dump with mat view
List pgsql-bugs
Michael Paquier <michael.paquier@gmail.com> writes:
> On Sat, Jan 16, 2016 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, this example doesn't work as-is because the command order is already
>> wrong.  But the issue is that CREATE MATERIALIZED VIEW ... WITH NO DATA
>> tries to plan and execute the query, not just store it, which entirely
>> defeats pg_dump's attempt to avoid hidden dependencies.  We need a less
>> chintzy solution to handling this variant of CREATE MATERIALIZED VIEW.
>>
>> (I seem to recall complaining about this before.)

> The first issue in the dump is the creation of point(), which depends
> on relation stend.

point() is just fine, because check_relation_bodies is turned off.
pg_dump does that specifically because it can't see into the function
body to know what dependencies might be there.

The reason for separating dump/reload of matviews into two steps,
CREATE ... WITH NO DATA followed by REFRESH, is again to guard against
hidden dependencies.  If the view were planned/executed only at the
REFRESH stage, this dump script would be fine as-is.  It's the attempt
to avoid having two code paths in ExecCreateTableAs() that is causing
this failure.

(Having said that, it would still be possible to break it I think,
though much harder.  Hiding relation dependencies inside functions
is not a good design technique.)

            regards, tom lane

pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: BUG #13863: Select from views gives wrong results
Next
From: Dean Rasheed
Date:
Subject: Re: BUG #13863: Select from views gives wrong results