Re: Hmm, nodeUnique doesn't really support backwards scan too well - Mailing list pgsql-bugs

From Gregory Stark
Subject Re: Hmm, nodeUnique doesn't really support backwards scan too well
Date
Msg-id 87y73aj63y.fsf@oxford.xeocode.com
Whole thread Raw
In response to Re: Hmm, nodeUnique doesn't really support backwards scan too well  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Hmm, nodeUnique doesn't really support backwards scan too well
List pgsql-bugs
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.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!

pgsql-bugs by date:

Previous
From: "Blanco, Jose"
Date:
Subject: Oder by not working
Next
From: Tom Lane
Date:
Subject: Re: Hmm, nodeUnique doesn't really support backwards scan too well