Re: [HACKERS] BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1

On Mon, Jan 08, 2007 at 10:27:15AM -0500, Tom Lane wrote:
> "Jonathan Hull" <jono(at)fabsoftware(dot)com> writes:
> > The key feature for the error is that when a result structure (eg : pg_foo)
> > is defined with a domain type that is not null, only PG 8.2 errors if the
> > result is an empty set.
>
> The problem is explained well enough by this comment in plpgsql's code
> for FOR-over-query:
>
> /*
> * If the query didn't return any rows, set the target to NULL and return
> * with FOUND = false.
> */
>
> At the time this code was written, there weren't any potential negative
> side-effects of trying to set a row value to all NULLs, but now it's
> possible that that fails because of domain constraints.
>
> I think the idea was to ensure that a record variable would have the
> correct structure (matching the query output) post-loop, even if the
> query produced zero rows. But it's not clear that that is really
> useful for anything, given plpgsql's dearth of introspection facilities.
> So we could make Jonathan's problem go away if we just take out the
> assignment of nulls, and say that FOR over no rows leaves the record
> variable unchanged. The documentation doesn't specify the current
> behavior.
>
> Looking through the code, I see another place that does the same thing:
> FETCH from a cursor, when the cursor has no more rows to return. It's
> a bit harder to argue that it's sane to leave the variable unchanged
> in this case. However, the documentation doesn't actually promise that
> the target gets set to null in this case either.

I think it would be safer to set the record variable to something that
wipes any old data. I can't think of any examples of good code that
would fall prey to this, but I can certainly think of some nasty bugs
that users could inadvertently create. I know I'd personally like to
have the safety net...

Perhaps a means to mark the record as being null, other than setting all
the fields to null? That might also allow for a means for users to set
records to null, which I think would be useful in some cases.

BTW, are row variables also affected by this bug or is it just record
variables?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)