Hi folks,
We have a materialized view from which a customer reported some
confusing/invalid results, leading us to inspect the query and not finding
anything wrong. Running the query defining the matview manually, or
creating a new (identical) materialized view returns the correct result.
Obviousl
On Thu, Jul 2, 2020 at 2:02 AM Tom Lane wrote:
> Anders Steinlein writes:
> > We have a materialized view from which a customer reported some
> > confusing/invalid results, leading us to inspect the query and not
> finding
> > anything wrong. Running the query defining
On Thu, Jul 2, 2020 at 12:12 PM Magnus Hagander wrote:
>
> On Thu, Jul 2, 2020 at 2:02 AM Tom Lane wrote:
>
>> Anders Steinlein writes:
>> > We have a materialized view from which a customer reported some
>> > confusing/invalid results, leading us to ins
On Thu, Jul 2, 2020 at 1:26 PM Anders Steinlein wrote:
> On Thu, Jul 2, 2020 at 2:02 AM Tom Lane wrote:
>
>> Anders Steinlein writes:
>> > We have a materialized view from which a customer reported some
>> > confusing/invalid results, leading us to inspec
On Thu, Jul 2, 2020 at 3:01 PM Jeremy Smith wrote:
> It looks like you are using now() fairly often in that query. That would,
> of course, give different results in different transactions, but it could
> also give different results if a) the things you are comparing now() to are
> timestamp wit
On Thu, Jul 2, 2020 at 3:44 PM Tom Lane wrote:
> Anders Steinlein writes:
> > On Thu, Jul 2, 2020 at 2:02 AM Tom Lane wrote:
> >> I suspect the query underlying the matviews is less deterministic than
> >> you think it is.
>
> > Thanks for the tip, but I&
On Thu, Jul 2, 2020 at 3:55 PM David G. Johnston
wrote:
> On Thursday, July 2, 2020, Anders Steinlein wrote:
>>
>>
>> I just wanted to add that we're on Postgres 12.3. This matview has been
>> with us since 9.4 days, and we have not experienced any such issues b
On Thu, Jul 2, 2020 at 5:43 PM Tom Lane wrote:
> Anders Steinlein writes:
> >> Even that perhaps isn't conclusive, so you could
> >> also try comparing the pg_rewrite.ev_action fields for the views'
> >> ON SELECT rules. (That might be a bit frustratin
On Thu, Jul 2, 2020 at 11:44 PM Tom Lane wrote:
> Anders Steinlein writes:
> > I'm reading this correctly, would this be a "reason" to be more explicit
> > when doing joins involving non-standard data types? I.e. would it be
> > "safer" to do ON x
