Обсуждение: "disappearing" rows in temp table, in recursing trigger

Hello, I'm guessing the rows aren't really disappearing but how else
to describe it?....

 I have a trigger function that calls another function that is
recursive.  The recursive function creates a temp table and inserts
rows into it.  After the recursive function returns, the trigger
function examines the temp table in order to validate the data in it.
This all works perfectly well when the trigger function is written
slightly modified as a regular function.  However when run as a
trigger, the temp table comes back empty.  No errors are thrown.  I
have version 8.1.10.  I've tried to include the relevant parts below.
Any smarty out there see the problem?  --Eric

--=========
  --The trigger is like this:
   CREATE TRIGGER trigger_name AFTER INSERT OR UPDATE ON table_name
FOR EACH ROW EXECUTE PROCEDURE trigger_func()

--========
--The trigger_func() (abbreviated):
begin
   perform recursive_func(new.id, 1, new.id);
   l_row_count := count(*) from tmp_ancestors;
   raise debug 'total rows=%', l_row_count; --LOG OUTPUT SAYS: "total rows=0"
   ....
   return new;
end;

========
--The recursive_func():
begin
      ....
      insert into tmp_ancestors (blah, blah)...
      if logic then
         new_level := p_level + 1;
         perform recursive_func(id, new_level, id);
      end if;
   l_row_count := count(*) from tmp_ancestors;
   raise debug 'returning p_level=%; rows in tmp_ancestors=%',
p_level, l_row_count;
   --LOG OUTPUT SHOWS EXPECTED INCREMENTING NUMBERS
   return;
end

Eric Worden wrote:

> The recursive function creates a temp table [...]  I
> have version 8.1.10.

While I haven't looked in detail, I'd be surprised if this wasn't an
issue with pre-8.3 versions lack of any way to automatically re-generate
cached plans in functions.

Try running your code on an 8.3 installation and see if you still get
the issue. I strongly suspect you won't.

If this does prove to be the case, there are workarounds for pre-8.3
versions, usually involving the use of  EXECUTE to bypass the cache and
force re-planning of statements at every execution. However, if it's
practical to do so upgrading to 8.3 might be a nicer and (in the long
run) easier option.

--
Craig Ringer

Thank you this helped me fix it.  I moved the "create temporary
table..." statement to the outermost calling function and changed it
from STABLE to VOLATILE.  I'm not sure which change made the
difference -- I had to move the create table statement for an
additional reason.



On Sat, Dec 27, 2008 at 1:14 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Eric Worden wrote:
>
>> The recursive function creates a temp table [...]  I
>> have version 8.1.10.
>
> While I haven't looked in detail, I'd be surprised if this wasn't an
> issue with pre-8.3 versions lack of any way to automatically re-generate
> cached plans in functions.
>
> Try running your code on an 8.3 installation and see if you still get
> the issue. I strongly suspect you won't.
>
> If this does prove to be the case, there are workarounds for pre-8.3
> versions, usually involving the use of  EXECUTE to bypass the cache and
> force re-planning of statements at every execution. However, if it's
> practical to do so upgrading to 8.3 might be a nicer and (in the long
> run) easier option.
>
> --
> Craig Ringer
>

"Eric Worden" <worden.eric@gmail.com> writes:
> Thank you this helped me fix it.  I moved the "create temporary
> table..." statement to the outermost calling function and changed it
> from STABLE to VOLATILE.  I'm not sure which change made the
> difference -- I had to move the create table statement for an
> additional reason.

Probably the latter.  STABLE functions generally use the snapshot of the
calling query, which means that on successive calls they'll see the same
state of the database.

            regards, tom lane