Обсуждение: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS constraint to existing columns after removing it

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:

Consider the following SQL statements:

---------------------------------------------------------------------
-- create a sequence
CREATE SEQUENCE seq1;

-- create a simple function
CREATE OR REPLACE FUNCTION myfun(seq_num BIGINT) RETURNS BIGINT AS
$$
SELECT seq_num * 2;
$$ LANGUAGE sql IMMUTABLE;

-- create a table with auto-generated values obtained by applying the
function to the sequence values
CREATE TABLE items (
    num_id BIGINT PRIMARY KEY DEFAULT NEXTVAL('seq1'),
    transformed_id BIGINT GENERATED ALWAYS AS (myfun(num_id)) STORED,
    description VARCHAR(100)
);

--insert some test values
INSERT INTO items(description) VALUES ('first'), ('second');

-- table content is now:
-- num_id | transformed_id | description
--   1           2            'first'
--   2           4            'second'

-- update the function
CREATE OR REPLACE FUNCTION myfun(seq_num BIGINT) RETURNS BIGINT AS
$$
SELECT seq_num * 3;
$$ LANGUAGE sql IMMUTABLE;

--insert some more test values
INSERT INTO items(description) VALUES ('third'), ('fourth');

-- table content is now:
-- num_id | transformed_id | description
--   1           2            'first'
--   2           4            'second'
--   3           9            'third'
--   4           12           'fourth'

---------------------------------------------------------------------

From what I know, it is possible to transform a GENERATED ALWAYS AS ..
STORED column into a normal one, by using:

ALTER TABLE items ALTER COLUMN transformed_id DROP EXPRESSION;

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 hope you'll implement this possibility in future PostgreSQL versions.

Kind regards

PGP

PG Bug reporting form <noreply@postgresql.org> writes:
> From what I know, it is possible to transform a GENERATED ALWAYS AS ..
> STORED column into a normal one, by using:
> ALTER TABLE items ALTER COLUMN transformed_id DROP EXPRESSION;
> But the reverse (i.e. re-adding the "GENERATED ALWAYS AS .. STORED" part to
> an existing column) is not possible.

Just drop the column and re-add it.  The system would have to recompute
all the entries anyway, so I can't get excited about offering a shortcut.

> I think this is a contradiction, because, by modifying the underlying
> function, I am able to insert incoherent values in the generated column,

As David mentioned, that is not considered a supported thing.  We cannot
enforce it (look up the "halting problem" sometime), but if you change
an immutable function's behavior then ensuing problems are your fault
not Postgres'.

            regards, tom lane