Collected from the PG bugs email list.
|OS||Red Hat Enterprise Linux 7.6|
|Reported by||Jason Madden|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15720 Logged by: Jason Madden Email address: (redacted) 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
|2019-03-28 15:34:43+00||PG Bug reporting form||BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC|
|2019-03-29 10:12:47+00||Thomas Munro||Re: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC|
|2019-03-29 12:42:38+00||Jason Madden||Re: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC|
|2019-07-09 15:45:02+00||Tom Lane||Re: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC|
|2019-07-09 15:55:38+00||Jason Madden|
|2019-07-09 22:46:37+00||Thomas Munro|