BUG #16328: Generated column and inheritance: strange default error
От | PG Bug reporting form |
---|---|
Тема | BUG #16328: Generated column and inheritance: strange default error |
Дата | |
Msg-id | 16328-8ded3dc9a974cd21@postgresql.org обсуждение исходный текст |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16328 Logged by: Rémi Cura Email address: remi.cura@gmail.com PostgreSQL version: 12.2 Operating system: Ubuntu 18.04 Description: (repost from hackers) I encountered a strange issue with generated column and inheritance. The core of the problem is that when inheriting from a table, you can re-declare the same column (provided it has the same type). But when doing this and introducing a generated column, an error is raised `[0A000] ERROR: cannot use column reference in DEFAULT expression` This is too bad, as generated columns are an awesome new feature. I don't think that is an expected behavior, and at the very least the error is misleading. Here is a super short synthetic demo, along with 2 workarounds ```SQL ---- testing an issue with inheritance and generated column DROP SCHEMA IF EXISTS test_inheritance_and_generated_column CASCADE ; CREATE SCHEMA IF NOT EXISTS test_inheritance_and_generated_column ; ---------------------------------------------------------------------------------------------------- --- ERROR (set a generated column in an inherited table) DROP TABLE IF EXISTS test_inheritance_and_generated_column.parent_table CASCADE; CREATE TABLE test_inheritance_and_generated_column.parent_table( science_grade float DEFAULT 0.7 , avg_grade float ); -- THIS RAISE THE ERROR : [0A000] ERROR: cannot use column reference in DEFAULT expression DROP TABLE IF EXISTS test_inheritance_and_generated_column.child_basic; CREATE TABLE test_inheritance_and_generated_column.child_basic( literature_grade float DEFAULT 0.3, -- avg_grade float is inherited avg_grade float GENERATED ALWAYS AS ( (science_grade+literature_grade)/2.0 ) STORED )INHERITS (test_inheritance_and_generated_column.parent_table); ------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------- --- WORKS (removing the column from parent) DROP TABLE IF EXISTS test_inheritance_and_generated_column.parent_table CASCADE; CREATE TABLE test_inheritance_and_generated_column.parent_table( science_grade float DEFAULT 0.7 -- , avg_grade float ); -- DROP TABLE IF EXISTS test_inheritance_and_generated_column.child_basic; CREATE TABLE test_inheritance_and_generated_column.child_basic( literature_grade float DEFAULT 0.3, avg_grade float GENERATED ALWAYS AS ( (science_grade+literature_grade)/2.0 ) STORED )INHERITS (test_inheritance_and_generated_column.parent_table); ------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------- -- THIS WORKS (droping inheritance, dropping column, creating column with generating, adding inheritance ) DROP TABLE IF EXISTS test_inheritance_and_generated_column.parent_table CASCADE; CREATE TABLE test_inheritance_and_generated_column.parent_table( science_grade float DEFAULT 0.7 , avg_grade float ); -- DROP TABLE IF EXISTS test_inheritance_and_generated_column.child_basic; CREATE TABLE test_inheritance_and_generated_column.child_basic( literature_grade float DEFAULT 0.3, avg_grade float )INHERITS (test_inheritance_and_generated_column.parent_table); ALTER TABLE test_inheritance_and_generated_column.child_basic NO INHERIT test_inheritance_and_generated_column.parent_table; ALTER TABLE test_inheritance_and_generated_column.child_basic DROP COLUMN avg_grade; ALTER TABLE test_inheritance_and_generated_column.child_basic ADD COLUMN avg_grade float GENERATED ALWAYS AS ( (science_grade+literature_grade)/2.0 ) STORED; ALTER TABLE test_inheritance_and_generated_column.child_basic INHERIT test_inheritance_and_generated_column.parent_table; ---------------------------------------------------------------------------------------------------- ``` PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
В списке pgsql-bugs по дате отправления:
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #16329: Valgrind detects an invalid read when building a gist index with buffering