8.2.6 > 8.3 blows up

On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger <karl@denninger.net> wrote:

Ugh.

I am attempting to move from 8.2.6 to 8.3, and have run into a major
problem.

The build goes fine, the install goes fine, the pg_dumpall goes fine.

However, the reload does not. I do the initdb and then during the
reload I get thousands of errors, apparently from table data which is
perfectly ok in the tables, but pukes up a hairball when attempted to be
reloaded.

So what error message?

A whole host of them, mostly about bad data formats in some of the table
data. I suspect the underlying problem is that something got mangled in
the table creates.

I'm setting up on a different box as my attempt to create a second
instance failed horribly - compiling with a different prefix and port
number appears to work but when you do an "initdb" it blows up with a
complaint about not being able to create the semaphores and shared
segment. It appears the port number is used for the SEMID and SHMID
prefixes, and those for some reason are not getting reset (it may be
that the change in configure requires a gmake clean; not sure)

In any event I have another machine and will get something more detailed
ASAP - I will also try the "restore" program and see if that works.

Karl Denninger (karl@denninger.net)
http://www.denninger.net

Scott Marlowe wrote:

Scott Marlowe wrote:

On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger <karl@denninger.net> wrote:

Ugh.

I am attempting to move from 8.2.6 to 8.3, and have run into a major
problem.

The build goes fine, the install goes fine, the pg_dumpall goes fine.

However, the reload does not. I do the initdb and then during the
reload I get thousands of errors, apparently from table data which is
perfectly ok in the tables, but pukes up a hairball when attempted to be
reloaded.

So what error message?

It looks like the problem had to do with the tsearch2 module that I have
in use in a number of my databases, and which had propagated into
template1, which meant that new creates had it in there.

If its in a database in a dump it trashes the restore for everything
beyond that point when the restore is unable to find it in the new database.

I went through the entire catalog and pulled the old contrib/tesearch2
module in for those databases where it was present before, and it now
appears to be restoring ok.....

