Re: [GENERAL] 8.2.6 8.3 blows up
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.6 8.3 blows up
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 ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
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 ([EMAIL PROTECTED]) 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.6 8.3 blows up
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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 ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.6 8.3 blows up
On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.6 8.3 blows up
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 ([EMAIL PROTECTED]) http://www.denninger.net Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly %SPAMBLOCK-SYS: Matched [hub.org+], message ok
Re: [GENERAL] 8.2.6 8.3 blows up
Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 02 Mar 2008 15:46:25 -0600 Karl Denninger [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.6 8.3 blows up
Karl Denninger [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
Tom Lane wrote: Karl Denninger [EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.6 8.3 blows up
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 02 Mar 2008 15:46:25 -0600 Karl Denninger [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
