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