Обсуждение: 8.2.6 > 8.3 blows up
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
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.
Scott Marlowe wrote:
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
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:
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
-----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: >> 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. >
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