Re: Problem with accessing TOAST data in stored procedures

Yura Sokolov писал 2021-04-13 17:01:
> Good day.
>
> There is reproducible bug posted 10 months ago by Konstantin Knizhnik:
> https://www.postgresql.org/message-id/flat/5d335911-fb25-60cd-4aa7-a5bd0954aea0%40postgrespro.ru
>
> I'll copy that message here.
>
>> Stored procedure allows to commit/rollback transaction inside its
>> body.
>> Unfortunately it is not always correctly handled.
>> This fragment of code reports ERROR: no known snapshots
>
>> CREATE TABLE toasted(id serial primary key, data text);
>> INSERT INTO toasted(data) VALUES((SELECT
>> string_agg(random()::text,':') FROM generate_series(1, 1000)));
>> INSERT INTO toasted(data) VALUES((SELECT
>> string_agg(random()::text,':') FROM generate_series(1, 1000)));
>> DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted
>> LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END
>> LOOP;END;$$;
>
>> I found out that code responsible for persisting portal correctly
>> extracts TOAST data.
>> But pl_pgsql is using prefetch and so takes records form SPI_tuptable,
>> not from stored tuplestore.
>> I didn't not find better solution rather than disabling prefetch when
>> loop body contains COMMIT or ROLLBACK statements.
>> Unfortunately there is no existed walker for plpgsql statements tree,
>> so I have to add such walker.
>> I hope that it will be useful not only for this case.
>> But may be there are some other ways to fix this problem...
>> Please notice the following bug report which may be also related:
>> https://www.postgresql.org/message-id/flat/20190904105618.j5l6fhyesmprmstf%40alap3.anarazel.de#d93b550f2f0c1eae785f666251432929
>
> Original message contains patch as well.
>
> Andres, I've mentioned you've participated in GIST case in bottom
> link, and it looks like related,
> because COMMIT is called there as well.

Remark: I've checked both with 13.2 and master branch.

Regards,
Yura Sokolov