Proposal - Enabling btree_gist by default
That btree_gist is key to making exclusion constraints useful, which
creates a case for it to be enabled by default, came up at the extensions
summit at PGConf.dev last year. As part of mopping up my todo for last
year, I'd like to present the idea for review with a WIP patch.
0002 is the minimal enabling of the extension in initdb and adapting the
test to deal with the extension already existing
0001 is a preparatory patch which resets the search path after creating the
information schema in initdb, so btree_gist gets created in the right
place. The RESET may not be the best approach, maybe these steps need to be
wrapped in transactions so that SET LOCAL can be used in the scripts?
There is more cleanup/adaptation I need to do on the test side - having
btree_gist in the initdb image breaks various things including the type and
operator sanity checks.
Before wading into all of that - what is the view on enabling btree_gist by
default in initdb?
Thanks
Alastair
Attachments:
0001-Reset-search_path-after-initialising-information_sch.patchtext/x-patch; charset=US-ASCII; name=0001-Reset-search_path-after-initialising-information_sch.patchDownload
From 397ccd2bad7f5be30209f75ce48177892cb6dbf5 Mon Sep 17 00:00:00 2001
From: Alastair Turner <minion@decodable.me>
Date: Mon, 5 Jan 2026 00:04:14 +0000
Subject: [PATCH 1/2] Reset search_path after initialising information_schema
---
src/backend/catalog/information_schema.sql | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 49adf66ba9b..8f3fc17b1a1 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -3009,3 +3009,5 @@ CREATE VIEW user_mappings AS
FROM _pg_user_mappings;
GRANT SELECT ON user_mappings TO PUBLIC;
+
+RESET search_path;
--
2.34.1
0002-Enable-btree_gist-by-default.patchtext/x-patch; charset=US-ASCII; name=0002-Enable-btree_gist-by-default.patchDownload
From 823ebe93e3ed7752f9acb666349d1d09c20ccbf2 Mon Sep 17 00:00:00 2001
From: Alastair Turner <minion@decodable.me>
Date: Mon, 5 Jan 2026 00:34:24 +0000
Subject: [PATCH 2/2] Enable btree_gist by default
---
contrib/btree_gist/expected/init.out | 3 ++-
contrib/btree_gist/sql/init.sql | 2 +-
src/bin/initdb/initdb.c | 12 ++++++++++++
3 files changed, 15 insertions(+), 2 deletions(-)
diff --git a/contrib/btree_gist/expected/init.out b/contrib/btree_gist/expected/init.out
index ce4559d8b03..ef63edfc0f8 100644
--- a/contrib/btree_gist/expected/init.out
+++ b/contrib/btree_gist/expected/init.out
@@ -1,4 +1,5 @@
-CREATE EXTENSION btree_gist;
+CREATE EXTENSION IF NOT EXISTS btree_gist;
+NOTICE: extension "btree_gist" already exists, skipping
-- Check whether any of our opclasses fail amvalidate
SELECT amname, opcname
FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
diff --git a/contrib/btree_gist/sql/init.sql b/contrib/btree_gist/sql/init.sql
index a6d2cffc677..14c6fd1fe6c 100644
--- a/contrib/btree_gist/sql/init.sql
+++ b/contrib/btree_gist/sql/init.sql
@@ -1,4 +1,4 @@
-CREATE EXTENSION btree_gist;
+CREATE EXTENSION IF NOT EXISTS btree_gist;
-- Check whether any of our opclasses fail amvalidate
SELECT amname, opcname
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 51b725e709c..f8789b23b95 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -289,6 +289,7 @@ static void setup_privileges(FILE *cmdfd);
static void set_info_version(void);
static void setup_schema(FILE *cmdfd);
static void load_plpgsql(FILE *cmdfd);
+static void load_btree_gist(FILE *cmdfd);
static void vacuum_db(FILE *cmdfd);
static void make_template0(FILE *cmdfd);
static void make_postgres(FILE *cmdfd);
@@ -1991,6 +1992,15 @@ load_plpgsql(FILE *cmdfd)
PG_CMD_PUTS("CREATE EXTENSION plpgsql;\n\n");
}
+/*
+ * load btree_gist index opclasses to enable exlusion constraints
+ */
+static void
+load_btree_gist(FILE *cmdfd)
+{
+ PG_CMD_PUTS("CREATE EXTENSION btree_gist;\n\n");
+}
+
/*
* clean everything up in template1
*/
@@ -3138,6 +3148,8 @@ initialize_data_directory(void)
load_plpgsql(cmdfd);
+ load_btree_gist(cmdfd);
+
vacuum_db(cmdfd);
make_template0(cmdfd);
--
2.34.1
Alastair Turner <minion@decodable.me> writes:
Before wading into all of that - what is the view on enabling btree_gist by
default in initdb?
Absolutely, positively not until we've dealt with the inet mess:
/messages/by-id/2483812.1754072263@sss.pgh.pa.us
Even once that dust settles, I'm not sure that "install the extension
by default" is an acceptable approach. In the past we've usually
preferred to migrate functionality into core.
regards, tom lane
I wrote:
Even once that dust settles, I'm not sure that "install the extension
by default" is an acceptable approach.
After reflecting a bit, the key problem with that is it'd break
pg_upgrade of an existing cluster that has the extension installed
normally. pg_upgrade needs to preserve OIDs of data types and some
other SQL objects, and there's no way that a pre-installed extension
would happen to match up with the OIDs the extension used before.
Maybe there's some way we could finesse that, relying on the
assumption that gbtreekey4 and siblings probably aren't being
used in user tables so their OIDs wouldn't appear on disk.
But it seems quite nontrivial to do, even if said assumption
is safe.
regards, tom lane
On Mon, 5 Jan 2026 at 01:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
Even once that dust settles, I'm not sure that "install the extension
by default" is an acceptable approach.After reflecting a bit, the key problem with that is it'd break
pg_upgrade of an existing cluster that has the extension installed
normally. pg_upgrade needs to preserve OIDs of data types and some
other SQL objects, and there's no way that a pre-installed extension
would happen to match up with the OIDs the extension used before.Maybe there's some way we could finesse that, relying on the
assumption that gbtreekey4 and siblings probably aren't being
used in user tables so their OIDs wouldn't appear on disk.
But it seems quite nontrivial to do, even if said assumption
is safe.Thanks for the feedback, Tom.
I expect the same complication would exist for anything which
moved functionality including data types into core. Which reinforces the
point for me that extensions and upgrades is the yak that needs to be
shaved before a lot of the discussions about where functionality should be
maintained can be acted on.
I'll strip out the preparatory fix for cleaning up search path settings in
initdb scripts into a separate thread.
FWIW, the discussion which led to proposing enabling the extension by
default had started off as a discussion of what from the contrib extension
world it would be desirable to move into core. Enabling an extension by
default (with PL/pgSQL as an almost-precedent) was seen as a preferable
alternative because:
- It's a less intrusive change (which this thread may have invalidated)
- It keeps the relevant extension points and APIs in use by something
which goes through CI
- By providing that clearer boundary, it may (an admittedly very
speculative may) help to scale the maintenance effort horizontally
Regards
Alastair