Re: regexp_replace failing on 9.0.4

On 03/18/2013 01:19 PM, Tom Lane wrote:
> Rob Sargent <robjsargent(at)gmail(dot)com> writes:
>> On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2]
>> test machine and my 9.1.2[3] dev box all is fine
>
> AFAICS from the commit logs, there were no changes affecting the regex
> code between 9.0.3 and 9.0.4. I'm suspicious that your data is
> different on the different servers.
>
> regards, tom lane
>
Good to hear, thought I might have glossed over the telling release note
- my usual mo

I restored my dev db from prod just for this run. test and prod are out
of sync by a couple of weeks.

What I had to do ultimately was as follows,

create table support.duplicate_pathdx_namespace
as select item_id, legacy_id, name, locked_by, maxversion,
regexp_replace(substring(content,1,150),
'(^.*)ns/acres/pathology/dx/1.5(.*$)',
E'\\1ns/acres/pathology/dx/1.6\\2')
|| substring(content, 151) as content
from support.fix_pathdx_namespace;

and the update finally stuck, then copied that reconstructed textblock
over to the target production table in an regexp-less update. Doing the
reg_exp_replace on the whole text blog as part of create temp table did
not work.

I'll double check my dev server from same pre-run dump.

Weird.