Re: [BUGS] 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@fabsoftware.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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)