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 #16328: Generated column and inheritance: strange default error |
Date: | 2020-03-29 20:33:43 |
Message-ID: | 16328-8ded3dc9a974cd21@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: 16328
Logged by: Rémi Cura
Email address: remi(dot)cura(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2020-03-29 21:00:01 | BUG #16329: Valgrind detects an invalid read when building a gist index with buffering |
Previous Message | Euler Taveira | 2020-03-29 19:52:29 | Re: BUG #16327: Too many files in pg_replslot folder |