public inbox for [email protected]
help / color / mirror / Atom feedRe: Bug in CREATE TABLE .. LIKE .. INCLUDING STATISTICS?
2+ messages / 2 participants
[nested] [flat]
* Re: Bug in CREATE TABLE .. LIKE .. INCLUDING STATISTICS?
@ 2026-04-21 15:19 Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew Dunstan @ 2026-04-21 15:19 UTC (permalink / raw)
To: Srinath Reddy Sadipiralla <[email protected]>; Julien Tachoires <[email protected]>; [email protected]; +Cc: [email protected]
On 2026-04-16 Th 3:44 AM, Srinath Reddy Sadipiralla wrote:
> Hi Julien,
>
> On Wed, Apr 15, 2026 at 7:47 PM Julien Tachoires <[email protected]>
> wrote:
>
> Hi,
>
> One of our customer is experiencing an issue when executing CREATE
> TABLE
> .. LIKE .. INCLUDING ALL; on 14, the following kind of error happens:
> ERROR: cache lookup failed for attribute X of relation ZZZZZZ
>
> It seems to come from generateClonedExtStatsStmt(): get_attname()
> appears to be called with an attribute number (attnum) that does not
> exist.
>
>
> yeah, i was able to reproduce and also check the flow which is the same
> as you mentioned.
>
>
> Please find attached 2 patches for the master branch, the first
> one adds
> a test that triggers the problem, the 2nd one is an attempt to fix it.
>
>
> I think it's better to write a 4 column test, with this it covers both
> cases of lookup either returning the wrong column name or errors
> out when the attnum does not exist in the child, thoughts?
>
> something like this ....
>
> CREATE TABLE parent_like_stats (a int, b int, c int, d int);
> ALTER TABLE parent_like_stats DROP COLUMN b;
> CREATE STATISTICS s_parent ON a, c FROM parent_like_stats;
> CREATE TABLE child_like_stats (LIKE parent_like_stats INCLUDING
> STATISTICS);
>
> other than this patches LGTM.
>
>
>
OK, here's a version that does that in a combined patch, and adjusts the
function comments to mention we also remap the stxkeys. Releases before
18 will need a little adjustment in the test files.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
Attachments:
[text/x-patch] v2-0001-Fix-attnum-remapping-in-generateClonedExtStatsStm.patch (6.1K, 3-v2-0001-Fix-attnum-remapping-in-generateClonedExtStatsStm.patch)
download | inline diff:
From 25c8165b3a7eb38e2f4823170a9bf31d2d1031c9 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Tue, 21 Apr 2026 11:04:34 -0400
Subject: [PATCH v2] Fix attnum remapping in generateClonedExtStatsStmt()
When cloning extended statistics via CREATE TABLE ... LIKE ... INCLUDING
STATISTICS, stxkeys holds attribute numbers from the source (parent)
table, but get_attname() was being called with the child relation's
OID. If the parent has dropped columns, the child's attribute numbers
are renumbered sequentially and no longer match, so the lookup either
returns the wrong column name (silent corruption) or errors out when
the attnum does not exist in the child.
Fix it by remapping the parent attnum through attmap before the lookup,
consistent with how expression statistics are already handled a few
lines below.
Add a regression test covering both manifestations: a 3-column parent
where the stale attnum refers to no child column (cache-lookup error),
and a 4-column parent where the stale attnum silently refers to the
wrong child column.
Author: Julien Tachoires <[email protected]>
Reviewed-by: Srinath Reddy Sadipiralla <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 14
---
src/backend/parser/parse_utilcmd.c | 8 +++--
.../regress/expected/create_table_like.out | 31 +++++++++++++++++++
src/test/regress/sql/create_table_like.sql | 26 ++++++++++++++++
3 files changed, 63 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 37071502a9f..f7ae6ef229d 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2042,7 +2042,10 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
* extended statistic "source_statsid", for the rel identified by heapRel and
* heapRelid.
*
- * Attribute numbers in expression Vars are adjusted according to attmap.
+ * stxkeys in the source statistic holds attribute numbers from the parent
+ * relation. Those attnums, along with the attribute numbers referenced by
+ * Vars inside the expression tree, are remapped to the new relation's
+ * numbering according to attmap.
*/
static CreateStatsStmt *
generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
@@ -2100,7 +2103,8 @@ generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
StatsElem *selem = makeNode(StatsElem);
AttrNumber attnum = statsrec->stxkeys.values[i];
- selem->name = get_attname(heapRelid, attnum, false);
+ selem->name =
+ get_attname(heapRelid, attmap->attnums[attnum - 1], false);
selem->expr = NULL;
def_names = lappend(def_names, selem);
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 5720d160f05..76069bde756 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -698,6 +698,37 @@ SELECT attname, attcompression FROM pg_attribute
e |
(5 rows)
+-- LIKE ... INCLUDING STATISTICS with dropped columns in the parent,
+-- so stxkeys attnums are not contiguous.
+CREATE TABLE ctl_stats3_parent (a int, b int, c int);
+ALTER TABLE ctl_stats3_parent DROP COLUMN b;
+CREATE STATISTICS ctl_stats3_stat ON a, c FROM ctl_stats3_parent;
+CREATE TABLE ctl_stats3_child (LIKE ctl_stats3_parent INCLUDING STATISTICS);
+CREATE TABLE ctl_stats4_parent (a int, b int, c int, d int);
+ALTER TABLE ctl_stats4_parent DROP COLUMN b;
+CREATE STATISTICS ctl_stats4_stat ON a, c FROM ctl_stats4_parent;
+CREATE TABLE ctl_stats4_child (LIKE ctl_stats4_parent INCLUDING STATISTICS);
+SELECT s.stxrelid::regclass AS relation,
+ array_agg(a.attname ORDER BY u.ord) AS stats_columns
+FROM pg_statistic_ext s
+CROSS JOIN LATERAL
+ unnest(s.stxkeys::int2[]) WITH ORDINALITY AS u(attnum, ord)
+JOIN pg_attribute a
+ ON a.attrelid = s.stxrelid AND a.attnum = u.attnum
+WHERE s.stxrelid IN ('ctl_stats3_child'::regclass,
+ 'ctl_stats4_child'::regclass)
+GROUP BY s.stxrelid
+ORDER BY s.stxrelid::regclass::text;
+ relation | stats_columns
+------------------+---------------
+ ctl_stats3_child | {a,c}
+ ctl_stats4_child | {a,c}
+(2 rows)
+
+DROP TABLE ctl_stats3_parent;
+DROP TABLE ctl_stats3_child;
+DROP TABLE ctl_stats4_parent;
+DROP TABLE ctl_stats4_child;
DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..d52a93ef131 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -276,6 +276,32 @@ CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl
SELECT attname, attcompression FROM pg_attribute
WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum;
+-- LIKE ... INCLUDING STATISTICS with dropped columns in the parent,
+-- so stxkeys attnums are not contiguous.
+CREATE TABLE ctl_stats3_parent (a int, b int, c int);
+ALTER TABLE ctl_stats3_parent DROP COLUMN b;
+CREATE STATISTICS ctl_stats3_stat ON a, c FROM ctl_stats3_parent;
+CREATE TABLE ctl_stats3_child (LIKE ctl_stats3_parent INCLUDING STATISTICS);
+CREATE TABLE ctl_stats4_parent (a int, b int, c int, d int);
+ALTER TABLE ctl_stats4_parent DROP COLUMN b;
+CREATE STATISTICS ctl_stats4_stat ON a, c FROM ctl_stats4_parent;
+CREATE TABLE ctl_stats4_child (LIKE ctl_stats4_parent INCLUDING STATISTICS);
+SELECT s.stxrelid::regclass AS relation,
+ array_agg(a.attname ORDER BY u.ord) AS stats_columns
+FROM pg_statistic_ext s
+CROSS JOIN LATERAL
+ unnest(s.stxkeys::int2[]) WITH ORDINALITY AS u(attnum, ord)
+JOIN pg_attribute a
+ ON a.attrelid = s.stxrelid AND a.attnum = u.attnum
+WHERE s.stxrelid IN ('ctl_stats3_child'::regclass,
+ 'ctl_stats4_child'::regclass)
+GROUP BY s.stxrelid
+ORDER BY s.stxrelid::regclass::text;
+DROP TABLE ctl_stats3_parent;
+DROP TABLE ctl_stats3_child;
+DROP TABLE ctl_stats4_parent;
+DROP TABLE ctl_stats4_child;
+
DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
--
2.43.0
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bug in CREATE TABLE .. LIKE .. INCLUDING STATISTICS?
@ 2026-04-21 15:56 Srinath Reddy Sadipiralla <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Srinath Reddy Sadipiralla @ 2026-04-21 15:56 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Julien Tachoires <[email protected]>; [email protected]; [email protected]
Hi Andrew,
On Tue, Apr 21, 2026 at 8:49 PM Andrew Dunstan <[email protected]> wrote:
>
> OK, here's a version that does that in a combined patch, and adjusts the
> function comments to mention we also remap the stxkeys. Releases before 18
> will need a little adjustment in the test files.
>
Thanks for the patch , it LGTM.
--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-21 15:56 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-21 15:19 Re: Bug in CREATE TABLE .. LIKE .. INCLUDING STATISTICS? Andrew Dunstan <[email protected]>
2026-04-21 15:56 ` Srinath Reddy Sadipiralla <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox