MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Date: Tue, 13 Apr 2021 17:29:17 +0300
From: Yura Sokolov <y.sokolov@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org
Cc: andres@anarazel.de
Subject: Re: Problem with accessing TOAST data in stored procedures
In-Reply-To: <03644c0e6bb82132ac783982b6abffdf@postgrespro.ru>
References: <03644c0e6bb82132ac783982b6abffdf@postgrespro.ru>
User-Agent: Roundcube Webmail/1.4.0
Message-ID: <1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru>
Archived-At: 
 <https://www.postgresql.org/message-id/1a9932c04b41d10c1ceb1d24cc1694f5%40postgrespro.ru>
Precedence: bulk

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