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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: remi(dot)cura(at)gmail(dot)com
Subject: BUG #18067: Droping function that was used to generate column drops the column, even after `DROP EXPRESSION`
Date: 2023-08-22 18:57:17
Message-ID: 18067-7a71abe62f8c64ca@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18067
Logged by: Rémi Cura
Email address: remi(dot)cura(at)gmail(dot)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 ;
```

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Dunstan 2023-08-22 19:22:46 Re: BUG #17994: Invalidating relcache corrupts tupDesc inside ExecEvalFieldStoreDeForm()
Previous Message Andres Freund 2023-08-22 18:45:58 Re: cache lookup failed dropping public schema with trgm index