Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux