Horton

Re: Dumping/restoring fails on inherited generated column

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2020-09-25T14:16:03Z
Lists: pgsql-hackers 
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> The proposed patches will cause the last statement to be omitted, but 
> that still won't recreate the original state.  The problem is that there 
> is no command to make a column generated afterwards, like the SET 
> DEFAULT command, so we can't dump it like this.

Right.  I'm not even sure what such a command should do ... would it run
through all existing rows and update them to be the GENERATED value?

> We could probably fix this by having ALTER TABLE ONLY / DROP EXPRESSION 
> update the attlocal column of direct children to true, to make the 
> catalog state look like something that can be restored.  However, that's 
> a fair amount of complicated code, so for now I propose to just prohibit 
> this command, meaning you can't use ONLY in this command if there are 
> children.  This is new in PG13, so this change would have very limited 
> impact in practice.

+1.  At this point we would want some fairly un-complicated fix for
the v13 branch anyhow, and this seems to fit the bill.  (Also, having
child columns suddenly grow an attislocal property doesn't seem to meet
the principle of least surprise.)

			regards, tom lane

Commits

  1. Fix ALTER TABLE / INHERIT with generated columns

  2. pg_dump: Fix dumping of inherited generated columns

  3. Disallow ALTER TABLE ONLY / DROP EXPRESSION

  4. Fix several DDL issues of generated columns versus inheritance