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

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jonathan Hull <jono(at)fabsoftware(dot)com>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1
Date: 2007-01-09 11:36:04
Message-ID: 20070109113603.GH12217@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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)

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2007-01-09 14:56:23 Re: [HACKERS] BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1
Previous Message (GalaxyMaster) 2007-01-09 10:45:02 Re: BUG #2870: incorrect man page for postgres

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim C. Nasby 2007-01-09 11:41:27 Re: ideas for auto-processing patches
Previous Message Jim C. Nasby 2007-01-09 11:11:57 Re: 8.3 pending patch queue