From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: plpgsql.consistent_into |
Date: | 2014-01-12 13:02:06 |
Message-ID: | 52D2924E.7060100@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 1/12/14, 7:47 AM, Pavel Stehule wrote:
> 2014/1/12 Marko Tiikkaja <marko(at)joh(dot)to>
>
>> Greetings fellow elephants,
>>
>> I would humbly like to submit for your consideration my proposal for
>> alleviating pain caused by one of the most annoying footguns in PL/PgSQL:
>> the behaviour of SELECT .. INTO when the query returns more than one row.
>> Some of you might know that no exception is raised in this case (as
>> opposed to INSERT/UPDATE/DELETE .. INTO, all of them yielding
>> TOO_MANY_ROWS), which can hide subtle bugs in queries if during testing the
>> query always returns only one row or the "correct" one happens to be picked
>> up every time. Additionally, the row_count() after execution is always
>> going to be either 0 or 1, so even if you want to explicitly guard against
>> potentially broken queries, you can't do so!
>>
>
> It is not bad and, sure, - it is very useful and important
>
> but - it is a redundant to INTO STRICT clause. When you use it, then you
> change a INTO behaviour. Is not better to ensure STRICT option than hidden
> redefining INTO?
That only works if the query should never return 0 rows either. If you
want to allow for missing rows, STRICT is out of the question.
> Option INTO (without STRICT clause) is not safe and we should to disallow.
> I see a three states (not only two)
>
> a) disallow INTO without STRICT (as preferred for new code)
> b) implicit check after every INTO without STRICT
> c) without check
>
> these modes should be: "strict_required", "strict_default", "strict_legacy"
I can't get excited about this. Mostly because it doesn't solve the
problem I'm having.
It is important to be able to execute queries with INTO which might not
return a row. That's what FOUND is for.
>> So I added the following compile-time option:
>>
>>
>> set plpgsql.consistent_into to true;
>>
>
> This name is not best (there is not clean with it a into should be
> consistent)
I agree, but I had to pick something. One of the three hard problems in
CS..
> Is question, if this functionality should be enabled by GUC to be used for
> legacy code (as protection against some kind of hidden bugs)
>
> This topic is interesting idea for me - some checks can be pushed to
> plpgsql_check (as errors or warnings) too.
>
> Generally I like proposed functionality, just I am not sure, so hidden
> redefining INTO clause (to INTO STRICT) is what we want. We can do it (but
> explicitly). I don't know any situation where INTO without STRICT is valid.
> Introduction of STRICT option was wrong idea - and now is not way to back.
Note that this is different from implicitly STRICTifying every INTO,
like I said above.
Regards,
Marko Tiikkaja
From | Date | Subject | |
---|---|---|---|
Next Message | Florian Pflug | 2014-01-12 15:20:00 | Re: Why does numeric_out produce so many trailing zeros? |
Previous Message | Pavel Stehule | 2014-01-12 12:16:15 | Fwd: patch: make_timestamp function |