ERROR: could not find relation 596922701 among query result relations

I wouldn’t normally post about this, but since there seems to be zero information on this error message, I figured I might as well put in a little information on the topic for future generations. Yesterday I was working on a function when I hit the above error; it looked something like this:

dev20100=# SELECT _global_tolerance_exception(20324,true);

ERROR:  could not find relation 597322790 among query result relations

CONTEXT:  SQL function "get_family" statement 1

SQL statement "INSERT INTO global_tolerance_exception (entity_id) SELECT get_family( $1 )"

PL/pgSQL function "_global_tolerance_exception" line 10 at SQL statement

My initial thought was some type of relation caching issue, but after a quick rebuild of the tables/functions involved, and actually reading the error message a few more times I dismissed this idea, and started leaning toward either a syntax error or a bug. Since you could run that statement from the command line:

INSERT INTO global_tolerance_exception (entity_id) SELECT get_family(20324);

without getting any errors, I then started leaning toward it being a bug, something around it not understanding how to properly handle the results of the get_family query. Oh, for reference, here is the defintion of get_family, and the defintion of _global_tolerance_exception:

CREATE OR REPLACE FUNCTION get_family(integer) RETURNS SETOF integer AS '

    SELECT c.entity_id FROM entity c, entity p WHERE c.lft >= p.lft AND c.lft <= p.rgt AND p.entity_id = $1;

' LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION _global_tolerance_exception(parent integer, ctrl bool) RETURNS boolean AS $$

DECLARE

BEGIN

	IF ctrl THEN

		INSERT INTO global_tolerance_exception (entity_id) SELECT get_family(parent);

	ELSE

		DELETE FROM global_tolerance_exception WHERE entity_id IN (SELECT get_family(parent));

	END IF;

	RETURN TRUE;

END

$$ LANGUAGE 'plpgsql';

You might recognize get_family as a classic nested set function, we’ve been using it without issue since at least 7.3, and I think 7.2 or maybe even 7.1. So I went to #irc, where I think the most telling response was “I have never seen that error before”. Yeah, (google|pgsql.ru) really hadn’t either. The only thing was a pointer to the source code; look in src/backend/commands/trigger.c and you will find the following comments:

if (nr <= 0)    /* should not happen */

                        elog(ERROR, "could not find relation %u among query result relations",

                             event->ate_relid);

It can’t be a good sign to trigger an error that has a code comment saying “should not happen”! Well, after kicking it around a bit, I never really did figure out what the problem was, but I did figure out the fix; I changed the _global_tolerance_exception function like so:

CREATE OR REPLACE FUNCTION _global_tolerance_exception(parent integer, ctrl bool) RETURNS boolean AS $$

DECLARE

BEGIN

	IF ctrl THEN

		INSERT INTO global_tolerance_exception (entity_id) SELECT * FROM get_family(parent);

	ELSE

		DELETE FROM global_tolerance_exception WHERE entity_id IN (SELECT get_family(parent));

	END IF;

	RETURN TRUE;

END

$$ LANGUAGE 'plpgsql';

Apparently it makes a difference whether you call the function using select get_family() or select * from get_family(). I don’t know why, it doesn’t matter on the command line, only in the function. More perplexing is that, while I can easily reproduce this error, I couldn’t make a reproducible test case… I have two theories on this… the first being that maybe there is some gunk in the works of the get_family function within the system tables having come from a 7.3 system originally… I dont think so, since it’s been dumped and restored several times since then, but maybe. Theory number two is that I originally got the error by calling the _global_tolerance_exception from another function that was called via a trigger. Perhaps something in the function got chaced in the server when called via trigger which caused the function being standalone to generate the error? Someone would need to go through and setup all the tables and triggers to proove that theory… in the meantime I’m moving on, but maybe if someone else ever gets this error, maybe this will help.