Alvaro Herrera <[email protected]> wrote: > On 2026-Sep-05, Osama Abdul Qader wrote: > > > I understand the distinction now. Allowing REPACK (ANALYZE) in a > > transaction block in the future would not necessarily mean that it is safe > > to execute it from a function, procedure, or DO block, since ANALYZE may > > start a new transaction in process_single_relation() while an SPI session > > is active. > > Well, I think the main point of running REPACK (ANALYZE) inside a > transaction is to allow it to run in a procedure. Consider something > like > > do $$ > declare r record; > begin > for r in > select relname from pg_class where relkind = 'r' and > relnamespace = (select oid from pg_namespace where > nspname = 'public') > loop > execute 'repack (verbose) ' || r.relname; > commit; > end loop; > end > $$; > > This works fine today and with the patch, both with REPACK and with > CLUSTER (good); but not with VACUUM FULL (sad, but we no longer care: > just use repack.) > > This is useful because it allows server-controlled execution of > repacking each table in its own transaction. But as soon as you add the > ANALYZE option, which would be valuable, this recipe no longer works. > > My point is that just the ability to run REPACK (ANALYZE) in a > transaction block without allowing it in a function would be, I think, > rather pointless -- who could possibly be interested in repacking > multiple tables in the same transaction? There's just no benefit. > > OTOH I think it may even be useful to implement in-procedure execution > for CONCURRENTLY, but that's likely a more challenging patch than > ANALYZE.
An alternative approach: as there are various commands that start their own transactions, it could help if we taught the EXECUTE command - when executed from pl/pgsql procedure or anonymous block (DO) - to accept this behavior. That would probably require a new option for EXECUTE to declare that a new transaction is either started by the statement, or (if the statement actually does not do it) by EXECUTE itself. (Then we might want to enhance the corresponding commands / functions in other languages, however it seems most useful in pl/pgsql.) -- Antonin Houska Web: https://www.cybertec-postgresql.com
