pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: remi.cura@gmail.com
Subject: BUG #16328: Generated column and inheritance: strange default error
Date: Sun, 29 Mar 2020 20:33:43 +0000
Message-ID: <16328-8ded3dc9a974cd21@postgresql.org> (raw)

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



Message-ID: <16328-8ded3dc9a974cd21@postgresql.org>
Permalink:  ../16328-8ded3dc9a974cd21@postgresql.org/
Also on:    postgresql.org/message-id/16328-8ded3dc9a974cd21@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, remi.cura@gmail.com
  Subject: Re: BUG #16328: Generated column and inheritance: strange default error
  In-Reply-To: <16328-8ded3dc9a974cd21@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox