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