Re: Hmm, nodeUnique doesn't really support backwards scan too well

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> Hm, that has the nasty side effect that someone who uses SCROLL but doesn't
>> fetch backwards much or at all suddenly gets a much more expensive plan than
>> if they didn't.
>
> Well, what are they using SCROLL for if they don't need it?
>
> A more plausible objection is that previously, (some) cursors using
> SELECT DISTINCT would support backwards fetch even if you hadn't said
> SCROLL. The bug I'm concerned about is only manifest with SELECT
> DISTINCT ON, so someone might well be happily using DISTINCT in a way
> that is affected. So there might be apps out there that are working
> today and will stop working after this change.

I must be missing something. Couldn't we just make the paths non-reversible if
there's a DISTINCT ON?

> But they are very clearly breaking the rules so I don't have a huge amount
> of sympathy for them. If we were to take this argument seriously, it would
> mean that we'd have to not only complicate ExecUnique but back-patch the
> result clear back to 7.4. I'm not even sure how to fix it (the nasty case is
> changing directions partway through the scan); let alone how to fix it in a
> way that's obviously enough right to make me feel comfortable in
> back-patching.

It seems like the obvious fix is to just reverse the behaviour -- keep reading
backwards until you see the level break then return the previous record from a
second slot.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!