Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it
Date
Msg-id CAKFQuwbPggYTMKWtwPeoZ==dfp0jYjMnhKtM=y70-w95XduG4Q@mail.gmail.com
Whole thread Raw
In response to BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
Tree view
On Tue, Jun 13, 2023 at 7:21 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17970
Logged by:          PGP
Email address:      pgp@gmx.it
PostgreSQL version: 15.3
Operating system:   Linux
Description:       


The bug reporting form is not an appropriate place to discuss feature requests.  Please subscribe to the -general list if you wish to start and partake in such discusssions.

But the reverse (i.e. re-adding the "GENERATED ALWAYS AS .. STORED" part to
an existing column) is not possible.
I think this is a contradiction, because, by modifying the underlying
function, I am able to insert incoherent values in the generated column, so,
since this is allowed, it should be possible as well to add again this kind
of constraint, after temporarily dropping it (for example, for doing a bulk
insert via COPY command, in the cases when generating the transformed_id
values outside the db is more performant than generating them automatically
within the insert statement).

I am doubtful any implementation of this feature is going to avoid recomputing the generated column value for every existing row.  I agree that the syntax seems to be a missing feature.  But apparently it wasn't added for some reason and I see little true benefit to the feature itself.

Also, you are not allowed to modify the underlying function such that it produces different values now for inputs than it did before.  Sure, the system is incapable of enforcing this rule, but it is well documented (see the definition of immutable) and any consequences for violating the rule are the fault of the admin, not the system.

David J.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it
Next
From: Tom Lane
Date:
Subject: Re: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it