Add a test case related to the error "cannot fetch toast data without an active snapshot"
Hi,
I was going through the comments [1]/* * GetOldestSnapshot returns NULL if the session has no active snapshots. * We can get that if, for example, a procedure fetches a toasted value * into a local variable, commits, and then tries to detoast the value. * Such coding is unsafe, because once we commit there is nothing to * prevent the toast data from being deleted. Detoasting *must* happen in * the same transaction that originally fetched the toast pointer. Hence, * rather than trying to band-aid over the problem, throw an error. (This * is not very much protection, because in many scenarios the procedure * would have already created a new transaction snapshot, preventing us * from detecting the problem. But it's better than nothing, and for sure * we shouldn't expend code on masking the problem more.) */ mentioned in
init_toast_snapshot() and based on the comments understood that the
error "cannot fetch toast data without an active snapshot" will occur
if a procedure fetches a toasted value into a local variable, commits,
and then tries to detoast the value. I would like to know the sample
query which causes such behaviour. I checked the test cases. Looks
like such a case is not present in the regression suit. It is better
to add one.
[1]: /* * GetOldestSnapshot returns NULL if the session has no active snapshots. * We can get that if, for example, a procedure fetches a toasted value * into a local variable, commits, and then tries to detoast the value. * Such coding is unsafe, because once we commit there is nothing to * prevent the toast data from being deleted. Detoasting *must* happen in * the same transaction that originally fetched the toast pointer. Hence, * rather than trying to band-aid over the problem, throw an error. (This * is not very much protection, because in many scenarios the procedure * would have already created a new transaction snapshot, preventing us * from detecting the problem. But it's better than nothing, and for sure * we shouldn't expend code on masking the problem more.) */
/*
* GetOldestSnapshot returns NULL if the session has no active snapshots.
* We can get that if, for example, a procedure fetches a toasted value
* into a local variable, commits, and then tries to detoast the value.
* Such coding is unsafe, because once we commit there is nothing to
* prevent the toast data from being deleted. Detoasting *must* happen in
* the same transaction that originally fetched the toast pointer. Hence,
* rather than trying to band-aid over the problem, throw an error. (This
* is not very much protection, because in many scenarios the procedure
* would have already created a new transaction snapshot, preventing us
* from detecting the problem. But it's better than nothing, and for sure
* we shouldn't expend code on masking the problem more.)
*/
Thanks & Regards,
Nitin Jadhav
if a procedure fetches a toasted value into a local variable, commits,
and then tries to detoast the value.
I spent some time and tried to reproduce this error by using [1]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)));
queries. But the error did not occur. Not sure whether I followed what
is mentioned in the above comment. Please correct me if I am wrong.
[1]: 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)));
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;
DECLARE vref_cursor REFCURSOR;
BEGIN
OPEN vref_cursor FOR SELECT data FROM toasted;
LOOP
fetch vref_cursor into v_r;
INSERT INTO toasted(data) VALUES(v_r.data);
COMMIT;
END LOOP;
END;$$;
Thanks & Regards,
Nitin Jadhav
On Fri, Jan 27, 2023 at 6:26 PM Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:
Show quoted text
Hi,
I was going through the comments [1] mentioned in
init_toast_snapshot() and based on the comments understood that the
error "cannot fetch toast data without an active snapshot" will occur
if a procedure fetches a toasted value into a local variable, commits,
and then tries to detoast the value. I would like to know the sample
query which causes such behaviour. I checked the test cases. Looks
like such a case is not present in the regression suit. It is better
to add one.[1]:
/*
* GetOldestSnapshot returns NULL if the session has no active snapshots.
* We can get that if, for example, a procedure fetches a toasted value
* into a local variable, commits, and then tries to detoast the value.
* Such coding is unsafe, because once we commit there is nothing to
* prevent the toast data from being deleted. Detoasting *must* happen in
* the same transaction that originally fetched the toast pointer. Hence,
* rather than trying to band-aid over the problem, throw an error. (This
* is not very much protection, because in many scenarios the procedure
* would have already created a new transaction snapshot, preventing us
* from detecting the problem. But it's better than nothing, and for sure
* we shouldn't expend code on masking the problem more.)
*/Thanks & Regards,
Nitin Jadhav