From: Marko Tiikkaja <marko(at)joh(dot)to>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: plpgsql.consistent_into
Date: 2014-01-12 05:51:04
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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!

So I added the following compile-time option:

set plpgsql.consistent_into to true;

create or replace function footest() returns void as $$
x int;
-- too many rows
select 1 from foo into x;
end$$ language plpgsql;

select footest();
ERROR: query returned more than one row

It defaults to false to preserve full backwards compatibility. Also
turning it on makes the executor try and find two rows, so it might have
an effect on performance as well. The patch, as currently written, also
changes the behaviour of EXECUTE .. INTO, but I don't feel strongly
about whether that should be affected as well or not.

Marko Tiikkaja

Attachment Content-Type Size
consistent_into_v1.patch text/plain 12.4 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2014-01-12 06:47:03 Re: plpgsql.consistent_into
Previous Message Pavel Stehule 2014-01-12 05:33:41 Re: array_length(anyarray)