Restoring a db dump with tsearch fields fails

Which version of pgsql? which version of tsearch?

I had similar problems and emailed the creators of tsearch and they
provided the fix. I believe it was later integrated back into the distro.

If you check the lines where those errors occur, I bet it is missing a
single or double quote around those word.

On Fri, 8 Nov 2002, Robert John Shepherd wrote:

Bit of a problem here, am regularly backing up my database with
pg_dumpall, which works fine.

However when I attempt a restore on another box, things go rather
pearshaped, and I'm pretty sure this is because I am using tsearch from
the contrib directory.

I've tried adding tsearch.sql to the template1 db, in case that helped,
which it didn't. :/

I get lots of these during the restore:
ERROR: parser: parse error at or near "ikebe"
ERROR: parser: parse error at or near "'"
ERROR: parser: parse error at or near "ne"
ERROR: parser: parse error at or near "'"
ERROR: parser: parse error at or near "ad"
ERROR: parser: parse error at or near "phane"
ERROR: parser: parse error at or near "rgaard"
ERROR: parser: parse error at or near "gnes"
ERROR: parser: parse error at or near "e"
ERROR: parser: parse error at or near "otis"
ERROR: parser: parse error at or near "gis"
ERROR: parser: parse error at or near "rald"
Query buffer reset (cleared).
invalid command \n
Query buffer reset (cleared).
invalid command \n<p
Query buffer reset (cleared).
invalid command \n<p
Query buffer reset (cleared).
invalid command \n<p
Query buffer reset (cleared).

I'm presuming all of these relate to the way pg_dump creates scripts for
the tsearch fields.

This could make restoring my database rather tricky if I ever have to do
this for real. :o

Anyone have any suggestions?

Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Thomas T. Thai
Minnesota.com, Inc.

Which version of pgsql? which version of tsearch?

7.2.3, and whatever ver of tsearch is included in the contrib dir for
that.

I had similar problems and emailed the creators of tsearch and they
provided the fix. I believe it was later integrated back into
the distro.

Don't suppose you still have that lying around? :)

If you check the lines where those errors occur, I bet it is missing a
single or double quote around those word.

Not sure it is that, but it could be I guess. From looking at the source
there are single quotes around all the tsearch words.

I've also tried dumping the database with:

pg_dump -Fc -b -c reviewer > reviewer.tar

But when I restore using:

pg_restore -Fc -C -d template1 reviewer.tar

I get the following error:

pg_restore: [archiver (db)] could not execute query: ERROR: Unable
to locate type name 'txtidx' in catalog

I've tried with and without including all the tsearch objects in
template1, when I have included them I just get a load of other errors
because the objects already exist.

Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk

Hi All,
I know how to get column names from tables:

select attname from pg_attribute where attrelid=(select oid from
pg_class where relname='table1');

but it does not work for temporary tables, because when creating it gets a system name like 'pg_temp_6410_1'

Is there any solution for it?
Thanks, Joseph

"Robert John Shepherd" <robert@reviewer.co.uk> writes:

I get the following error:
pg_restore: [archiver (db)] could not execute query: ERROR: Unable
to locate type name 'txtidx' in catalog

IIRC, there is some issue with tsearch that makes it likely to fool
pg_dump into outputting items in the wrong order (ie, you may have
tables with txtidx columns appearing before the type txtidx is defined).
The short-term workaround is to manually fix the load order. With a
text dump file you'd just hack away at it with a text editor. With a
tar dump file, you need to use pg_restore's options that control the
load order. I've never done this and can't offer any advice beyond
"read the man page" ...

regards, tom lane

"Kabai J�zsef" <kabai@audiobox.hu> writes:

I know how to get column names from tables:
select attname from pg_attribute where attrelid=(select oid from
pg_class where relname='table1');
but it does not work for temporary tables, because when creating it gets a system name like 'pg_temp_6410_1'

There is no good solution in pre-7.3 releases, because the mapping from
logical temp table name to actual table name is hidden inside the
backend.

In 7.3 temp tables actually have their user-given names. (They don't
conflict with regular tables because they're in a different schema.)
This moves the problem from "how do I find the temp table name" to "how
do I find the temp schema name" --- but there are several possible
answers to that. One nice way is to bypass the problem by using the
new regclass datatype:

select attname from pg_attribute where attrelid = 'table1'::regclass;

The regclass conversion produces essentially the same effect as your
subselect, ie, it gets the OID of table1 ... but the regclass input
converter uses your schema search path, so it will find the temp table
named 'table1' in preference to any other 'table1'.

So, come help beta-test 7.3 ... ;-)

regards, tom lane