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
> 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
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
In response to
pgsql-hackers by date
|Next:||From: Jim C. Nasby||Date: 2007-01-09 11:41:27|
|Subject: Re: ideas for auto-processing patches|
|Previous:||From: Jim C. Nasby||Date: 2007-01-09 11:11:57|
|Subject: Re: 8.3 pending patch queue|
pgsql-bugs by date
|Next:||From: Tom Lane||Date: 2007-01-09 14:56:23|
|Subject: Re: [HACKERS] BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1 |
|Previous:||From: (GalaxyMaster)||Date: 2007-01-09 10:45:02|
|Subject: Re: BUG #2870: incorrect man page for postgres|