Skip to content

Commit eddbc84

Browse files
e-mbrowne-mbrown
committed
sql: fix reporting of stored and key columns in pg_attribute
The primary key was stored in pg_attribute using its table attribute number and stored columns weren't included in pg_attribute. This commit changes the behavior to match Postgres. Release note (bug fix): Fixed the calculation of the pg_attribute.attnum column for indexes so that the attnum is always based on the order the column appears in the index. Also fixed the pg_attribute table so that it includes stored columns in secondary indexes.
1 parent 1c02afc commit eddbc84

2 files changed

Lines changed: 71 additions & 16 deletions

File tree

‎pkg/sql/logictest/testdata/logic_test/pg_catalog‎

Lines changed: 53 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -715,22 +715,23 @@ attrelid relname attname atttypid attstattarget
715715
110 t1 m 20 0 8 14 0 -1
716716
110 t1 n 20 0 8 15 0 -1
717717
3687884466 t1_pkey p 701 0 8 1 0 -1
718-
3687884465 t1_a_key a 20 0 8 2 0 -1
719-
3687884464 index_key b 20 0 8 3 0 -1
720-
3687884464 index_key c 20 0 8 4 0 -1
718+
3687884465 t1_a_key a 20 0 8 1 0 -1
719+
3687884464 index_key b 20 0 8 1 0 -1
720+
3687884464 index_key c 20 0 8 2 0 -1
721721
111 t1_m_seq value 20 0 8 1 0 -1
722722
112 t1_n_seq value 20 0 8 1 0 -1
723723
113 t2 t1_id 20 0 8 1 0 -1
724724
113 t2 rowid 20 0 8 2 0 -1
725-
2955071325 t2_pkey rowid 20 0 8 2 0 -1
725+
2955071325 t2_pkey rowid 20 0 8 1 0 -1
726726
2955071326 t2_t1_id_idx t1_id 20 0 8 1 0 -1
727727
114 t3 a 20 0 8 1 0 -1
728728
114 t3 b 20 0 8 2 0 -1
729729
114 t3 c 25 0 -1 3 0 -1
730730
114 t3 rowid 20 0 8 4 0 -1
731-
2695335054 t3_pkey rowid 20 0 8 4 0 -1
731+
2695335054 t3_pkey rowid 20 0 8 1 0 -1
732732
2695335053 t3_a_b_idx a 20 0 8 1 0 -1
733733
2695335053 t3_a_b_idx b 20 0 8 2 0 -1
734+
2695335053 t3_a_b_idx c 25 0 -1 3 0 -1
734735
115 v1 p 701 0 8 1 0 -1
735736
115 v1 a 20 0 8 2 0 -1
736737
115 v1 b 20 0 8 3 0 -1
@@ -739,26 +740,26 @@ attrelid relname attname atttypid attstattarget
739740
116 t4 b 701 0 8 2 0 -1
740741
116 t4 c 25 0 -1 3 0 -1
741742
116 t4 rowid 20 0 8 4 0 -1
742-
3214807592 t4_pkey rowid 20 0 8 4 0 -1
743+
3214807592 t4_pkey rowid 20 0 8 1 0 -1
743744
117 t5 a 20 0 8 1 0 -1
744745
117 t5 b 701 0 8 2 0 -1
745746
117 t5 c 25 0 -1 3 0 -1
746747
117 t5 rowid 20 0 8 4 0 -1
747-
1869730585 t5_pkey rowid 20 0 8 4 0 -1
748+
1869730585 t5_pkey rowid 20 0 8 1 0 -1
748749
120 t6 a 20 0 8 1 0 -1
749750
120 t6 b 20 0 8 2 0 -1
750751
120 t6 c 25 0 -1 3 0 -1
751752
120 t6 m 100118 0 -1 4 0 -1
752753
120 t6 rowid 20 0 8 6 0 -1
753-
2129466852 t6_pkey rowid 20 0 8 6 0 -1
754-
2129466855 t6_expr_idx crdb_internal_idx_expr 20 0 8 5 0 -1
755-
2129466854 t6_expr_expr1_idx crdb_internal_idx_expr_1 25 0 -1 7 0 -1
756-
2129466854 t6_expr_expr1_idx crdb_internal_idx_expr 20 0 8 5 0 -1
757-
2129466848 t6_expr_key crdb_internal_idx_expr_1 25 0 -1 7 0 -1
758-
2129466850 t6_expr_idx1 crdb_internal_idx_expr_2 16 0 1 8 0 -1
754+
2129466852 t6_pkey rowid 20 0 8 1 0 -1
755+
2129466855 t6_expr_idx crdb_internal_idx_expr 20 0 8 1 0 -1
756+
2129466854 t6_expr_expr1_idx crdb_internal_idx_expr_1 25 0 -1 1 0 -1
757+
2129466854 t6_expr_expr1_idx crdb_internal_idx_expr 20 0 8 2 0 -1
758+
2129466848 t6_expr_key crdb_internal_idx_expr_1 25 0 -1 1 0 -1
759+
2129466850 t6_expr_idx1 crdb_internal_idx_expr_2 16 0 1 1 0 -1
759760
121 mv1 ?column? 20 0 8 1 0 -1
760761
121 mv1 rowid 20 0 8 2 0 -1
761-
784389845 mv1_pkey rowid 20 0 8 2 0 -1
762+
784389845 mv1_pkey rowid 20 0 8 1 0 -1
762763

