pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
3+ messages / 3 participants
[nested] [flat]
* PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
@ 2022-12-05 19:49 Nunya Business <nb3425586@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Nunya Business @ 2022-12-05 19:49 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
@ 2022-12-06 15:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nunya Business <nb3425586@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-12-06 15:23 UTC (permalink / raw)
To: Nunya Business <nb3425586@gmail.com>; +Cc: pgsql-general@postgresql.org
"Nunya Business" <nb3425586@gmail.com> writes:
> 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.
Hmm, do you have actually circular dependencies in that? pg_dump has
some heuristics for dealing with such cases, but maybe it needs more.
Please create a self-contained example and submit it to pgsql-bugs.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns
@ 2022-12-06 15:51 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Nunya Business <nb3425586@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2022-12-06 15:51 UTC (permalink / raw)
To: Nunya Business <nb3425586@gmail.com>; pgsql-general@postgresql.org
On 12/5/22 11:49, Nunya Business wrote:
> 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?
https://www.postgresql.org/docs/current/sql-createtable.html
"GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column
cannot be written to, and when read the result of the specified
expression will be returned.
The keyword STORED is required to signify that the column will be
computed on write and will be stored on disk.
The generation expression can refer to other columns in the table,
but not other generated columns. Any functions and operators used must
be immutable. **References to other tables are not allowed.**
"
Emphasis(**) added.
I'm going to say hiding the table/view reference in a function is not
going to work any better then when folks try that in a CHECK constraint.
>
> Any insight is appreciated. Please reply-all as I'm not currently
> subscribed to the list. Thanks in advance!
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-12-06 15:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-05 19:49 PG 14.5 -- Impossible to restore dump due to interaction/order of views, functions, and generated columns Nunya Business <nb3425586@gmail.com>
2022-12-06 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-06 15:51 ` Adrian Klaver <adrian.klaver@aklaver.com>
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