Re: Proposal: PL/PgSQL strict_mode - Mailing list pgsql-hackers

From chris travers
Subject Re: Proposal: PL/PgSQL strict_mode
Date
Msg-id 200006361.174862.1379134432266.open-xchange@email.1and1.co.uk
Whole thread Raw
In response to Proposal: PL/PgSQL strict_mode  (Marko Tiikkaja <marko@joh.to>)
Responses Re: Proposal: PL/PgSQL strict_mode
Re: Proposal: PL/PgSQL strict_mode
Re: Proposal: PL/PgSQL strict_mode
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Marko Tiikkaja
Date:
Subject: Re: Proposal: PL/PgSQL strict_mode
Next
From: Pavel Stehule
Date:
Subject: Re: Proposal: PL/PgSQL strict_mode