Subject: Add <caution> for ALTER TEXT SEARCH CONFIGURATION

From: Jeff Janes <[email-masked]>
Date: Fri, 15 Nov 2019 14:09:59 -0500

If you alter one of the built-in text search configurations, the
modifications to it will not get dumped by pg_dump, and thus won't get
propagated by pg_upgrade leading to silent behavior changes in the new
cluster (as well in any other type of restoration from pg_dump output)

I would say it is bad practise to alter one of those anyway, rather than
copy and alter the copy.  But I wasn't aware until now of just how bad of
an idea it was.  I think that this needs to be warned about in the docs
for ALTER TEXT SEARCH CONFIGURATION.  If I were monkeying around in
$SHAREDLIB/tsearch_data, I'd expect traps like this, but not when executing
things from SQL after reading the docs on the command.

Cheers,

Jeff

---

From: Euler Taveira <[email-masked]>
Date: Mon, 25 Nov 2019 10:51:23 -0300

Em sex., 15 de nov. de 2019 às 16:10, Jeff Janes
<[email-masked]> escreveu:
>
> If you alter one of the built-in text search configurations, the modifications to it will not get dumped by pg_dump, and thus won't get propagated by pg_upgrade leading to silent behavior changes in the new cluster (as well in any other type of restoration from pg_dump output)
>
It was a bad design to allow changes in builtin text search objects.
User expects that all modifications should be propagated while dumping
a cluster.

Since pg_ts_config_map does not have an oid column, it is not easy to
guess that a text search configuration was modified (unless we dump
the initial table and compare row-by-row). We could disallow changes
in builtin text search objects but it could potentially break some
scenarios. Even if it breaks a scenario, a workaround with another
user-defined text search configuration is possible.

> I would say it is bad practise to alter one of those anyway, rather than copy and alter the copy.  But I wasn't aware until now of just how bad of an idea it was.  I think that this needs to be warned about in the docs for ALTER TEXT SEARCH CONFIGURATION.  If I were monkeying around in $SHAREDLIB/tsearch_data, I'd expect traps like this, but not when executing things from SQL after reading the docs on the command.
>
Let's start with a tangible idea: add a big "caution" and also a
WARNING while ALTER TEXT SEARCH CONFIGURATION whose schema is
pg_catalog.

-- 
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

---

From: Tom Lane <[email-masked]>
Date: Mon, 25 Nov 2019 10:16:59 -0500

>> If you alter one of the built-in text search configurations, the modifications to it will not get dumped by pg_dump, and thus won't get propagated by pg_upgrade leading to silent behavior changes…

This isn't really different from what happens if you alter any object
defined by initdb.   (There are limited exceptions now for GRANT/REVOKE,
but not for any other object property.)

> It was a bad design to allow changes in builtin text search objects.

I disagree.  It is reasonable to point out that if you do that,
propagating your changes to new databases is your problem.  But
the fact that you can mess with built-in objects has always been
seen as a feature not a bug, and I'm not willing to change that
approach, nor to start plastering random man pages with bright
yellow cautions against doing so.  Having the code itself issue
complaints is right out.

			regards, tom lane