A few thoughts about this. > On 14 September 2013 at 05:28 Marko Tiikkaja <ma...@joh.to> wrote: > > > Hi, > > After my previous suggestion for adding a STRICT keyword got shot > down[1], I've been thinking about an idea Andrew Gierth tossed out: > adding a new "strict mode" into PL/PgSQL. In this mode, any query which > processes more than one row will raise an exception. This is a bit > similar to specifying INTO STRICT .. for every statement, except > processing no rows does not trigger the exception. The need for this > mode comes from a few observations I make almost every day: > 1) The majority of statements only deal with exactly 0 or 1 rows. > 2) Checking row_count for a statement is ugly and cumbersome, so > often it just isn't checked. I often use RETURNING TRUE INTO STRICT _OK > for DML, but that a) requires an extra variable, and b) isn't possible > if 0 rows affected is not an error in the application logic. > 3) SELECT .. INTO only fetches one row and ignores the rest. Even > row_count is always set to 0 or 1, so there's no way to fetch a value > *and* to check that there would not have been more rows. This creates > bugs which make your queries return wrong results and which could go > undetected for a long time.
I am going to suggest that STRICT is semantically pretty far from what is meant here in common speech. I think STRICT here would be confusing. This would be really pretty severe for people coming from Perl or MySQL backgrounds. May I suggest SINGLE as a key word instead? It might be worth having attached to a INSERT, UPDATE, and DELETE statements. I am thinking something like: DELETE SINGLE FROM foo WHERE f1 < 1000; would be more clearer. Similarly one could have: INSERT SINGLE INTO foo SELECT * from foo2; and UPDATE SINGLE foo You could even use SELECT SINGLE but not sure where the use case is there where unique indexes are not sufficient. > > Attached is a proof-of-concept patch (no docs, probably some actual code > problems too) to implement this as a compile option: > > =# create or replace function footest() returns void as $$ > $# #strict_mode strict > $# begin > $# -- not allowed to delete more than one row > $# delete from foo where f1 < 100; > $# end$$ language plpgsql; > CREATE FUNCTION > =# select footest(); > ERROR: query processed more than one row > CONTEXT: PL/pgSQL function footest() line 5 at SQL statement > > Now while I think this is a step into the right direction, I do have a > couple of problems with this patch: > 1) I'm not sure what would be the correct behaviour with EXECUTE. > I'm tempted to just leave EXECUTE alone, as it has slightly different > rules anyway. > 2) If you're running in strict mode and you want to > insert/update/delete more than one row, things get a bit uglier; a wCTE > would work for some cases. If strict mode doesn't affect EXECUTE (see > point 1 above), that could work too. Or maybe there could be a new > command which runs a query, discards the results and ignores the number > of rows processed. Yeah, I am worried about this one. I am concerned that if you can't disable on a statement by statement basis, then you have a problem where you end up removing the mode from the function and then it becomes a lot harder for everyone maintaining the function to have a clear picture of what is going on. I am further worried that hacked ugly code ways around it will introduce plenty of other maintenance pain points that will be worse than what you are solving. > > I'll be adding this to the open commitfest in hopes of getting some > feedback on this idea (I'm prepared to hear a lot of "you're crazy!"), > but feel free to comment away any time you please. Well, I don't know if my feedback above is helpful, but there it is ;-) > > > Regards, > Marko Tiikkaja > > [1]: http://www.postgresql.org/message-id/510bf731.5020...@gmx.net > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers Best Wishes, Chris Travers http://www.2ndquadrant.com PostgreSQL Services, Training, and Support
