From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jonathan S. Katz <jkatz@postgresql.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Large expressions in indexes can't be stored (non-TOASTable)
Date: Wed, 18 Sep 2024 14:52:53 -0500
Message-ID: <ZusvlZBrPKm913LA@nathan> (raw)
In-Reply-To: <1812111.1726671296@sss.pgh.pa.us>
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>
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
Attachments:test.patch (text/plain, 2.0K) downloaddiff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.outindex 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.sqlindex 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:
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-hackers@postgresql.org
Cc: nathandbossart@gmail.com, tgl@sss.pgh.pa.us, jkatz@postgresql.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Large expressions in indexes can't be stored (non-TOASTable)
In-Reply-To: <ZusvlZBrPKm913LA@nathan>
* 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