Anders Steinlein writes:
> Am I right in thinking that we should actually go over (i.e. re-create) all
> functions and views defined before this dump/restore where we're using JOIN
> ... USING (citext_column)? We most definitely have many more such cases,
> since this is the common (perhaps
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 x1.email::citext == x2.email::citext instead of
Anders Steinlein writes:
> On Thu, Jul 2, 2020 at 5:43 PM Tom Lane wrote:
>> Unfortunately,
>> since the reverse-listing of this join is just going to say "USING
>> (email)", there's no way to detect from human-readable output that the
>> interpretation of the USING clauses is different. (We've
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 frustrating because of likely
> >> inconsistencies
On Thu, Jul 2, 2020 at 8:44 AM Tom Lane wrote:
> A plausible explanation for how things got that way is that citext's
> equality operator wasn't in your search_path when you created the original
> matview, but it is in view when you make the new one, allowing that
> equality operator to capture
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 frustrating because of likely
>> inconsistencies in node "location" fields; but any other difference
>> is
On Thu, Jul 2, 2020 at 8:06 AM Anders Steinlein wrote:
> On Thu, Jul 2, 2020 at 3:55 PM David G. Johnston <
> david.g.johns...@gmail.com> 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
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 before
>> (could be customers who
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'm having a hard time thinking that's
On Thursday, July 2, 2020, Anders Steinlein wrote:
>
>
>> Thanks for the tip, but I'm having a hard time thinking that's the case,
>> seeing as I'm unable to trigger the wrong result no matter how hard I try
>> with a new definition/manual query. I've introduced random ordering to the
>> first
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'm having a hard time thinking that's the case,
> seeing as I'm unable to trigger the wrong result
Hi Michael,
> On 02. Jul, 2020, at 14:55, Michael Lewis wrote:
>
> Is now() computed at the time the view is defined and not at refresh? If this
> were a function, I would be more suspicious of that but a plain view, surely
> not. I hope.
now() returns the time at the beginning of the
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
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 without time zone and b) the session time zone of the user
Is now() computed at the time the view is defined and not at refresh? If
this were a function, I would be more suspicious of that but a plain view,
surely not. I hope.
>
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 inspect the query and not
>> finding
>> >
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 inspect the query and not
>> finding
>> >
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 the matview manually, or
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 the matview manually, or
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 the matview manually, or
> creating a new (identical) materialized view returns the
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.
21 matches
Mail list logo