[postgis-users] hard upgrade of database fails because of tsvector syntax error

[Scott Pezanowski]

Sandro,
Thank you for your advice and I apologize for the lengthy delay in my reply. Based on your advice, I seem to have narrowed the problem to one record the restore script hits and its tsvector value (perhaps there are other records that may fail after this, but the restore fails to get past this one). Below is the error I am getting when using postgis_restore.pl.

  Reading list of functions to ignore...  Writing manifest of things to read from dump file...  Writing ASCII to stdout...ERROR:  syntax error in tsvector: "'2012':5 'call':8 'elect':17 'gagnam':12 'hurrican':14 'koni':7 'mayb':10 'olymp':11 'presidenti':16 'rt':1 'sandi':15 'stop':6 'style':13 'summari':3 'wt"CONTEXT:  COPY twitter_datatable_2012_12, line 46295160, column textsearchable_index_col_with_stop: "'2012':5 'call':8 'elect':17 'gagnam':12 'hurrican':14 'koni':7 'mayb':10 'olymp':11 'presidenti':16..."
The actual complete tsvector text is shown below.
"'2012':5 'call':8 'elect':17 'gagnam':12 'hurrican':14 'koni':7 'mayb':10 'olymp':11 'presidenti':16 'rt':1 'sandi':15 'stop':6 'style':13 'summari':3 'wtffact':2"
Therefore, there is more text in the tsvector field, which seems to be being truncated. I do not see anything out of the ordinary with any characters near where the truncation occurs, that would cause this - Unless it is perhaps actually failing somewhere other than at the  'wt  part. Do you think there may be a need for some sort of escape of certain characters like the apostrophe? Or perhaps a length imposed on the text?
I am not quite sure what the problem with the data could be. If I simply insert the full actual value into the tsvector column, it inserts fine. So I think it seems like the text being truncated for some reason causes the "syntax error".
Based upon this, can you see what the problem may be or do you have any other suggestions on things I can look at to try to diagnose the problem?
Thanks again,Scott

> Date: Tue, 7 Jan 2014 18:04:39 +0100
> From: strk at keybit.net
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] hard upgrade of database fails because of tsvector syntax error
> 
> On Mon, Jan 06, 2014 at 09:03:05PM -0500, Scott Pezanowski wrote:
> 
> > When I do a straight pg_restore of the dump file into a PostGIS database without using the postgis_restore.pl script, the database restores fine. Is there any chance I could do this and have the latest version of PostGIS 2 work on it as-is? Or could I perhaps load the legacy.sql file and then do this?
> > Is there any way I could modify the postgis_restore.pl script where it would accomplish what it needs to do with the PostGIS upgrade, but then use the pg_restore command instead of piping the results to psql?
> > I also have the database dumped as a SQL file. Is there any way I can use this and achieve a solid restore to PostGIS 2?
> 
> What you could do is send the output of "postgis_restore.pl" to a file,
> and compare it with the output of pg_restore to see where things get
> messed up. 
> 
> --strk;
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140130/ab0c0e6d/attachment.html>