BUG #17960: pg_dump does not backup generated columns correctly.
The following bug has been logged on the website:
Bug reference: 17960
Logged by: Sajjad Hassany Pazoky
Email address: ijustloveit13@gmail.com
PostgreSQL version: 15.2
Operating system: Windows
Description:
Hi
Dear are two stackoverflow bugs reported an none had an answer:
https://stackoverflow.com/questions/64600614/restoring-pg-database-from-dump-fails-due-to-generated-columns
https://dba.stackexchange.com/questions/327852/pg-dump-unable-to-produce-a-valid-backup
pg_dump does not wite DEFAULT for generated columns. So, it produces error
when restoring.
The strange part is that when I create the table again, fill it with data
and use pg_dump to bacup, it is correct, but the current table in the
database producecs wrong backup.
I tested this on Postgresql 13, 14, and 15.
I'm available if any assistance is needed.
PG Bug reporting form <noreply@postgresql.org> writes:
pg_dump does not wite DEFAULT for generated columns. So, it produces error
when restoring.
It works fine for me, and if it didn't work fine for other people
we'd have had a lot of other bug reports by now. Please provide
a concrete example.
The strange part is that when I create the table again, fill it with data
and use pg_dump to bacup, it is correct, but the current table in the
database producecs wrong backup.
Hmm ... what minor release of pg_dump are you using? I wonder if
you are hitting the bug fixed here:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL_15_BR [0b126c6a4] 2021-11-22 15:25:48 -0500
Branch: REL_14_STABLE Release: REL_14_2 [aedc4600d] 2021-11-22 15:25:48 -0500
Branch: REL_13_STABLE Release: REL_13_6 [6fc8b145e] 2021-11-22 15:25:48 -0500
Branch: REL_12_STABLE Release: REL_12_10 [1e7f588ad] 2021-11-22 15:25:48 -0500
Fix pg_dump --inserts mode for generated columns with dropped columns.
If a table contains a generated column that's preceded by a dropped
column, dumpTableData_insert failed to account for the dropped
column, and would emit DEFAULT placeholder(s) in the wrong column(s).
This resulted in failures at restore time. The default COPY code path
did not have this bug, likely explaining why it wasn't noticed sooner.
regards, tom lane