[BUGS] 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
Re: [BUGS] pg_dump: SQL command failed
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 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 >
Re: [BUGS] pg_dump: SQL command failed
Thangalin 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
