BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15720
Logged by:          Jason Madden
Email address:      jason.madden@nextthought.com
PostgreSQL version: 11.2
Operating system:   Red Hat Enterprise Linux 7.6
Description:

Version: PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit

As described in the subject, we seem to have produced a rare, intermittent
issue in a trigger when calling a procedure that modifies a declaratively
hash partitioned table which results in executing a trigger referencing the
transition table. In several weeks of fairly heavy testing of our
application and this specific code path we've only produced this error once
(during a large automated concurrent run, which succeeded on the second
attempt), so sadly I don't have clear reproduction steps. Unfortunately, by
the time this got reported to me backend log information was no longer
available so I can only report what we saw on the fronted.

Here's the error, as reported from the JDBC driver (names shortened for
clarity):

```
SQL failure calling: SELECT * FROM wip_upload_finish(...)
...
Caused by: org.postgresql.util.PSQLException: ERROR: executor could not find
named tuplestore "updated_positions"
  Where: SQL statement "UPDATE w_instances pi
    SET last_modified = NOW()
    FROM updated_positions up
    WHERE pi.object_id = up.p_instance_id"
PL/pgSQL function wip_update_AC_trigger_func() line 24 at SQL statement
SQL statement "
      DELETE FROM wip
      WHERE p_instance_id = (
        SELECT object_id
        FROM w_instances
        WHERE c1 = $1 and c2 = $2
      )
    "
```

`wip` is a declaratively hash partitioned table with ten partitions:
```
CREATE TABLE wip (
    object_id object_id_type NOT NULL DEFAULT nextval('seq_object_id'),
    p_instance_id object_id_type NOT NULL,
    sm_id object_id_type NOT NULL,
    csn TEXT,
    tags jsonb,
    CONSTRAINT wip_pkey
             PRIMARY KEY (object_id, p_instance_id),
)
  PARTITION BY HASH(p_instance_id);
```

It has four statement triggers on it, two each for INSERT and DELETE, all of
which reference a transition table:

```
CREATE TRIGGER wip_update_AC_delete_trigger
  AFTER DELETE
  ON wip
  REFERENCING OLD TABLE AS updated_positions
  FOR EACH STATEMENT
  EXECUTE PROCEDURE wip_update_AC_trigger_func();

CREATE TRIGGER wip_update_AC_insert_trigger
  AFTER INSERT
  ON wip
  REFERENCING NEW TABLE AS updated_positions
  FOR EACH STATEMENT
  EXECUTE PROCEDURE wip_update_AC_trigger_func();
```

The trigger function references the transition table in a loop, and that
worked. After the loop there's a statement that resulted in the error (line
numbers should match up):

```
CREATE OR REPLACE FUNCTION wip_update_AC_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
DECLARE
  rec RECORD;
BEGIN
  -- comment
  -- comment
  -- comment
  -- comment
  FOR rec IN SELECT DISTINCT p_instance_id FROM updated_positions LOOP

    UPDATE w_instances pi
    SET c1 = COALESCE(
      (SELECT c1
       FROM view1 ap
       WHERE ap.p_instance_id = rec.p_instance_id),
     pi.c1)
    WHERE object_id = rec.p_instance_id
    AND EXISTS (SELECT 1 FROM wip WHERE p_instance_id =
rec.p_instance_id);
  END LOOP;

  -- comment
  -- comment
  -- comment
  UPDATE w_instances pi
  SET last_modified = NOW()
  FROM updated_positions up -- ERROR line
  WHERE pi.object_id = up.p_instance_id;

  RETURN NULL;
END;
$$;
```

The other pair of triggers (named `wip_stats_delete_trigger` and
`wip_stats_delete_trigger`) are similarly defined as AFTER STATEMENT
triggers that call a function passing the transition table. This function
*only* contains a `FOR rec IN SELECT ... FROM updated_positions` LOOP. 

I believe the JIT was off.

I apologize for the dearth of information I'm able to provide and for the
lack of a simple reproducible example; I know that makes for an annoying bug
report. I also apologize if our app is doing something wrong or if this is a
known issue of some sort; I wasn't able to find anything related outside the
PostgreSQL source code itself. I'm happy to try to provide any other helpful
information.

Thanks,
Jason