It may be simpler to just run the query from the materialized view definition as the user that you want to refresh the mv.
On Tue, Nov 28, 2017 at 10:30 PM, Daevor The Devoted <doll...@gmail.com> wrote: > > > On 28 Nov 2017 5:18 pm, "Tom Lane" <t...@sss.pgh.pa.us> wrote: > > Henrik Uggla <henrik.ug...@kristianstad.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 > -- *Ben Primrose | Postgres DBA | TraceLink Inc.*400 Riverpark Dr. Floor 2, Suite 200 North Reading, MA 01864 o: +1.978.396.6507 e: bprimr...@tracelink.com
