Re: pg_dump: SQL command failed

Hi,

*REPLICATE*

0. Create a new database (*superdatabase*)
1. Create a new schema (*superschema*)
2. Add the unaccent extension to the schema:
CREATE EXTENSION unaccent;
3. Create a wrapper for unaccent that exposes an *IMMUTABLE* interface
(this is side issue):

CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
RETURNS text AS
$BODY$
-- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
SELECT unaccent($1);
$BODY$
LANGUAGE sql IMMUTABLE
COST 1;

4. Dump the schema using pg_dump:

pg_dump -n *superschema* --inserts *superdatabase* > superduper.sql

*EXPECTED RESULTS*

A file named superduper.sql with all the SQL statements needed to recreate
a fresh copy of *superschema* within *superdatabase* in another PostgreSQL
instance running on another server.

*ACTUAL RESULTS*

Failure:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: function unaccent(text) does
not exist
LINE 3: SELECT unaccent($1);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:
-- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
SELECT unaccent($1);

CONTEXT: SQL function "unaccent_text" during inlining

Shouldn't need to typecast, just dump the function's text to a file without
trying to inline it or otherwise interpret it. The function works when the
application is running, so it should also export using pg_dump without
quibbles.

For what it's worth, adding typecasts didn't help:

SELECT unaccent($1::text)::text;

Kind regards,
Dave

Hi,

*WORKAROUND*

Until this is fixed, here is a workaround:

1. Comment out the following line:

SELECT unaccent($1);

2. Change the code to:

SELECT $1;

3. Run pg_dump as before.

4. Remember to reinstate the old code. ;-)

Works as expected.

Thank you!

Dave

On Sun, May 13, 2012 at 7:46 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:

> Hi,
>
> *REPLICATE*
>
> 0. Create a new database (*superdatabase*)
> 1. Create a new schema (*superschema*)
> 2. Add the unaccent extension to the schema:
> CREATE EXTENSION unaccent;
> 3. Create a wrapper for unaccent that exposes an *IMMUTABLE* interface
> (this is side issue):
>
> CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
> RETURNS text AS
> $BODY$
> -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
> SELECT unaccent($1);
> $BODY$
> LANGUAGE sql IMMUTABLE
> COST 1;
>
> 4. Dump the schema using pg_dump:
>
> pg_dump -n *superschema* --inserts *superdatabase* > superduper.sql
>
> *EXPECTED RESULTS*
>
> A file named superduper.sql with all the SQL statements needed to recreate
> a fresh copy of *superschema* within *superdatabase* in another
> PostgreSQL instance running on another server.
>
> *ACTUAL RESULTS*
>
> Failure:
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: function unaccent(text) does
> not exist
> LINE 3: SELECT unaccent($1);
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY:
> -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
> SELECT unaccent($1);
>
> CONTEXT: SQL function "unaccent_text" during inlining
>
> Shouldn't need to typecast, just dump the function's text to a file
> without trying to inline it or otherwise interpret it. The function works
> when the application is running, so it should also export using pg_dump
> without quibbles.
>
> For what it's worth, adding typecasts didn't help:
>
> SELECT unaccent($1::text)::text;
>
> Kind regards,
> Dave
>

Thangalin <thangalin(at)gmail(dot)com> writes:
> 4. Dump the schema using pg_dump:
> pg_dump -n *superschema* --inserts *superdatabase* > superduper.sql

This does not dump the extension, because the extension is not within
the schema "superschema". (It definitely isn't given your creation
command, but pg_dump doesn't consider that extensions are inside schemas
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.

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.

regards, tom lane

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

On Sun, May 13, 2012 at 10:46 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:
> Hi,
>
> REPLICATE
>
> 0. Create a new database (superdatabase)
> 1. Create a new schema (superschema)
> 2. Add the unaccent extension to the schema:
> CREATE EXTENSION unaccent;
> 3. Create a wrapper for unaccent that exposes an IMMUTABLE interface (this
> is side issue):
>
> CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
>   RETURNS text AS
> $BODY$
>   -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
>   SELECT unaccent($1);
> $BODY$
>   LANGUAGE sql IMMUTABLE
>   COST 1;
>
> 4. Dump the schema using pg_dump:
>
> pg_dump -n superschema --inserts superdatabase > superduper.sql

I just tried this exact series of steps and it worked for me. What
version are you using?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi, Robert.

$ psql --version
psql (PostgreSQL) 9.1.2

D J

On Tue, May 22, 2012 at 12:55 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Sun, May 13, 2012 at 10:46 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:
> > Hi,
> >
> > REPLICATE
> >
> > 0. Create a new database (superdatabase)
> > 1. Create a new schema (superschema)
> > 2. Add the unaccent extension to the schema:
> > CREATE EXTENSION unaccent;
> > 3. Create a wrapper for unaccent that exposes an IMMUTABLE interface
> (this
> > is side issue):
> >
> > CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
> > RETURNS text AS
> > $BODY$
> > -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
> > SELECT unaccent($1);
> > $BODY$
> > LANGUAGE sql IMMUTABLE
> > COST 1;
> >
> > 4. Dump the schema using pg_dump:
> >
> > pg_dump -n superschema --inserts superdatabase > superduper.sql
>
> I just tried this exact series of steps and it worked for me. What
> version are you using?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

On Thu, May 24, 2012 at 8:37 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:
> Hi, Robert.
>
> $ psql --version
> psql (PostgreSQL) 9.1.2
>
> D J

[rhaas ~]$ createdb superdatabase
[rhaas ~]$ psql superdatabase
Line style is old-ascii.
psql (9.1.2)
Type "help" for help.

superdatabase=# create schema superschema;
CREATE SCHEMA
superdatabase=# CREATE EXTENSION unaccent;
CREATE EXTENSION
superdatabase=# CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
superdatabase-# RETURNS text AS
superdatabase-# $BODY$
superdatabase$# -- unaccent is STABLE, but the indexes must use
IMMUTABLE functions.
superdatabase$# SELECT unaccent($1);
superdatabase$# $BODY$
superdatabase-# LANGUAGE sql IMMUTABLE
superdatabase-# COST 1;
CREATE FUNCTION
superdatabase=# \q
[rhaas ~]$ pg_dump -n superschema --inserts superdatabase > superduper.sql

I tried a few other combinations, but I can't reproduce an error in
pg_dump no matter what I try. Maybe you're leaving out a step or two?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company