3

We use our Postgres database dumps as a way to backup/reset our staging DB. As part of that, we frequently remove all rows in the DB and insert the rows from the PG dump. However, the generated columns are included as part of the PG dump, but with their values instead of the DEFAULT keyword.

Trying to run the DB dump triggers cannot insert into column errors since one cannot insert values into a generated column. How do we dump our DB and recreate it from the dump despite the generated columns?

EDIT: Note that we cannot use GENERATED BY DEFAULT or OVERRIDING SYSTEM VALUE since those are only available for identity columns and not generated columns.

EDIT 2: It seems that it's a special case for us that the values are dumped instead of as DEFAULT. Any idea why that might be?

pir
  • 5,513
  • 12
  • 63
  • 101
  • 2
    Are you using the correct version of pg_dump? It should be equal to the version of your database. With generated columns, using --column-insert, the output should include that column and a value of DEFAULT, which will be inserted as expected. – Jeremy Oct 30 '20 at 02:49
  • `pg_dump` will indeed include all the target columns, but for the generated column, it will use the `default` clause in the `values` part. Something like this: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2e8e67f1fff9aee4f97099dbb6ca886f so I don't see a problem –  Oct 30 '20 at 15:13
  • Thanks! Sounds like it might be a version thing. I'm using `pg_dump (PostgreSQL) 12.4` for a PG 12 DB so I thought that it would work as expected. I'll try upgrading and see if it fixes the problem. – pir Oct 30 '20 at 16:03
  • I'm on the latest PG 12 version (see https://formulae.brew.sh/formula/postgresql@12) so I'd have to upgrade to a later major version locally than the remote DB, which seems non-ideal. @Jeremy, do you know if this is fixed in pg_dump for PG 13? – pir Oct 30 '20 at 16:07
  • Just tried with `pg_dump (PostgreSQL) 13.0` and the actual values are still dumped instead of `default`. – pir Oct 30 '20 at 17:44
  • I tested with 12 and it worked fine for me. How are you calling pg_dump? Also, are you certain you are talking about the generated columns to which you linked or the identity columns? – Jeremy Oct 30 '20 at 17:52
  • Strange. I'm calling it straight from the Mac terminal: `PGPASSWORD=mypwd pg_dump -f tmp_dev.sql --column-insert -t "myschema.mytable" -n "myschema" -w -h myhostname -p myport -U myuser -d mydb`. I add the column with `ALTER TABLE mys.myt ADD COLUMN my_c uuid GENERATED ALWAYS AS (uuid_from_id(call_keep_id)) STORED;` and in the dump, it also shows up as a generated column. – pir Oct 30 '20 at 18:07
  • This is the PG DB version in case it's useful: `PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit` – pir Oct 30 '20 at 18:11
  • Strange, indeed. `pg_dump --version` shows 12.3 as well? – Jeremy Oct 30 '20 at 18:51
  • It used to show `pg_dump (PostgreSQL) 12.4`. I upgraded locally to try to fix it so it now shows `pg_dump (PostgreSQL) 13.0`. – pir Oct 30 '20 at 19:10
  • @a_horse_with_no_name I agree that the output in the DB fiddle is the intended output, but it's not clear to me why that's not the output we get from PG dump. Any idea why? – pir Oct 30 '20 at 20:06
  • Well, it's the output I get from pg_dump (both 12 and 13). No idea what is wrong on your system –  Oct 30 '20 at 20:28
  • I get this error both using pg_dump 12/13 on Mac installed using homebrew and on Linux using `aptitude install -y postgresql-12 postgresql-client-12`. Perhaps it could be something to do with the DB? (although I have no idea what) – pir Oct 30 '20 at 20:35
  • In 'recovering' to stage environment from another you need to preserve the key values in order to preserve FK relationships. The problem is DEFAULT values are not preserved. You will therefore have to determine the new FK values. This is (at least can be) a greater then maintaining the original values. However generated columns offers a solution. Typically they are created as "GENERATED ALWAYS", which prevents restoring their value. Instead create them as ""GENERATED BY DEFAULT" then your 'restore' procedure can then save the existing values. Make sure you also restore sequences! – Belayer Oct 30 '20 at 21:27
  • I'm not sure I understand. We're not using identity columns, but [generated columns](https://www.postgresql.org/docs/12/ddl-generated-columns.html), which only has `GENERATED ALWAYS` as an option. – pir Oct 30 '20 at 22:03
  • I've found now that dropping the table and recreating it using the exact dumped SQL definition fixes the problem. If I now dump the table, the values are correctly dumped with `DEFAULT`. Any idea why that might be and how I can fix it w/o dumping the table? – pir Nov 02 '20 at 03:57
  • Could it have something to do with the table being originally created in PG 10 and since upgraded to PG 12? Would such a table have a different underlying data storage than a table created in PG 12? – pir Nov 02 '20 at 04:00

0 Answers0