Re: Problem with restoring dump (may be tsearch-related) - Mailing list pgsql-general

Hi!

Thanks for your help, I think I've got more facts now...

First: The creation of the article-table (and any table using the
txtidx-type) fails because of txtidx not being present at that time:

2002-09-06 10:48:59 [3995]   DEBUG:  query: CREATE TABLE "article" (
        "site_id" integer NOT NULL,
        [...]
        "textindex" txtidx);
2002-09-06 10:48:59 [3995]   ERROR:  Unable to locate type name 'txtidx'
in catalog

Unfortunately, new types, operators and functions are being declared
just after the tables are created, as you max see from the timestamp;
this bit here is what installs tsearch:

2002-09-06 10:48:59 [3995]   DEBUG:  query: CREATE OPERATOR ~@
(PROCEDURE = rexecqtxt ,
        LEFTARG = query_txt ,
        RIGHTARG = txtidx ,
        COMMUTATOR = @@ ,
        RESTRICT = contsel ,
        JOIN = contjoinsel );
[...]
2002-09-06 10:48:59 [3995]   DEBUG:  query: CREATE TYPE "txtidx" (
internallength = variable, externallength = variable, input =
"txtidx_in", output = "txtidx_out", send = "txtidx_out", receive =
"txtidx_in", alignment = int4, storage = extended);
2002-09-06 10:48:59 [3995]   DEBUG:  ProcessUtility: CREATE TYPE
"txtidx" ( internallength = variable, externallength = variable, input =
"txtidx_in", output = "txtidx_out", send = "txtidx_out", receive =
"txtidx_in", alignment = int4, storage = extended);
[...]
2002-09-06 10:48:59 [3995]   DEBUG:  ProcessUtility: CREATE FUNCTION
"gtxtidx_same" (gtxtidx,gtxtidx,opaque) RETURNS opaque AS
'$libdir/tsearch', 'gtxtidx_same' LANGUAGE 'C';

Now of course as soon as the data from article wants to be copied into
the table "article" (which isn't there), an error is thrown:

2002-09-06 10:49:01 [3995]   DEBUG:  query: COPY "article" FROM stdin;
2002-09-06 10:49:01 [3995]   DEBUG:  ProcessUtility: COPY "article" FROM
stdin;
2002-09-06 10:49:01 [3995]   ERROR:  Relation "article" does not exist

And everything else is, I think, just a result of "article" not being
present, as the subsequent lines are interpreted as queries instead of
data. This explains why there's a parse error for every semicolon - this
usually just signals the end of a query:

2002-09-06 10:49:01 [3995]   DEBUG:  query: 26    73091    1    News
no    184    15850    Vivendi Universal Interactive Publishing (DE)
MAIN_COMPANY    Vivendi: Pop-Trio Wonderwall leiht Malice ihre Stimmen
Die drei Sä
2002-09-06 10:49:01 [3995]   ERROR:  parser: parse error at or near "26"
2002-09-06 10:49:01 [3995]   DEBUG:  query: ngerinnen der populä
2002-09-06 10:49:01 [3995]   ERROR:  parser: parse error at or near
"ngerinnen"

So there's no problem with character-encoding or locale-support at all,
just this custom datatype issue. Now I wonder just why are datatypes
created only AFTER the tables are restored from the dump? Does this make
much sense? I think that one is bound to run into such trouble if custom
datatypes are being used in a database - no table can be created before
the datatypes uses have been declared. Is there some way for me to just
swap steps 1 (create tables) and 2 (create types) before step 3 (copy
table)?

Right now, the resulting files of pg_dumpall are of no use whatsoever if
I cannot restore them in case of an emergency. I tried and installed
tsearch in template1 (which is something I didn't really want to do)
before restoring the dump, but that didn't help either, the result was
just the same - I should have known, because new databases are created
from template0, IIRC. Now would it help if I just searched and replaced
any "template0" in alldb1.sql with "template1" in order to have tsearch
installed on creation of the database? Or what would be the correct way
for restoring my dumps?

Regards,

    Markus

> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Gesendet: Donnerstag, 5. September 2002 22:33
> An: Markus Wollny
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Problem with restoring dump (may be
> tsearch-related)
>
>
> "Markus Wollny" <Markus.Wollny@computec.de> writes:
> > Now you mention it, I
> > believe that all of the strings which are in one of these
> "parse error
> > at or near"-messages are actually preceded by a HTML-umlaut
> or the like:
>
> Oooh, interesting.
>
> > But why would ordinary plain text cause these parse-errors?
>
> Very good question.  What would be worth doing next is trying to load
> the dump file with query logging enabled, and then look in the
> postmaster log file to see what query the backend thinks it's getting.
>
> I am still suspicious that we're dealing with some weird
> character-set-conversion behavior, but we need to nail down the
> facts first.
>
>             regards, tom lane
>