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
>
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).

#localhost:cms# select count(*) from pg_trigger;
+-------+
| count |
+-------+
| 364 |
+-------+
(1 row)

Time: 0.407 ms
#localhost:cms# select tgname from pg_trigger where tgname !~
'^RI_ConstraintTrigger';
+--------+
| tgname |
+--------+
+--------+
(0 rows)

#localhost:cms# select version();
+-------------------------------------------------------------------------------------------------------+
| version
|
+---------------------------------------------------------------------------------------------------------
| PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit |
+-------------------------------------------------------------------------------------------------------+
(1 row)

select substring(text,1,150) from cms.segment_data
where id = 'c092880f-8484-4b29-b712-f3df12216701';
+----------------------------------------------------------------------------------------------------------------+
| substring
|
+----------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>\r
+|
| <pathology xmlns="http://amirsys.com/ns/acres/pathology/dx/1.5"
enable-tables="true"><metadata><id>diversion_c |
+----------------------------------------------------------------------------------------------------------------+
(1 row)

#localhost:cms# begin;
BEGIN

<< 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

< check >>
select substring(text,1,150) from cms.segment_data
where id = 'c092880f-8484-4b29-b712-f3df12216701';
+----------------------------------------------------------------------------------------------------------------+
| substring
|
+----------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>\r
+|
| <pathology xmlns="http://amirsys.com/ns/acres/pathology/dx/1.5"
enable-tables="true"><metadata><id>diversion_c |
+----------------------------------------------------------------------------------------------------------------+
(1 row) NO CHANGE (still "1.5");

<< 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

<<check>>
select substring(text,1,150) from cms.segment_data
where id = 'c092880f-8484-4b29-b712-f3df12216701';
+----------------------------------------------------------------------------------------------------------------+
| substring
|
+----------------------------------------------------------------------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?>\r
+|
| <pathology xmlns="http://amirsys.com/ns/acres/pathology/dx/1.6"
enable-tables="true"><metadata><id>diversion_c |
+----------------------------------------------------------------------------------------------------------------+
(1 row)

<<CHANGED!! (now "1.6")>>

ROLLBACK