Date: Wed, 18 Sep 2024 14:52:53 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Jonathan S. Katz" <jkatz@postgresql.org>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Large expressions in indexes can't be stored (non-TOASTable)
Message-ID: <ZusvlZBrPKm913LA@nathan>
References: <b611015f-b423-458c-aa2d-be0e655cc1b4@postgresql.org>
 <ZtipsVMZAGjrO1p_@nathan>
 <1679756.1725476893@sss.pgh.pa.us>
 <c500a379-7a20-4ab4-bcc9-5ea9aabe017a@postgresql.org>
 <ZuroEfWjxd4Zj6Yq@nathan>
 <1812111.1726671296@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="XPnlhGfhBsHUsLKx"
Content-Disposition: inline
In-Reply-To: <1812111.1726671296@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/ZusvlZBrPKm913LA%40nathan>
Precedence: bulk


--XPnlhGfhBsHUsLKx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Sep 18, 2024 at 10:54:56AM -0400, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
>> Any objections to committing this?
> 
> Nope.

Committed.  I waffled on whether to add a test for system indexes that used
pg_index's varlena columns, but I ended up leaving it out.  I've attached
it here in case anyone thinks we should add it.

-- 
nathan

--XPnlhGfhBsHUsLKx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="test.patch"

diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index 45e7492877..2af66cf648 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -60,6 +60,31 @@ ORDER BY 1, 2;
  pg_largeobject_metadata | lomacl        | aclitem[]
 (9 rows)
 
+-- **************** pg_index ****************
+-- Look for system indexes that use varlena columns in pg_index.  There
+-- shouldn't be any to avoid circularity hazards with pg_index's TOAST table.
+--
+-- The first query returns the varlena columns in pg_index.  Each should be
+-- listed in the second query's WHERE clause.
+SELECT attname
+FROM pg_attribute
+WHERE attstorage != 'p' AND
+      attrelid = 'pg_catalog.pg_index'::regclass
+ORDER BY 1;
+ attname  
+----------
+ indexprs
+ indpred
+(2 rows)
+
+SELECT relname
+FROM pg_class c JOIN pg_index i ON indexrelid = c.oid
+WHERE c.oid < 16384 AND
+      (i.indexprs IS NOT NULL OR i.indpred IS NOT NULL);
+ relname 
+---------
+(0 rows)
+
 -- system catalogs without primary keys
 --
 -- Current exceptions:
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 16f3a7c0c1..3e87c50b84 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -53,6 +53,26 @@ WHERE c.oid < 16384 AND
 ORDER BY 1, 2;
 
 
+-- **************** pg_index ****************
+
+-- Look for system indexes that use varlena columns in pg_index.  There
+-- shouldn't be any to avoid circularity hazards with pg_index's TOAST table.
+--
+-- The first query returns the varlena columns in pg_index.  Each should be
+-- listed in the second query's WHERE clause.
+
+SELECT attname
+FROM pg_attribute
+WHERE attstorage != 'p' AND
+      attrelid = 'pg_catalog.pg_index'::regclass
+ORDER BY 1;
+
+SELECT relname
+FROM pg_class c JOIN pg_index i ON indexrelid = c.oid
+WHERE c.oid < 16384 AND
+      (i.indexprs IS NOT NULL OR i.indpred IS NOT NULL);
+
+
 -- system catalogs without primary keys
 --
 -- Current exceptions:

--XPnlhGfhBsHUsLKx--