On 03/18/2013 02:40 PM, Tom Lane wrote:
Rob Sargent <robjsargent@xxxxxxxxx> writes:
On 03/18/2013 01:19 PM, Tom Lane wrote:
Rob Sargent <robjsargent@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
