Re: record identical operator

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> Now I admit that's an arguable point. We could certainly define an
> intermediate notion of equality that is more equal than whatever =
> does, but not as equal as exact binary equality.

I suggested it up-thread and don't recall seeing a response, so here it
is again- passing the data through the binary-out function for the type
and comparing *that* would allow us to change the interal binary
representation of data types and would be something which we could at
least explain to users as to why X isn't the same as Y according to this
binary operator.

> I think the conservative (and therefore correct) approach is to decide
> that we're always going to update if we detect any difference at all.

And there may be users who are surprised that a refresh changed parts of
the table that have nothing to do with what was changed in the
underlying relation, because a different plan was used and the result
ended up being binary-different. It's easy to explain to users why that
would be when we're doing a wholesale replace but I don't think it'll be
nearly as clear why that happened when we're not replacing the whole
table and why REFRESH can basically end up changing anything (but
doesn't consistently). If we're paying attention to the records changed
and only updating the matview's records when they're involved, that
becomes pretty clear. What's happening here feels very much like
unintended consequences.

> It is obviously true, and unavoidable, that if the query can produce
> more than one result set depending on the query plan or other factors,
> then the materialized view contents can match only one of those
> possible result sets. But you are arguing that it should be allowed
> to produce some OTHER result set, that a direct execution of the query
> could *never* have produced, and I can't see how that can be right.

I agree that the matview shouldn't produce things which *can't* exist
through an execution of the query. I don't intend to argue against that
but I realize that's a fallout of not accepting the patch to implement
the binary comparison operators. My complaint is more generally that if
this approach needs such then there's going to be problems down the
road. No, I can't predict exactly what they are and perhaps I'm all wet
here, but this kind of binary-equality operations are something I've
tried to avoid since discovering what happens when you try to compare
a couple of floating point numbers.

Thanks,

Stephen