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
From 397ccd2bad7f5be30209f75ce48177892cb6dbf5 Mon Sep 17 00:00:00 2001 From: Alastair Turner <[email protected]> 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
From 823ebe93e3ed7752f9acb666349d1d09c20ccbf2 Mon Sep 17 00:00:00 2001 From: Alastair Turner <[email protected]> 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
