From: | Ben Primrose <bprimrose(at)tracelink(dot)com> |
---|---|
To: | Daevor The Devoted <dollien(at)gmail(dot)com> |
Cc: | hendrik(dot)uggla(at)kristianstad(dot)se, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SV: Refreshing materialized views |
Date: | 2017-11-29 11:42:56 |
Message-ID: | CA+OR6cTj8d4g+LLATg2QAGBjXTmF_w__tpGpJNKuFsA4_E3m7w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 <dollien(at)gmail(dot)com>
wrote:
>
>
> 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
>
--
*Ben Primrose | Postgres DBA | TraceLink Inc.*400 Riverpark Dr.
Floor 2, Suite 200
North Reading, MA 01864
o: +1.978.396.6507
e: bprimrose(at)tracelink(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Swapnil Vaze | 2017-11-29 12:07:11 | Re: vacuumdb fails with error pg_statistic_relid_att_inh_index constraint violation after upgrade to 9.6 |
Previous Message | legrand legrand | 2017-11-29 10:31:24 | Re: seq vs index scan in join query |