pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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
Date: Tue, 13 Apr 2021 17:29:17 +0300
Message-ID: <1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru> (raw)
In-Reply-To: <03644c0e6bb82132ac783982b6abffdf@postgrespro.ru>
References: <03644c0e6bb82132ac783982b6abffdf@postgrespro.ru>

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#d93b5...
> 
> 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





view thread (3+ messages)  latest in thread

Message-ID: <1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru>
Permalink:  ../1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru/
Also on:    postgresql.org/message-id/1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: y.sokolov@postgrespro.ru, pgsql-bugs@lists.postgresql.org, andres@anarazel.de
  Subject: Re: Problem with accessing TOAST data in stored procedures
  In-Reply-To: <1a9932c04b41d10c1ceb1d24cc1694f5@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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