2014/1/12 Marko Tiikkaja <ma...@joh.to> > On 1/12/14, 7:47 AM, Pavel Stehule wrote: > >> 2014/1/12 Marko Tiikkaja <ma...@joh.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.
hmm - you have true. try to find better name. Other questions is using a GUC for legacy code. I am for this checked mode be default (or can be simply activated for new code) Regards Pavel > > > 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 >
