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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jason(dot)madden(at)nextthought(dot)com
Subject: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC
Date: 2019-03-28 15:34:43
Message-ID: 15720-38c2b29e5d720187@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15720
Logged by: Jason Madden
Email address: jason(dot)madden(at)nextthought(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2019-03-28 17:04:13 Re: BUG #15718: DROP TABLE fails if it's partitioned by a key of a deleted enum
Previous Message PG Bug reporting form 2019-03-28 13:38:29 BUG #15719: When using reverse proxy in Kubernetes (which terminates TLS) - pgAdmin container often hangs