Skip site navigation (1) Skip section navigation (2)

Re: pg_dump: SQL command failed

From: Thangalin <thangalin(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_dump: SQL command failed
Date: 2012-05-15 04:44:54
Message-ID: CAANrE7qNut_baKrRUYNLGu7ND43kOgHZBByy6B_EGnB3AEF_KQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Ibrahim, Karim Aly Mohi EldinDate: 2012-05-15 04:56:34
Subject: Error with refering to the header files
Previous:From: iain.daltonDate: 2012-05-14 21:41:59
Subject: BUG #6639: Manual uses boldface where it says italic,and monospace where it says boldface

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group