Re: SV: Refreshing materialized views - Mailing list pgsql-general

From Ben Primrose
Subject Re: SV: Refreshing materialized views
Date
Msg-id CA+OR6cTj8d4g+LLATg2QAGBjXTmF_w__tpGpJNKuFsA4_E3m7w@mail.gmail.com
Whole thread Raw
In response to Re: SV: Refreshing materialized views  (Daevor The Devoted <dollien@gmail.com>)
Responses SV: SV: Refreshing materialized views
List pgsql-general
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 wrote: > > > On 28 Nov 2017 5:18 pm, "Tom Lane" wrote: > > Henrik Uggla 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: bprimrose@tracelink.com

pgsql-general by date:

Previous
From: legrand legrand
Date:
Subject: Re: seq vs index scan in join query
Next
From: Swapnil Vaze
Date:
Subject: Re: vacuumdb fails with error pg_statistic_relid_att_inh_indexconstraint violation after upgrade to 9.6