pg_upgrade fails with missing FTS resources

On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote:

Hello,

pg_upgrade fails on missing FTS dictionaries requiring external files.
--check fails to detect the incompatibility.

E.g. after installed italian_fts (http://pgxn.org/dist/italian_fts/)
into a database, the pg_upgrade_dump_db.sql contains the command:

CREATE TEXT SEARCH DICTIONARY italian_ispell (
TEMPLATE = pg_catalog.ispell,
dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords
= 'italian_ispell' );

that fails with a:

psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:56891: ERROR: could
not open dictionary file
"/usr/share/postgresql/9.1/tsearch_data/italian_ispell.dict": No such
file or directory

I think it would be enough to detect the incompatibility at --check
time, as much as it's done for the required libraries, and only for
the builtin dict types.

If you want the issue to be fixed I can try to fix it myself: it seems
easy enough.

Sure, we can test for that. What system column stores these file names?
Do we have any other file references in our system tables?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote:

pg_upgrade fails on missing FTS dictionaries requiring external files.
--check fails to detect the incompatibility.

Sure, we can test for that. What system column stores these file names?

Trying to check that on the basis of the system catalog entries seems
quite hopeless. What's in the catalogs is essentially what appears
in the CREATE command, ie

dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = 'italian_ispell'

The dictionary template knows which of these options are file names and
which are not, and what filename extension to apply to each option that
is a file name; but you don't.

The closest that I think pg_upgrade could reasonably come is to compare
the contents of the old and new installations' tsearch_data/
directories, and complain about any files present in the former and not
the latter. However, that method seems fraught with opportunities for
false positives: in particular you might complain about some file that
was in the old installation but not actually referenced by any text
search catalog entry.

In the end I'm not sure it's worth it. There are any number of ways
that the restore step can fail, and it's impossible for pg_upgrade
to pre-check for all of them.

regards, tom lane

On Fri, Jun 15, 2012 at 10:27:59PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote:

pg_upgrade fails on missing FTS dictionaries requiring external files.
--check fails to detect the incompatibility.

Sure, we can test for that. What system column stores these file names?

Trying to check that on the basis of the system catalog entries seems
quite hopeless. What's in the catalogs is essentially what appears
in the CREATE command, ie

dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = 'italian_ispell'

The dictionary template knows which of these options are file names and
which are not, and what filename extension to apply to each option that
is a file name; but you don't.

The closest that I think pg_upgrade could reasonably come is to compare
the contents of the old and new installations' tsearch_data/
directories, and complain about any files present in the former and not
the latter. However, that method seems fraught with opportunities for
false positives: in particular you might complain about some file that
was in the old installation but not actually referenced by any text
search catalog entry.

In the end I'm not sure it's worth it. There are any number of ways
that the restore step can fail, and it's impossible for pg_upgrade
to pre-check for all of them.

Agreed. Not worth it. If it is reported again, we can document this
requirement.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +