Re: regexp_replace failing on 9.0.4

On 03/18/2013 01:19 PM, Tom Lane wrote:
> Rob Sargent <robjsargent@gmail.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.