From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Carl Smith <carl@msupply.foundation>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update
Date: Thu, 14 Mar 2024 09:55:25 +0100
Message-ID: <ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at> (raw)
In-Reply-To: <ED83A0DA-A6E7-400E-B445-8B8FC8890014@msupply.foundation>
References: <18392-142078457489d3a5@postgresql.org>
<a07a7bc4d0442e7dadce1ec57c014c2ebced13ee.camel@cybertec.at>
<ED83A0DA-A6E7-400E-B445-8B8FC8890014@msupply.foundation>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, carl@msupply.foundation, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18392: Can't restore database (using pg_restore) after latest Feb 8 update
In-Reply-To: <ae4f109912f70d6628eafe8efb99a6f5fbfdde83.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox