Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update

On Thu, 2024-03-14 at 19:03 +1300, Carl Smith wrote:
> Command failed: pg_restore -U postgres --clean --if-exists --dbname tmf_app_manager /Users/carl/GitHub/conforma/conforma-server/database/_snapshots/TEST_14_11_2024-03-14_15-27-40/database.dump
> pg_restore: error: could not execute query: ERROR: relation "public.template" does not exist
> LINE 5: public.template
> ^
> QUERY:
> SELECT
> template.code
> FROM
> public.template
> JOIN public.template_section ON template_id = template.id
> WHERE
> template_section.id = $1;
>
>
> CONTEXT: SQL function "get_template_code" during inlining
> Command was: CREATE TABLE public.template_element (
> id integer NOT NULL,
> section_id integer NOT NULL,
> code character varying NOT NULL,
> index integer,
> title character varying,
> category public.template_element_category,
> element_type_plugin_code character varying,
> visibility_condition jsonb DEFAULT 'true'::jsonb,
> is_required jsonb DEFAULT 'true'::jsonb,
> is_editable jsonb DEFAULT 'true'::jsonb,
> validation jsonb DEFAULT 'true'::jsonb,
> initial_value jsonb,
> validation_message character varying,
> help_text character varying,
> parameters jsonb,
> reviewability public.reviewability DEFAULT 'ONLY_IF_APPLICANT_ANSWER'::public.reviewability NOT NULL,
> template_code character varying GENERATED ALWAYS AS (public.get_template_code(section_id)) STORED,
> template_version character varying GENERATED ALWAYS AS (public.get_template_version(section_id)) STORED
> );

That's not a PostgreSQL bug, that's a bug in your code.

You must have declared the function public.get_template_code(integer) as IMMUTABLE, but
it SELECTs from public.template, so it clearly is *not* immutable. It depends on the state
of the database, in your case on the existence of a certain table (and on its contents).

So you lied when you declared the function IMMUTABLE, and the consequences are your fault.

You should instead use a trigger to populate template_code. That is the correct solution,
and then you wouldn't get that error.

If that worked previously, it's purely by coincidence: if public.template happens to exist,
the restore doesn't fail.

Yours,
Laurenz Albe