Re: regexp_replace failing on 9.0.4

On 03/18/2013 02:40 PM, Tom Lane wrote:
> Rob Sargent <robjsargent(at)gmail(dot)com> writes:
>> 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.
>
>> Good to hear, thought I might have glossed over the telling release note
>> - my usual mo
>
> Maybe we're barking up the wrong tree by suspecting the regex itself.
> Perhaps the updates were suppressed by a trigger, or the transaction
> rolled back instead of committing, or some such?
>
> regards, tom lane
>
The work was all rolled into a function:
o find the chapters;
o copy the necessary data (mainly the text blob) into a back-out
table
o "lock" the chapters (protect them from exposure to the client app)
o perform the regexp_replace as the update to prod. table

The function was exec'd in a tx and committed, leaving the back-out
table and the programmatic locks in place, but the update itself had
been a no-op and continued to be with ad hoc update statements, until I
hit the final goofy answer ( rg_replace(string, start) || substring(end) )

Have not yet had a chance to re-create on dev. Test worked like a charm.