Re: Proposal: PL/PgSQL strict_mode

A few thoughts about this.

> On 14 September 2013 at 05:28 Marko Tiikkaja <marko@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.5020802@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