Re: Assertion failure in HEAD and 13 after calling COMMIT in a stored proc

On Mon, Jun 21, 2021 at 04:19:27PM -0700, Jim Nasby wrote:

> The following generates an assertion failure. Quick testing with start and
> stop as well as the core dump shows it’s failing on the execution of
> `schema_name := schema_name(i)` immediately after COMMIT, because there’s no
> active snapshot. On a build without asserts I get a failure in
> GetActiveSnapshot() (second stack trace). This works fine on 12_STABLE, but
> fails on 13_STABLE and HEAD.

For me it's a typo.

need_snapshot = (expr->expr_simple_mutable || !estate->readonly_func);

HEAD with no assertion:

 CREATE OR REPLACE FUNCTION public.schema_name(i integer)
postgres-#   RETURNS text
postgres-#   LANGUAGE sql
postgres-#   IMMUTABLE
postgres-# AS $function$
postgres$# SELECT 'test_' || trim(to_char(i, '000000'))
postgres$# $function$;
CREATE FUNCTION
postgres=# CREATE OR REPLACE PROCEDURE public.build_schema(start integer, stop
postgres(# integer, commit_interval integer DEFAULT 10, do_insert boolean DEFAULT true)
postgres-#   LANGUAGE plpgsql
postgres-# AS $procedure$
postgres$# DECLARE
postgres$#      schema_name text;
postgres$# BEGIN
postgres$# FOR i IN start .. stop LOOP
postgres$#      schema_name := schema_name(i);
postgres$#      IF i % commit_interval = 0 THEN
postgres$#          --RAISE NOTICE 'COMMIT CREATE step %', i;
postgres$#          COMMIT;
postgres$#      END IF;
postgres$# END LOOP;
postgres$# END$procedure$;
CREATE PROCEDURE
postgres=# CALL build_schema(1,11);
CALL
postgres=# CALL build_schema(1,11);
CALL
postgres=# CALL build_schema(1,11);
CALL

The comments in the function are clear:

If expression is mutable OR is a non-read-only function, so need a snapshot.

Can you test please?

regards,

Ranier Vilela