Re: plpgsql.consistent_into

On 1/13/14, 6:36 PM, Florian Pflug wrote:
> On Jan14, 2014, at 01:20 , Jim Nasby<jim(at)nasby(dot)net> wrote:
>> >On 1/13/14, 5:57 PM, Josh Berkus wrote:
>>> >>On 01/13/2014 03:41 PM, Florian Pflug wrote:
>>>> >>>It therefor isn't an oversight that SELECT ... INTO allows multiple result rows
>>>> >>>but INSERT/UPDATE/DELETE forbids them, it's been done that way on purpose and
>>>> >>>for a reason. We shouldn't be second-guessing ourselves by changing that later -
>>>> >>>not, at least, unless we have a*very* good reason for it. Which, AFAICS, we don't.
>>>> >>>
>>>> >>>(And yeah, personally I'd prefer if we'd complain about multiple rows. But it's
>>>> >>>IMHO just too late for that)
>>> >>
>>> >>I*really* don't want to go through all my old code to find places where
>>> >>I used SELECT ... INTO just to pop off the first row, and ignored the
>>> >>rest. I doubt anyone else does, either.
>> >
>> >Do you regularly have use cases where you actually want just one RANDOM row?
>> >I suspect the far more likely scenario is that people write code assuming they'll
>> >get only one row and they'll end up with extremely hard to trace bugs if that
>> >assumption is ever wrong.
> One case that immediatly comes to mind is a JOIN which sometimes returns
> multiple rows, and a projection clause that only uses one of the tables
> involved in the join.

Which is just bad coding IMHO.

> Another are queries including an ORDER BY - I don't think the patch makes an
> exception for those, and even if it did, it probably wouldn't catch all
> cases, like e.g. an SRF which returns the rows in a deterministic order.

Sure, but if you've gone to the trouble of putting the ORDER BY in, how hard is it to add LIMIT 1?

> Or maybe you're picking a row to process next, and don't really care about
> the order in which you work through them.

Again, how hard is LIMIT 1?

BTW, my issue here isn't with typing out " STRICT". I'd be fine if the way things worked was you had to specify INTO STRICT or INTO LOOSE (or whatever you want to call the opposite of strict).

My problem is that the default here is plain and simple a bad choice for default behavior. In light of Tom's research showing this was added in 8.2 I understand why we went that route, but I'd really like a way to change the default. Or even disallow it (IE: force the user to state which route they're going here).
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net