PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16272
PG Version11.7
OSUbuntu 18.04
Opened2020-02-21 19:37:55+00
Reported byTom Gottfried
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16272
Logged by:          Tom Gottfried
Email address:      (redacted)
PostgreSQL version: 11.7
Operating system:   Ubuntu 18.04
Description:        

Dear PostgreSQL developers,

consider the following to reproduce:

/* Works: */
CREATE TABLE test (
    testp varchar,
    testc varchar
);
CREATE INDEX test_idx ON test
    ((CAST((testp, testc) AS test)));

INSERT INTO test (testp) VALUES ('test');

CREATE TABLE test_ext (
    newcol int,
    LIKE test INCLUDING ALL
);

INSERT INTO test_ext SELECT 1, * FROM test;


/* Does not work: */
\set VERBOSITY verbose

CREATE TABLE test_parent (
    testp varchar
);

CREATE TABLE test_child (
    testc varchar
) INHERITS (test_parent);
CREATE INDEX test_child_idx ON test_child
    ((CAST((testp, testc) AS test_child)));

INSERT INTO test_child (testp) VALUES ('test');


CREATE TABLE test_parent_ext (
    newcol int,
    LIKE test_parent
);

CREATE TABLE test_child_ext (LIKE test_child INCLUDING INDEXES)
    INHERITS (test_parent_ext);
/* =>                                                                       
   
  NOTICE:  00000: moving and merging column "testp" with inherited
definition   
  DETAIL:  User-specified column moved to the position of the inherited
column.
  LOCATION:  MergeAttributes, tablecmds.c:2378                              
   
*/

INSERT INTO test_child_ext SELECT 1, * FROM test_child;
/* =>                                                                       
   
  ERROR:  42804: attribute 1 of type record has wrong type                  
   
  DETAIL:  Table has type integer, but query expects character varying.     
   
  LOCATION:  CheckVarSlotCompatibility, execExprInterp.c:1898               
   
*/

\d test_child_idx
\d test_child_ext_row_idx
/* =>                                                                       
   
                Index "public.test_child_idx"                               
   
 Column |    Type    | Key? |           Definition                          
   
--------+------------+------+---------------------------------              
   
 row    | test_child | yes  | (ROW(testp, testc)::test_child)               
   
btree, for table "public.test_child"                                        
   
                                                                            
   
             Index "public.test_child_ext_row_idx"                          
   
 Column |    Type    | Key? |            Definition                         
   
--------+------------+------+----------------------------------             
   
 row    | test_child | yes  | (ROW(newcol, testp)::test_child)              
   
btree, for table "public.test_child_ext"                                    
   
*/

SELECT version();                                                           
 
/* =>                                                                       
   
PostgreSQL 11.7 (Ubuntu 11.7-1.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                        
     
*/

The index expression in the index created via LIKE ... INCLUDING INDEXES
still refers to the first two attributes of the table, although an attribute
has been put in place before the columns the expression referred to in the
original index.

I expected the new index expression to refer to the same (now
merged/inherited) columns as the original index (here: testp, testc) as it
actually does in the first example without inheritance.

Thanks and best regards,
Tom

Messages

DateAuthorSubject
2020-02-21 19:37:55+00PG Bug reporting formBUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE
2020-02-21 23:33:31+00Tom LaneRe: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE
2020-04-27 00:53:35+00Tom LaneRe: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE