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

[Sandro Santilli]

On Thu, Jan 30, 2014 at 01:05:45PM -0500, Scott Pezanowski wrote:
> 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?

First thing I'd do is try to re-create the problem with a new small dump.
It sounds like postgis_restore.pl is truncating a line, but dunno why
it would do that (maybe some env variable affecting perl run ?).
Once you have the small dump you could compare pg_restore output with
postgis_restore one to see is this is the case (truncation performed
by postgis_restore..).

--strk;