Thread: 8.2.6 > 8.3 blows up

8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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.

Its not clear how I can have both versions running at once side-by-side;
this is a production system and I can get a "tee" of the blowup and post
it, but to do that I need to be able to start both versions at once.....
both ARE loaded on the system; is there a way to do that?

Thanks in advance....

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





Re: 8.2.6 > 8.3 blows up

From
"Scott Marlowe"
Date:
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?

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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:
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 majorproblem.
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 thereload I get thousands of errors, apparently from table data which isperfectly ok in the tables, but pukes up a hairball when attempted to bereloaded.   
So what error message?

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to majordomo@postgresql.org so that your      message can get through to the mailing list cleanly


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

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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




Re: 8.2.6 > 8.3 blows up

From
"Joshua D. Drake"
Date:
-----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-----

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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

Re: 8.2.6 > 8.3 blows up

From
Tom Lane
Date:
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

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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




Re: 8.2.6 > 8.3 blows up

From
Richard Huxton
Date:
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

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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





Re: 8.2.6 > 8.3 blows up

From
Richard Huxton
Date:
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

Re: 8.2.6 > 8.3 blows up

From
Karl Denninger
Date:
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:
>> 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.
>



Re: 8.2.6 > 8.3 blows up

From
Richard Huxton
Date:
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