763764
query TTIBTTBBTT colnames,rowsort
764765
SELECT c.relname, attname, atttypmod, attbyval, attstorage, attalign, attnotnull, atthasdef, attidentity, attgenerated
@@ -800,6 +801,7 @@ t3 rowid -1 NULL NULL NU
800801
t3_pkey rowid -1 NULL NULL NULL true true · ·
801802
t3_a_b_idx a -1 NULL NULL NULL false false · ·
802803
t3_a_b_idx b -1 NULL NULL NULL false false · ·
804+
t3_a_b_idx c -1 NULL NULL NULL false true · ·
803805
v1 p -1 NULL NULL NULL false false · ·
804806
v1 a -1 NULL NULL NULL false false · ·
805807
v1 b -1 NULL NULL NULL false false · ·
@@ -869,6 +871,7 @@ t3 rowid false true 0
869871
t3_pkey rowid false true 0 NULL NULL NULL
870872
t3_a_b_idx a false true 0 NULL NULL NULL
871873
t3_a_b_idx b false true 0 NULL NULL NULL
874+
t3_a_b_idx c false true 0 NULL NULL NULL
872875
v1 p false true 0 NULL NULL NULL
873876
v1 a false true 0 NULL NULL NULL
874877
v1 b false true 0 NULL NULL NULL
@@ -958,6 +961,7 @@ t6_expr_expr1_idx crdb_internal_idx_expr_1 text 3403232968 default
958961
t6 c text 3403232968 default
959962
t5 c text 3403232968 default
960963
t4 c text 3403232968 default
964+
t3_a_b_idx c text 3403232968 default
961965
t3 c text 3403232968 default
962966
t1 j char 3403232968 default
963967
t1 i _varchar 3403232968 default
@@ -6053,3 +6057,38 @@ COMMIT;
60536057

60546058
statement ok
60556059
DROP TABLE t;
6060+
6061+
## Check key columns for indexes PG_Attribute
6062+
statement ok
6063+
CREATE TABLE t1(x int, y int primary key);
6064+
6065+
statement ok
6066+
CREATE TABLE t2(x int, y int);
6067+
6068+
statement ok
6069+
CREATE TABLE t3(x int, y int);
6070+
6071+
statement ok
6072+
CREATE INDEX t3idx ON t3(x) include(y);
6073+
6074+
query IT colnames
6075+
select attnum, attname from pg_attribute a join pg_class c on c.oid = a.attrelid where c.relname = 't1_pkey'
6076+
order by attnum;
6077+
----
6078+
attnum attname
6079+
1 y
6080+
6081+
query IT colnames
6082+
select attnum, attname from pg_attribute a join pg_class c on c.oid = a.attrelid where c.relname = 't2_pkey'
6083+
order by attnum;
6084+
----
6085+
attnum attname
6086+
1 rowid
6087+
6088+
query IT colnames
6089+
select attnum, attname from pg_attribute a join pg_class c on c.oid = a.attrelid where c.relname = 't3idx'
6090+
order by attnum;
6091+
----
6092+
attnum attname
6093+
1 x
6094+
2 y

‎pkg/sql/pg_catalog.go‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -467,11 +467,27 @@ https://www.postgresql.org/docs/12/catalog-pg-attribute.html`,
467467
// Columns for each index.
468468
columnIdxMap := catalog.ColumnIDToOrdinalMap(table.PublicColumns())
469469
return catalog.ForEachIndex(table, catalog.IndexOpts{}, func(index catalog.Index) error {
470+
idxID := h.IndexOid(table.GetID(), index.GetID())
471+
470472
for i := 0; i < index.NumKeyColumns(); i++ {
471473
colID := index.GetKeyColumnID(i)
472-
idxID := h.IndexOid(table.GetID(), index.GetID())
473474
column := table.PublicColumns()[columnIdxMap.GetDefault(colID)]
474-
if err := addColumn(column, idxID, uint32(column.GetPGAttributeNum())); err != nil {
475+
// The attnum for columns in an index is the order it appears in the
476+
// index definition and is not related to the attnum the column has in
477+
// the table.
478+
if err := addColumn(column, idxID, uint32(i+1)); err != nil {
479+
return err
480+
}
481+
}
482+
// pg_attribute only includes stored columns for secondary indexes, not
483+
// for primary indexes
484+
for i := 0; i < index.NumSecondaryStoredColumns(); i++ {
485+
colID := index.GetStoredColumnID(i)
486+
column := table.PublicColumns()[columnIdxMap.GetDefault(colID)]
487+
// The attnum for columns in an index is the order it appears in the
488+
// index definition and is not related to the attnum the column has in
489+
// the table.
490+
if err := addColumn(column, idxID, uint32(i+1+index.NumKeyColumns())); err != nil {
475491
return err
476492
}
477493
}

0 commit comments

Comments
 (0)