Re: pg_dump: SQL command failed

Hi, Tom.

Thanks for the quick reply.

anyway, for the purposes of options such as "-n".) So on reload, the
> user function fails; it's referencing a function that doesn't exist
> in the new database. That's not a bug.
>

I'm probably not understanding something: I'm not importing anything into a
new database. I'm trying to dump an existing database that uses a couple of
extensions.

It is not intuitive that using extension functions cause pg_dump to fail.
(The pg_dump has no command to work-around the issue.) I think I understand
why this is (because the import into a new database would fail without the
requisite extension), but surely that should generate an error on *import*,
rather than on *export*?

What am I "reloading" when running pg_dump?

Also, pg_dump need not export the extension statement (although, that would
be a nice feature). The expected behaviour is that pg_dump should export a
valid database (to a text file). How else can I make a back-up?

What I take from this is that it is not possible to use pg_dump to dump a
database that uses extensions. That is what I believe to be a bug.

> BTW, the reason the unaccent function isn't marked immutable is that its
> behavior can be changed with ALTER TEXT DICTIONARY. This wrapper
> function doesn't eliminate that risk (in fact it adds some new ones),
> so it doesn't look very safe to me.
>

Thank you for the note! I'm using the following index:

CREATE INDEX unaccented_words_idx
ON superschema.table_name
USING gin
(superschema.unaccent_text(label::text) COLLATE pg_catalog."default"
gin_trgm_ops);

This was necessary so that an autocomplete field would match "creme" to
"Crème" when using the ~~ operator, for example:

SELECT id, label FROM superschema.table_name WHERE
superschema.unaccent_text(label) ~~ '%$search_term%' ORDER BY
similarity(label, '$search_term') DESC, label LIMIT 12

Took a few hours to get that to work. Would be nice to know if there's a
better way, without having to wrap the unaccent function.

Dave