PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns

From: "Nunya Business" <nb3425586(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
Date: 2022-12-05 19:49:40
Message-ID: emad8fa821-2230-4b8b-893b-cb2b8b376e9b@81da44b0.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Good afternoon,

I've recently run into a weird issue that I'm trying to gather more data
on before sending an official bug report on the off chance that it's
already been addressed.

Within my schema there is a table that has a GENERATED ALWAYS column
that calls a plpgsql function. The called function has a "row type"
variable declared that references a view. While the schema itself
functions properly day to day, and pg_dumpall works as expected, the
generated SQL fails to successfully execute. The table in question is
restored with no rows, and an error is generated during the COPY stating
that the type does not exist.

The issue appears to be that the COPY statement for the data is trying
to execute the function specified for the GENERATED ALWAYS column, and
that function cannot run because the view that the function references
does not yet exist.

The dump was made with: pg_dumpall -c --quote-all-identifiers
--exclude-database=postgres --exclude-database=template0
--exclude-database=template1

Is this a known or unknown issue, or am I just missing something?

Any insight is appreciated. Please reply-all as I'm not currently
subscribed to the list. Thanks in advance!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexander Korotkov 2022-12-05 23:57:49 Re: Bug in jsonb_path_exists (maybe _match) one-element scalar/variable jsonpath handling
Previous Message Thomas Munro 2022-12-05 10:09:00 Re: BUG #17705: Segmentation fault in BufFileLoadBuffer

Browse pgsql-general by date

  From Date Subject
Next Message Alexis Zapata 2022-12-06 03:01:57 Solved: Table : Bloat grow high
Previous Message Jonathan Lemig 2022-12-05 18:18:03 Fwd: Views "missing" from information_schema.view_table_usage