Обсуждение: 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@gmail.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@gmail.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=E8me" 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@gmail.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) > =A0 RETURNS text AS > $BODY$ > =A0 -- unaccent is STABLE, but the indexes must use IMMUTABLE functions. > =A0 SELECT unaccent($1); > $BODY$ > =A0 LANGUAGE sql IMMUTABLE > =A0 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? --=20 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@gmail.com> wrote: > On Sun, May 13, 2012 at 10:46 PM, Thangalin <thangalin@gmail.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@gmail.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