> 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] 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))); 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 <nitinjadhavpostg...@gmail.com> wrote: > > 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
