Re: plpgsql.consistent_into

On Jan13, 2014, at 22:49 , Jim Nasby <jim(at)nasby(dot)net> wrote:
> ISTM that in this case, it should be safe to make the new default behavior STRICT;
> if you forget to set the GUC to disable than you'll get an error that points directly
> at the problem, at which point you'll go "Oh, yeah... I forgot to set X..."

What do you mean by STRICT? STRICT (which we already support) complains if the
query doesn't return *exactly* one row. What Marko wants is to raise an error
for a plain SELECT ... INTO if more than one row is returned, but to still
convert zero rows to NULL.

> Outside of the GUC, I believe the default should definitely be STRICT. If your app is
> relying on non-strict then you need to be made aware of that. We should be able to
> provide a DO block that will change this setting for every function you've got if
> someone isn't happy with STRICT mode.

If you mean that we should change SELECT ... INTO to always behave as if STRICT
had been specified - why on earth would we want to do that? That would break
perfectly fine code for no good reason whatsoever.

In fact, after reading the documentation on SELECT ... INTO, I'm convinced the
the whole consistent_into thing is a bad idea. The documentation states clearly
that

For INSERT/UPDATE/DELETE with RETURNING, PL/pgSQL reports an error for more than
one returned row, even when STRICT is not specified. This is because there is no
option such as ORDER BY with which to determine which affected row should be
returned.

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)

best regards,
Florian Pflug