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: 20070109113603.GH12217@nasby.net (view raw or flat)
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
variables?
-- 
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In response to

Responses

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-2014 The PostgreSQL Global Development Group