pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed[BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
3+ messages / 2 participants
[nested] [flat]
* [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
@ 2020-08-28 15:43 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jehan-Guillaume de Rorthais @ 2020-08-28 15:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi,
We discovered a bug in plpgsql.
When using RETURN QUERY on a relation with some toasted values and when this
relaiton is later dropped or truncated, an error is raised at the end of the
function.
Consider the following reproduction test:
BEGIN;
CREATE TABLE IF NOT EXISTS temp_rel ( str text );
-- feed enough data to toast the field
INSERT INTO temp_rel
SELECT string_agg(chr((33+random()*93)::int),'') AS v
FROM generate_series(1,16000);
CREATE OR REPLACE FUNCTION testcase(OUT res text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY SELECT str FROM temp_rel;
DROP TABLE temp_rel;
--TRUNCATE TABLE temp_rel;
END
$$;
SELECT * FROM testcase();
ROLLBACK;
When using "DROP TABLE", the function finishes with this error:
ERROR: could not open relation with OID xxx
WHEN using "TRUNCATE", it finishes with:
ERROR: XX001: missing chunk number 0 for toast value xxx in yyy
Both have the same stack path leading to pg_detoast_datum_packed (or
pg_detoast_datum with a large array of smaller values). Eg.:
[...]
#2 toast_fetch_datum at detoast.c:352
#3 detoast_attr at detoast.c:122
#4 pg_detoast_datum_packed at fmgr.c:1761
#5 text_to_cstring at varlena.c:208
#6 textout at varlena.c:557
#7 FunctionCall1Coll at fmgr.c:1142
#8 OutputFunctionCall at fmgr.c:1579
#9 printtup at printtup.c:434
#10 ExecutePlan at execMain.c:1677
#11 standard_ExecutorRun at execMain.c:364
#12 ExecutorRun at execMain.c:308
[...]
So I suppose the tuplestore built during the function call is keeping
references to toasted values. As soon as the function is ready to output the
result, it fails fetching the toasted values if the relation has been truncated
or dropped in its body some time after the tuplesort has been built.
Regards,
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
@ 2020-08-29 17:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2020-08-29 17:47 UTC (permalink / raw)
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>; +Cc: pgsql-bugs@lists.postgresql.org
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> We discovered a bug in plpgsql.
> When using RETURN QUERY on a relation with some toasted values and when this
> relaiton is later dropped or truncated, an error is raised at the end of the
> function.
This isn't particularly RETURN QUERY's fault; there are any number of ways
to produce the same sort of error. I reproduced it with
declare r record;
...
for r in SELECT str FROM temp_rel loop
return next r.str;
end loop;
as well as
declare r record;
...
SELECT str INTO r FROM temp_rel LIMIT 1;
DROP TABLE temp_rel;
--TRUNCATE TABLE temp_rel;
return r.str;
I guess we could forcibly detoast values in enough places to close all the
gaps, but the performance costs might be annoying. I think a case can
definitely be made for saying "don't do that".
(Another idea, perhaps, might be to detoast only in volatile functions,
reasoning that a nonvolatile one can't drop the table.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
@ 2020-09-04 15:02 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jehan-Guillaume de Rorthais @ 2020-09-04 15:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On Sat, 29 Aug 2020 13:47:19 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> > We discovered a bug in plpgsql.
> > When using RETURN QUERY on a relation with some toasted values and when this
> > relaiton is later dropped or truncated, an error is raised at the end of the
> > function.
>
> This isn't particularly RETURN QUERY's fault; there are any number of ways
> to produce the same sort of error. I reproduced it with
> [...]
Indeed. Thanks.
> I guess we could forcibly detoast values in enough places to close all the
> gaps, but the performance costs might be annoying. I think a case can
> definitely be made for saying "don't do that".
What do you mean? Writing in configuration to not drop a relation in a function
where some past computed results depend on it?
> (Another idea, perhaps, might be to detoast only in volatile functions,
> reasoning that a nonvolatile one can't drop the table.)
Would it be possible to detoast values iif the related relation are dropped
later?
Regards,
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-09-04 15:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-28 15:43 [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2020-08-29 17:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-04 15:02 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox