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+2-1
0002-Enable-btree_gist-by-default.patchtext/x-patch; charset=US-ASCII; name=0002-Enable-btree_gist-by-default.patchDownload+15-3
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
On 1/8/26 11:14 AM, Alastair Turner wrote:
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
A not very realistic dream for me would be to move some things (mostly
the money type) out of core rather than moving moe stuff in to core, but
given pg_upgrade and OID stability I do not think that is realistic.
Andreas