Re: Wrong rows selected with view

Bill Moseley <moseley(at)hank(dot)org> writes:
> [ strange behavior ]

Oh, duh, it's not a PG bug: the problem is that the view is
underspecified. You have

SELECT DISTINCT ON (class.id)
... a bunch of stuff ...
FROM ... a bunch of tables ...
ORDER BY class.id;

The difficulty with this is that DISTINCT ON will take the first row in
each group with the same class.id. And since you're only sorting by
class.id, "the first row" is ill-defined. I'm not sure why qsort's
behavior seems to depend on the width of the rows, but there's no doubt
that it's sorting different rows to the front of each group depending
on which view you use.

To get stable results from this view, what you need to do is add enough
ORDER BY conditions to make sure you are getting a consistent "first
row" in each group. Adding the primary keys of each of the tables would
be enough, though it might be overkill.

It could also be that you don't want to be using DISTINCT ON at all;
have you thought through exactly what this view ought to produce for
each class.id?

regards, tom lane