Re: SV: Refreshing materialized views

On 28 Nov 2017 5:18 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Henrik Uggla <Henrik(dot)Uggla(at)kristianstad(dot)se> writes:
> The underlying tables are foreign tables. The user has been mapped to a
foreign user with select permission. I have no problem selecting from the
foreign tables or the materialized views.

[ shrug... ] WFM; if I can select from the foreign table then I can make
a materialized view that selects from it, and that refreshes without
complaint. Admittedly, getting things set up to select from the foreign
table is trickier than it sounds: your local user needs SELECT on the
foreign table plus a mapping to some remote userid, and *on the remote
server* that remote userid needs SELECT on whatever the foreign table
is referencing. I'm guessing you messed up one of these components.

regards, tom lane

Hendrik, perhaps an easy way to check out Tom's suggestion is to create a
very simple materialized view that selects just from one of the foreign
tables, then attempt the REFRESH. If that works, then keep adding more
tables from your original materialized view until you have found the
problem.
Basically, reduce the problem to the simplest case, and if that works, then
keep adding to it until you hit the problem. You may still not know why the
problem is happening, but you'll at least know where to focus any further
investigation.

Kind regards,
Daevor, The Devoted