Skip site navigation (1) Skip section navigation (2)

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: (view raw or whole thread)
Lists: pgsql-bugspgsql-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
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      512.569.9461 (cell)

In response to


pgsql-hackers by date

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

pgsql-bugs by date

Next:From: Tom LaneDate: 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

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group