Re: huge SubtransSLRU and SubtransBuffer wait_event - Mailing list pgsql-performance

On Fri, 2024-02-02 at 02:04 -0800, Nikolay Samokhvalov wrote:
> On Thu, Feb 1, 2024 at 04:42 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Today, the only feasible solution is not to create more than 64 subtransactions
> > (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.
>
> I think 64+ nesting level is quite rare

It doesn't have to be 64 *nested* subtransactions.  This is enough:

CREATE TABLE tab (x integer);

DO
$$DECLARE
   i integer;
BEGIN
   FOR i IN 1..70 LOOP
      BEGIN
         INSERT INTO tab VALUES (i);
      EXCEPTION
         WHEN unique_violation THEN
            NULL; -- ignore
      END;
   END LOOP;
END;$$;

Yours,
Laurenz Albe