Re: regexp_replace failing on 9.0.4

On 03/21/2013 06:25 PM, Tom Lane wrote:
> Rob Sargent <robjsargent@gmail.com> writes:
>> For fun I decided to install 9.2 and thought I would try my luck there.
>>    Here's was I saw (apologies for the wide output).
>
>> << simple update in place>>
>> update cms.segment_data
>> set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)',
>> E'\\1ns/acres/pathology/dx/1.6\\2')
>> where id = 'c092880f-8484-4b29-b712-f3df12216701';
>> UPDATE 1
>> [ doesn't change the first 150 characters of the field ]
>
>> << update in parts >>
>> update cms.segment_data set text =
>> regexp_replace(substring(text,1,150),
>> '(^.*)ns/acres/pathology/dx/1.5(.*$)',
>> E'\\1ns/acres/pathology/dx/1.6\\2') || substring(text, 151)
>> where id = 'c092880f-8484-4b29-b712-f3df12216701';
>> UPDATE 1
>> [ does change the first 150 characters of the field ]
>
> I'm suspicious that there is more than one match to the substring
> in that field, with the extra match(es) coming beyond char 150.
> The way that regexp is written, I think it would replace the last
> match not the first.
>
>             regards, tom lane
>
Indeed there are (or at least may be) other instances of the namespace
string.  I was operating on the assumption that only the first would get
hit, but I fear greediness has gotten the better of my yet again.

And there's reason to believe the "9.0.3" test server db did not have
the proliferations of the string.

Thanks as always.