Re: record identical operator - Review

Steve,

Thanks for providing a summary.

* Steve Singer (steve(at)ssinger(dot)info) wrote:
> The arguments for this patch are
> * We want the materialized view to return the same value as would be
> returned if the query were executed directly. This not only means
> that the values should be the same according to a datatypes =
> operator but that they should appear the same 'to the eyeball'.

With the cases where the equality operator doesn't match the 'to the
eyeball' appearance, this really seems to be a pipe dream to me, unless
we *also* define an ordering or similar which would then change the
existing semantics for end users (which might be reasonable), but I'm
not sure how we could do that without input from the type- iow, I don't
think we can say "well, we'll order by byte representation". It's also
going to possibly be a performance hit since we're going to have to do
both an equality op call during a HashAgg/HashJoin/whatever and have to
do a "which one is bigger of these two equal things", and then I wonder
if we'd need to allow users to somehow specify "I don't want the bigger
of the equal things, I want the smaller, in this query for this equality
check." I'd really like to see how we're going to provide for that when
the user is doing a GROUP BY without breaking something else or causing
problems with later SQL spec revisions.

> * Supporting the materialized view refresh check via SQL makes a lot
> of sense but doing that requires exposing something via SQL

... which we don't currently expose for the queries that we already
support users running today. Users seem to generally be accepting of
that too, even though what they end up with in the output isn't
necessairly consistent from query to query. The issue here is that
we're trying to make the mat view look like what the query would do when
run at the same time, which is a bit of a losing battle, imv.

> * If we adequately document what we mean by record_image_identical
> and the operator we pick for this then users shouldn't be surprised
> at what they get if they use this

That's a bit over-simplistic, really. We do try to keep to the POLA
(principle of least astonishment) and that's not going to be easy here.

> I think there is agreement that better (as in more obscure)
> operators than === and !== need to be picked and we need to find a
> place in the user-docs to warn users of the behaviour of this
> operators. Hannu has proposed

I'm a bit on the fence about this, after having discussed my concerns
with Robert at PostgresOpen. If we're going to expose and support
these at the SQL level, and we can figure out some semantics and
consistency for using them that isn't totally baffling, then perhaps
having them as simple and clear operator names would be reasonable. One
concern here is if the SQL spec might decide some day that '===' is a
useful operator for something else (perhaps even "they look the same
when cast to text").

> *==* "binary equal, surely very equal by any other definition as wall"
> !==? "maybe not equal" -- "binary inequal, may still be equal by
> other comparison methods"

Those look alright to me also though, but we'd need to work out the
other operation names, right? And then figure out if we want to use
those other operators (less-than, greater-than, etc) when doing equality
operations to figure out which equal value to return to the user.

Thanks,

Stephen