Thread: BUG #18067: Droping function that was used to generate column drops the column, even after `DROP EXPRESSION`

The following bug has been logged on the website:

Bug reference:      18067
Logged by:          Rémi Cura
Email address:      remi.cura@gmail.com
PostgreSQL version: 14.7
Operating system:   PostgreSQL 14.7 on aarch64-unknown-linux-gnu, comp
Description:

```SQL
/* BUG REPORT:
    Hello dear postgres community, I think I stumbled upon a bug involving
generated columns dependencies 
       persisting after the column is not generated anymore.
    This convenient scripts allows to reproduce easily
       

 - creating a table with generated column based on function
 - making the generated column not generated anymore
 - droppign function
 --> the column gets dropped entirely from the table 
 
 Expected outcome: 
 AS the column is not generated anymore, it should have nothing to do with
what function was used to generate it
 At the very least, we should get an error message/dependency warning
 
 Here are the full, safe instructions to reproduce.
 *  */

-- select version();
    -- PostgreSQL 14.7 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit 

CREATE SCHEMA demo_pg_bug_gencol ;

-- create stripping function 
    DROP FUNCTION IF EXISTS demo_pg_bug_gencol.text_stripping(IN a_string
text , OUT a_stripped_string text );
   CREATE OR REPLACE FUNCTION demo_pg_bug_gencol.text_stripping(IN a_string
text , OUT a_stripped_string text ) 
    RETURNS TEXT
    LANGUAGE SQL    IMMUTABLE     STRICT     PARALLEL SAFE
    AS $fun1$
        SELECT TRIM(BOTH '/' FROM a_string) ; 
    $fun1$; 

-- create hashing function
    DROP FUNCTION IF EXISTS demo_pg_bug_gencol.string2uuid(a_string text);
    CREATE OR REPLACE FUNCTION demo_pg_bug_gencol.string2uuid( a_string
text)
    RETURNS uuid 
    LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS $$   
        SELECT uuid_generate_v5(
            uuid_ns_url()
            ,  demo_pg_bug_gencol.text_stripping(a_string)
        ) ;
    $$ ;

-- create table with generated column using function
    DROP TABLE IF EXISTS demo_pg_bug_gencol.a_table ;
    CREATE TABLE demo_pg_bug_gencol.a_table (
        some_string text 
        , some_hash text GENERATED ALWAYS AS
(demo_pg_bug_gencol.string2uuid(
demo_pg_bug_gencol.text_stripping(some_string)) ) STORED 
    ); 
    

-- inserting some strings 
    INSERT INTO demo_pg_bug_gencol.a_table ( some_string) 
    SELECT concat('/', s, '/')
    FROM generate_series('1000/01/01'::Date,'1000/01/3'::date, '1 day' ) as
s ;
    
    SELECT * 
    FROM demo_pg_bug_gencol.a_table;
        --    some_string                   |some_hash
    |
        --
------------------------------+------------------------------------+
        --    /1000-01-01
00:00:00-04:56:02/|242745c8-234b-5822-be66-ca4cbf6b9973|
        --    /1000-01-02
00:00:00-04:56:02/|f6f3c5de-07e3-5f4b-bcc0-3d37d7d78e3c|
        --    /1000-01-03
00:00:00-04:56:02/|ce6c317c-b36b-5af1-9371-ca9087c33c43|

-- now make the column not generated:
    ALTER TABLE demo_pg_bug_gencol.a_table ALTER COLUMN some_hash DROP
EXPRESSIon ; 

-- we can still access the column, everything fine 
    SELECT * 
    FROM demo_pg_bug_gencol.a_table;


-- inserting new values --> the column is not generated anymore --> expected
behavior
    INSERT INTO demo_pg_bug_gencol.a_table ( some_string) 
    SELECT concat('/', s, '/')
    FROM generate_series('2000/01/01'::Date,'2000/01/03'::date, '1 day' ) as
s ;
    
        SELECT * 
        FROM demo_pg_bug_gencol.a_table;
        --    some_string                   |some_hash
    |
        --
------------------------------+------------------------------------+
        --    /1000-01-01
00:00:00-04:56:02/|242745c8-234b-5822-be66-ca4cbf6b9973|
        --    /1000-01-02
00:00:00-04:56:02/|f6f3c5de-07e3-5f4b-bcc0-3d37d7d78e3c|
        --    /1000-01-03
00:00:00-04:56:02/|ce6c317c-b36b-5af1-9371-ca9087c33c43|
        --    /2000-01-01 00:00:00-05/      |
    |
        --    /2000-01-02 00:00:00-05/      |
    |
        --    /2000-01-03 00:00:00-05/      |
    |

-- now drop the function that was used to generat the column 
    DROP FUNCTION IF EXISTS demo_pg_bug_gencol.text_stripping(IN a_string
text , OUT a_stripped_string text );

--> the column got dropped ! 
    SELECT some_string, some_hash
    FROM demo_pg_bug_gencol.a_table;
        --> /!\ Error [42703]: ERROR: column "some_hash" does not exist


---- cleaning 
-- DROP SCHEMA IF EXISTS demo_pg_bug_gencol CASCADE ; 
```

    Hello dear postgres community, I think I stumbled upon a bug involving

generated columns dependencies 

       persisting after the column is not generated anymore.

    This convenient scripts allows to reproduce easily