I'm not quite clear what I have to do in terms of if/when I can drop the
old tsearch config stuff and for obvious reasons (like not running into
this in the future) I'd like to. Can I just run the "untsearch2" script
against those databases or will that destroy the search functionality?
Are there changes necessary in the SQL code (the documentation implies
not unless I'm doing "odd" things)

Thanks in advance...

Karl Denninger (karl@denninger.net)
http://www.denninger.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, 02 Mar 2008 15:46:25 -0600
Karl Denninger <karl@denninger.net> wrote:

I'm not quite clear what I have to do in terms of if/when I can drop
the old tsearch config stuff and for obvious reasons (like not
running into this in the future) I'd like to. Can I just run the
"untsearch2" script against those databases or will that destroy the
search functionality? Are there changes necessary in the SQL code
(the documentation implies not unless I'm doing "odd" things)

One of the goals for 8.3 and the integrated tsearch was to remove
exactly this problem.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHyzUtATb/zqfZUUQRAuyAAJ4vTqNjBvuNkyx2ygX55V3KPHxO+QCgl8yb
IAvNRUdlBg6G75KX9d95FiA=
=cqc6
-----END PGP SIGNATURE-----

Joshua D. Drake wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, 02 Mar 2008 15:46:25 -0600
Karl Denninger <karl@denninger.net> wrote:

I'm not quite clear what I have to do in terms of if/when I can drop
the old tsearch config stuff and for obvious reasons (like not
running into this in the future) I'd like to. Can I just run the
"untsearch2" script against those databases or will that destroy the
search functionality? Are there changes necessary in the SQL code
(the documentation implies not unless I'm doing "odd" things)

One of the goals for 8.3 and the integrated tsearch was to remove
exactly this problem.

Sincerely,

Joshua D. Drake

Unfortunately there are code changes necessary on my end in one of my
major applications; the attempt at compatability is somewhat less than
successful from here.....

The problem is that I was holding the ts_vector in a column in the table
with a GIST index on that column. This fails horribly under 8.3; it
appears to be ok on the reload but as there is a trigger on updates any
update or insert fails immediately with a data mistype complaint.

The fix is to rejigger the query to go directly at the fields and build
a gin or gist index directly on the underlying, which is not difficult
but DOES require code and schema changes. I'll get through it but this
is going to bite people with some authority if they have applications
that were doing things the same way I was - there was nothing esoteric
about the way I had coded it (although it could be argued it was
somewhat wasteful of disk space.)

-- Karl Denninger
karl@denninger.net

Karl Denninger <karl@denninger.net> writes:

It looks like the problem had to do with the tsearch2 module that I have
in use in a number of my databases, and which had propagated into
template1, which meant that new creates had it in there.

The old tsearch2 module isn't at all compatible with 8.3. I'd suggest
removing it from the 8.2 source databases where possible, ie wherever
you're not actually using it. Where you are using it, the cure is to
install the 8.3 version of contrib/tsearch2 into the target database
*before* restoring. See
http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
http://www.postgresql.org/docs/8.3/static/tsearch2.html

regards, tom lane

Tom Lane wrote:

Karl Denninger <karl@denninger.net> writes:

It looks like the problem had to do with the tsearch2 module that I have
in use in a number of my databases, and which had propagated into
template1, which meant that new creates had it in there.

The old tsearch2 module isn't at all compatible with 8.3. I'd suggest
removing it from the 8.2 source databases where possible, ie wherever
you're not actually using it. Where you are using it, the cure is to
install the 8.3 version of contrib/tsearch2 into the target database
*before* restoring. See
http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
http://www.postgresql.org/docs/8.3/static/tsearch2.html

regards, tom lane

%SPAMBLOCK-SYS: Matched [postgresql.org], message ok

Yeah, I read that in the docs...

But there are compatability problems with the tsearch2 contrib module in
8.3 and backwards constructs and code, with the most serious being that
it simply doesn't work correctly for some of the older ways of
formatting queries and storage.

Specifically, if you store the ts_vector in the table via a different
column and have a GIST index on it, this fails with the 8.3 tsearch2
module loaded as all updates or inserts return a complaint when the
trigger fires - the claim is that there's a data type mismatch.

The simplest fix is to rework the index to go directly off the
underlying column and then query off that but that requires a change to
both the query and the schema. Not a big deal to do once you figure out
what's up, but it DOES require code changes.

See my other message to the list on this; I have worked around it in the
applications affected but this is likely to bite people with some
authority until they get their arms around it.

Karl Denninger (karl@denninger.net)
http://www.denninger.net

Karl Denninger wrote:

The problem is that I was holding the ts_vector in a column in the table
with a GIST index on that column. This fails horribly under 8.3; it
appears to be ok on the reload but as there is a trigger on updates any
update or insert fails immediately with a data mistype complaint.

Are you sure you've not got an old definition of tsvector/tsquery or
some such? I've got a tsvector column in a table, updated from a trigger
with a gist index and it's fine. That's all created for 8.3 though, not
dumped/restored from 8.2

--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:

Karl Denninger wrote:

The problem is that I was holding the ts_vector in a column in the
table with a GIST index on that column. This fails horribly under
8.3; it appears to be ok on the reload but as there is a trigger on
updates any update or insert fails immediately with a data mistype
complaint.

Are you sure you've not got an old definition of tsvector/tsquery or
some such? I've got a tsvector column in a table, updated from a
trigger with a gist index and it's fine. That's all created for 8.3
though, not dumped/restored from 8.2

Quite sure; I just did it "by the book". The simple fix was to get rid
of that and re-write the queries, which I've now done.

Karl Denninger (karl@denninger.net)
http://www.denninger.net

Karl Denninger wrote:

Richard Huxton wrote:

Karl Denninger wrote:

The problem is that I was holding the ts_vector in a column in the
table with a GIST index on that column. This fails horribly under
8.3; it appears to be ok on the reload but as there is a trigger on
updates any update or insert fails immediately with a data mistype
complaint.

Are you sure you've not got an old definition of tsvector/tsquery or
some such? I've got a tsvector column in a table, updated from a
trigger with a gist index and it's fine. That's all created for 8.3
though, not dumped/restored from 8.2

Quite sure; I just did it "by the book". The simple fix was to get rid
of that and re-write the queries, which I've now done.

Do you have a record of what the type error was, and what triggered it?
If this is reproducable we'll need to change the docs.

--
Richard Huxton
Archonet Ltd

I can reproduce this as I have the dump from "before conversion" and can
load it on a different box and "make it happen" a second time.

Would you like it on the list or privately?

Karl Denninger (karl@denninger.net)
http://www.denninger.net

Richard Huxton wrote:

Karl Denninger wrote:

I can reproduce this as I have the dump from "before conversion" and can
load it on a different box and "make it happen" a second time.

Would you like it on the list or privately?

Privately, unless you can boil it down to a small schema fragment.

--
Richard Huxton
Archonet Ltd